First, queries cannot use variables directly. There are serveral ways to
accomplish this.
1. Put the value in a control on the form and reference that control in your
query
Since you are using 2 forms, this will not work in this case.
2. Programmatically open the query and modify its SQL proprerty.
This would be one way to accomplish this
3. Use a Static Function to hold the value of the parameter and call the
function in your query. This is may preferred way of doing it.
Here is how a Static Function works. Static functions retain their value as
long as the module they are in is open. If you put a static function in a
form module, it only retains its value while the form is open. If you put it
in a standard module, it will retain its value as long as the application is
open.
A Static Function works logically like a Global variable. If you don't pass
it a value, it returns the current value. If you pass it a value, it changes
its value to the passed value. I usually make my static functions use
Variant data types to allow flexibility. Here is an example:
Static Function GetParmValue(Optional varNewValue as Variant) As Variant
Dim varSaveValue as Variant
If Not IsMissing(varNewValue) Then
varSaveValue = varNewValue)
End If
GetParmValue = varSaveValue
End Function
Put this in a Standard module. When you want to assign it a value:
GetParmValue("Gort")
From this point, until you pass it a new value, it will return "Gort"
So, in your query you can use it in a Calculated field definition or as a
criteria to return the value you assigned regardless of where you made the
assignment.