using the same query with two forms with different parameters

  • Thread starter Thread starter leahf via AccessMonster.com
  • Start date Start date
L

leahf via AccessMonster.com

I need to use the same query for two different forms. However, each form has
a different parameter. I am using a global variable for the parameters, but
is there any better way built into Access to do this?
 
If you don't mind doing it with code, you can use the querydef's
OpenRecordset method and attach parameters dynamically. This will allow
you to generalize your queries and call them differently from anywhere.
 
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.
 
Back
Top