Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Forums  Contact  Privacy  Links

Query Parameter Global Variables
Use Global Variables as SQL Parameters

Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Denormalize Records
Stock Quotes
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions

Visual Basic Function Examples

Global Variables Parameters

Need help with Global Variables? Your time is too valuable - call us and we will solve the problem or there is no charge! $100 1/2 hour.

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:

    You did not know that nearly 100 cable and satellite tv installers use our software to manage their device inventory. They never lose track of an expensive device.

    Did you know that most template databases often do not do exactly what you need done? We can create a custom template that fits your needs exactly.

  • 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.

New! Download Access example of  Global Variables

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"
            Case "Start_Date"
            Case "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...


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.

Blue Claw Database Design Downloadable Tutorial:
Popup Form Control Method  

A Blue Claw Software Design Template:

Student Grant Program Administration Software

A Blue Claw Database Design Article:

Microsoft Access On The Internet

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016