Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  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
Concatenate Records
Stock Quotes
CreateQueryDef
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
FindFirst
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.
410-708-1417

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:



    Since 2000 we have developed 100's of databases for clients throughout the world. See a sample of them at our recent clients page.

    Do you and your clients need access to data on the Internet? We can create a secure system to match your requirements perfectly.


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










Blue Claw Database Design Downloadable Tutorial:
Bar Chart / Bar Graph Download  








A Blue Claw Software Design Template:

Order Processing Case Study
 










A Blue Claw Database Design Article:

Software Risk Assessment
 

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