Global Variables as Query Parameters

After reviewing our example: all about global variables you are ready to put this knowledge to good use using global variables to pass parameters to a query.

As with nearly all tasks in Microsoft Access there are usually four or five different ways to a solution.  Some are better than others in certain situations, and some are easier than others.

Using variables to pass parameters to queries is one of these multi-solution tasks.  Here are some of the standard solutions:

• Use a criteria entry in the query grid to define a parameter that pops up as a question when the query runs.
   

• Store parameters in a temporary table that is unlinked but defined to the query.
   

• Reference the form field control that contains the parameter value.
   

• Build the sql code in visual basic and either run it as a docmd.runsql or create a stored query with the sql text.

The last, less commonly used method, is use global variables as query parameters.

This is perhaps the neatest method and also allows the query to be used for multiple parameter forms, unlike when you reference the parameter form field directly.  It also provides a consistent method throughout your application and a more centralized location for key information.

The setup is as follows:  Create a Module and place the following code into it.  Note that you'll need to call this procedure (Get_Global) once when the database opens just to setup the variables.. you might want to do this in you opening form.

Option Compare Database

Global GBL_Project_ID As Long
Global GBL_Start_Date As Date
Global GBL_End_Date As Date

Option Explicit

Public Function get_global(G_name as string) as Variant

' property of blueclaw-db.com
'
     Select Case G_name
            Case "Project_ID"
                    Get_Global=GBL_Project_ID
            Case "Start_Date"
                    Get_Global=GBL_Start_Date
            Case "End_Date"
                    Get_Global=GBL_End_Date
    End Select
End Sub

In the parameter form that runs the report you save the value selected by the user to the appropriate variable.  This is easily done by adding the following line of code in the After Update event...

GBL_Project_ID=Project_Combo

In the query there's no more trying to figure out the full path to your parameter field which might be buried several sub forms deep or might change during development up or down, requiring the query reference to change.  Simple refer to the Get_Global function in the criteria of your query - here's the SQL:

Select Project_ID, Project_Name from M_Projects
Where Project_ID=Get_Global('Project_ID');

This is our method of choice for all except the simplest of databases.

Is there a downside to using this method?  Probably.  We haven't done any testing but one would assume that if you have 100's of case statements the lookup of the variable may slow the query down a tad.

Try our downloadable Access database demonstration of using Global variables as query parameters.