Consolidate Forms/Queries

[keith701a]

I have three split datasheet forms in which users can double click a request number to open an update form (each split form is a filter for different tables). Each split form has a related update form. The source of the update form is a query. (Three split forms, three update forms, three update queries).

I’d like to consolidate into one update form/query and move the query criteria into some sort of filter on the split form. Is this possible? If so, how is it done?

NS_Request_Info is a search form.
Double clicking a report number opens the form NS_Request_Info_Update.
NS_Request_Info_Update is linked to the query NS_Request_Info_Update Query.
In the query, Report_Number has a criteria of [Forms]![NS_Request_Info]![Report_Number]


NS_Sujects_Form is a search form.
Double clicking a report number opens the form NS_Subject_Update.
NS_Subject_Update is linked to the query NS_Subjects_Update_Update.
In the query, Report_Number has a criteria of [Forms]![NS_Subject_Form]![Report_Number_Subjects].

NS_Vehicles_Form is a search form.
Double clicking a report number opens the form NS_Vehicles_Update.
NS_Vehicles_Update is linked to the query NS_Vehicles_Update_Update.
In the query, Report_Number has a criteria of [Forms]![NS_Vehicles_Form]![Report_Number_Vehicles].

So I’d like to accomplish:
Double clicking report number on NS_Request_Info, NS_Vehicles_Form, and/or NS_Subjects_Form opens “Search_Update_Form” based on the filter [Forms]![NS_Request_Info]![Report_Number], OR, [Forms]![NS_Subject_Form]![Report_Number_Subjects], OR, [Forms]![NS_Vehicles_Form]![Report_Number_Vehicles]

 

[pbaldy]

As suggested elsewhere:

http://www.baldyweb.com/wherecondition.htm

and also

http://www.excelguru.ca/node/7

 

[keith701a]

As suggested elsewhere:

http://www.baldyweb.com/wherecondition.htm

and also

http://www.excelguru.ca/node/7

So I created a new update form that does not have a record source. I then went in and created some unique names and added this to the on double click:

DoCmd.OpenForm "Update_Form", , , "UFReportNumber = " & Me.NSRIReport_Number

When I double click a parameter dialog opens asking for the report number. What am I doing wrong?

 

[pbaldy]

The update form would need a recordsource. You'd just be using this to filter it rather than a query criteria.

 

[keith701a]

The update form would need a recordsource. You'd just be using this to filter it rather than a query criteria.

I added a record source, a query with no criteria. It now opens a parameter dialog asking for UFReportNumber?

 

[pbaldy]

Is that a field returned by the query? If so, what is the field's data type?

 

[keith701a]

Is that a field returned by the query? If so, what is the field's data type?

It is; both fields are the report number, but I gave the fields unique names so I could write the code easier. The field is returned by the query.

The field in the table is formatted as short text.

 

[pbaldy]

If it's text you'd need the text format from the link, using delimiters. Can you attach the db here?

 

[keith701a]

If it's text you'd need the text format from the link, using delimiters. Can you attach the db here?

This is the new code:
Private Sub NSRIReport_Number_DblClick(Cancel As Integer)
DoCmd.OpenForm "Update_Form", , , "UFReportNumber = '" & Me.NSRIReport_Number & "'"
End Sub

Same issue, parameter window asking for UFReportNumber. What does using delimiters mean? I can attached the database, but I have it split front end/back end. I cannot attach the back end because of the sensitive data it contains. Please don't judge the database too harshly, I'm completely self taught.

I assume if I get it working I only need to change the me.control variable to get it working on other forms?

 

[keith701a]

I got it to work! Screwing around I change it from the field names to the name of the field in the table "Report_Number." It now works. Is there a way to get this to work if one of the fields is in a navigation form? I'd like to double click a report on the home screen to open the same update form.

 

[pbaldy]

Great! The parameter prompt indicated that the field name was wrong, or at least not in the report. Can't tell from the sample without the data, as it can't find field names.

I would expect the same code to work from anywhere, as "Me" refers to the form containing the code. That said, I've never used the built-in navigation form.

 

[keith701a]

Great! The parameter prompt indicated that the field name was wrong, or at least not in the report. Can't tell from the sample without the data, as it can't find field names.

I would expect the same code to work from anywhere, as "Me" refers to the form containing the code. That said, I've never used the built-in navigation form.

I got to work quick and can confirm it is working everywhere. I appreciate the help.

Is there a way to code a button to open the same form (Update_Form) and have it ask for a parameter (a report number)? I'd like to purge a few more forms if possible. Thanks again!

 

[pbaldy]

If you'll always want that number, you can add a prompt to the source query. Or, use an input box in the code and get it from the user right before opening the form.

Dim strInput As String
strInput = InputBox(...)
DoCmd.OpenForm "Update_Form", , , "UFReportNumber = '" & strInput & "'"

You could add something to validate what they've entered.

 

[keith701a]

If you'll always want that number, you can add a prompt to the source query. Or, use an input box in the code and get it from the user right before opening the form.

Dim strInput As String
strInput = InputBox(...)
DoCmd.OpenForm "Update_Form", , , "UFReportNumber = '" & strInput & "'"

You could add something to validate what they've entered.

Would I put that code into a box? Basically want I want to do is use that same update form, but instead of finding a value from the box that is double clicked the user types the value in. Right now that is done from the query "update_request_bynumber" and the form "Update Request." I want to eliminate those two and move the parameter in "update_request_bynumber" just into the VBA code.

VBA is not my strong suit. I've taken an online class on it, but it is still an foreign language.

 

[pbaldy]

You can put the code behind a button, textbox, or wherever you want the action triggered from.

 

[keith701a]

You can put the code behind a button, textbox, or wherever you want the action triggered from.

I added this code to a new button on click event:
Dim strInput As String
strInput = InputBox("Report_Number")
DoCmd.OpenForm "Update_Form", , , "UFReportNumber = '" & strInput & "'"
When I click the button I get this prompt capture.png (which is what I want). After entering a valid report number capture2.png opens, so obviously it isn't pulling what's typed in the prompt. What am I doing wrong?

 

[pbaldy]

Didn't you change the field name to something else?

 

[keith701a]

Didn't you change the field name to something else?

Yes, and that's what did it. Seriously, thank you so much. I've wanted to try to do this for months and your help made it totally painless. I really appreciate it!

 

[pbaldy]

Happy to help!

 

[keith701a]

The one last item of cleanup I cannot figure out is refreshing the list of open reports after the update form is closed.
I tried adding Forms!Nav_Form!Pending_Working.Requery to the on close event of the form, but it doesn't requery the form. Do you happen to know my latest VBA mistake?