Referencing a form field in the query criteria row triggers a prompt

[Access_Novice]

The attached Word file contains a screen shot of the design grid in my query. In the query form, I am referencing a form field called ParentID which is in a modal form. I want this query to run by taking in the value typed into ParentID. But instead, when I run the query, a prompt is triggered. I suppose it is because Access put [ ] after I typed in the criteria. When I was using a normal form I did not run into this problem.

In summary, when the query runs, I want the query to take in the value of ParentID that was entered in the Modal form, then display the results w/o a prompt popping up. Is this possible?Modal Form.zip

[June7]

Modal is not cause of the issue. Works for me. The [] also are not the issue.

Access is not finding control named ParentID.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify object involved.

[Access_Novice]

Modal is not cause of the issue. Works for me. The [] also are not the issue.

Access is not finding control named ParentID.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify object involved.

I attached my DB. When I tried to open it I got an error message "Can't find language DLL msain.dll"

I appreciate your help.

[Access_Novice]

Modal is not cause of the issue. Works for me. The [] also are not the issue.

Access is not finding control named ParentID.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify object involved.

Since I'm dealing with a Modal form, I added a button called 'Search' which will close out the form when pressed. Because the form is closed, then I run the query, maybe that is the reason why I get a prompt from the query? Perhaps the query can't read a value from a form that is closed?

Also, I noticed I originally referenced 'parentID' as the name of the control, but the name of the control is Text3. I made this change to the query criteria, but I'm still getting the same problem with the prompt appearing.

[June7]

The searchByParentId form is popup and modal. There is no code to open a query or form. Cannot get to the navigation pane with the popup open. The form must remain open for the query parameter to work. Your code closes the form then if you open the query the form is not available, therefore Access does the next best thing, it prompts for the input.

[Access_Novice]

The searchByParentId form is popup and modal. There is no code to open a query or form. Cannot get to the navigation pane with the popup open. The form must remain open for the query parameter to work. Your code closes the form then if you open the query the form is not available, therefore Access does the next best thing, it prompts for the input.

Very helpful. Thank you. I got this to work with the Modal form. This time the modal box stays open while the query executes. I added a report based on that query. So now, when the user clicks search, it will trigger the report which gets its information from the query. The report is filtered based on the parentID that is entered into the modal form.

But here is one thing I noticed. I open the modal form, then type in a parentID and the report pulls up only that parentID. So far so good. At this point the modal box is still open. If I advance the records to a new parentID and click the search button, I do not get a new form with that other parentID. So this only works the first time I submit a parentID.

Why isn't this working when I try to submit another parentID?

[June7]

Not sure what you mean by 'do not get a new form with that other parentID'. Did you mean 'report'? Have to refresh the report.

[Access_Novice]

Not sure what you mean by 'do not get a new form with that other parentID'. Did you mean 'report'? Have to refresh the report.

Yes, you're right. I meant to say that after I enter another parentID in the modal form (which is still open), I do not get a new report. The old one is still there. I can't click Refresh on the ribbon because the modal form is still open. So in my macro, after opening the report, I tried the refresh command and also refresh record. Both of those didn't work.

If I want to pull up a new report from the modal form, I have to close the modal form and report first, then start over. I'm just wondering how to pull up another record while the modal form is still open?

[June7]

This works:

Private Sub Command7_Click()
If CurrentProject.AllReports("Parent Info").IsLoaded Then
    Reports![Parent Info].Requery
Else
    DoCmd.OpenReport "Parent Info", acViewReport
End If
End Sub

[Access_Novice]

This works:

Private Sub Command7_Click()
If CurrentProject.AllReports("Parent Info").IsLoaded Then
    Reports![Parent Info].Requery
Else
    DoCmd.OpenReport "Parent Info", acViewReport
End If
End Sub

Wow! Amazing. Thank you very much. I have a lot to learn so I can make sense out of all the code. Thank you. Just what I was looking for.