Interchangeable form control criteria

[SKIGAL]

I have a base query where the criteria is based on a form control. I have a 'sum' query built on top of this query. The click event on a form will drive code that looks up a value from the 'sum' query.

The question is, how do I reference the form controls in the query criteria if I want to use this query for multiple forms and the criteria will pull from the active or current form?

Thanks in advance for your assistance!

:->

 

[jmrSudbury]

You can dynamically change the SQL of a query.

'Open the query as a QueryDef
dim qdf as querydef

set qdf = currentdb.QueryDefs("YourQueryName&quot
qdf.sql = left(qdf.sql, instr(qdf.sql, _
" WHERE &quot-1 & " WHERE " & txtYourNewCriteria
qdf.close


Just make sure that the form is modal so two forms using the same query cannot be opened at one time.

 

[Onyxpurr]

The easiest way I think of is you may want to use a function. E.G.

Create a module with a simple function and public variable:
Public criteria as string
Public Function QueryCrit as string
Return criteria
End Function

In the forms, on an event (such as clicking a button that generates the query):
Sub OnClick
criteria = "Smith"
end sub

In the query, in the criteria:
QueryCrit()