Refer to a form control in a query based on whichever form is "active"

[dgj32784]

I'm not sure if the title of this post makes any sense. Basically, I'm trying to figure out how to refer to a control on a form in a query criteria based on whichever form is "active." The scenario is I have many forms where I have a form control that retains a "master ID" and another control (combo box) that lists associcated "sub IDs." The value of the master ID control is passed to a query parameter that then selects all of the sub IDs that are related, which populates the list in the combo box on the form. But I don't want to create a query for every single form that needs this functionality simply because the form name is different.

Is there a way to make the red outlined portion of the query criteria a "parameter" or "wildcard" such that the form name is popped into this query dependent on whichever form is "active?" Or in other words, is there a way to refer to the form in the query criteria as "Me?" Do I need to generate the SQL syntax using concatenation in VBA and pass that to the query?

See attached JPG of query matrix.

Any help would be appreciated.

Thanks,
David

[pbaldy]

I know you've cross-posted this, so I would waste too much time on it. You can't do directly what you're asking. You could copy values to a form that will always be open (like a main menu, or hidden criteria form) and have the query look there. You could create a function that used ActiveForm to determine the active form and return the value from it, and call the function in the query.

[June7]

Short answer is no. However, VBA code can modify query using QueryDefs collection or any number of ways pass the 'master ID' value to the query.

You have many forms that have these same two dependent comboboxes? Why are there many forms? Are they essentially identical?

You don't need to create a query object for each combobox. You can build the SQL statement right in the combobox RowSource property. Or use VBA event to set the RowSource property of combobox.

[dgj32784]

Sorry for the cross-posting. I wasn't sure how varied the forum audiences were and thought I would increase my chances.

Thanks for the reply and I think I understand what you have both suggested. I'll try the SQL route first and then the Function option.

I know it seems strange to have these two "dependent" controls on a lot of forms, but the database is an in-the-woods survey database. There are many attributes associated with each survey related to these IDs but having all of this information on one (or few) forms is not practical. It would greatly disrupt the user workflow, make it hard to enter data on a form given we run the dbase on a touch screen TabletPC, and some of the information - while tied to these IDs - is best stored in seperate tables for easy data summary.

[pbaldy]

No problem; post back if you get stuck.

[June7]

Have you considered Form/Subforms arrangement with subforms on tab control?