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
​

 

[spikepl]

The last two posts in this thread show give you options: http://www.access-programmers.co.uk/forums/showthread.php?t=112974

 

[JLCantara]

No, you need one query per form. But if your familiar with VBA and QueryDefs, yes there is a solution to your problem.

JLCantara.

 

[boblarson]

No, you need one query per form. But if your familiar with VBA and QueryDefs, yes there is a solution to your problem.

JLCantara.

No need for the QueryDef object. As long as a single value is to be returned, the Function method shown by Gemma-the-Husky in the thread referred to by spikepl will work just fine.

I'll post an example here so y'all don't have to go searching for it.

First, a global Variable is declared in the General Declarations Section of a STANDARD module (not form, report, or class module).

Public gstrFormName As String

Then the function (I set it as Long because that is what I use for ID fields but if you have used Text then change it to STRING - but if you have it as text, my question would be why as that is not as efficient).

Function GetStrSurveyID() As Long
   GetStrSurveyID = Forms(gstrFormName).ComboNameHere
End Function

And that can be like this: