Using "global" variables in MS Access

As developers, we frequently need to store values for use throughout our application or to pass values from one object (form, report, module) to another.  There are a number of ways to do this within Access and I'll discuss several of the more common methods in this article.

Caveat Emptor: Before delving into this discussion, I want to address a common misconception that "global variables" are bad.  I strongly disagree with this view and regularly use them in my database applications.  The key is that you must be cautious about how you use these variables and understand that changing the variable in one spot will affect other aspects of your application.  

For example: You use a global variable as a criteria in a recordset (say the RecordSource of a form).  While that form is open, you somehow navigate to another form or place in your code where the value of that variable is set to a new value.  When you return to the original form, the value of the variable has been reset, but the RecordSource of the form has not been re-queried, so the records that are displayed are not indicative of the current value of the variable.  For this reason, you must be cautious when using "global variables"

1.  Global Variables: I frequently encounter databases where the developer has chosen to use this technique and has simply defined one or more variables as global in a declaration statement.  This is by far the easiest way to define a variable which can be used throughout your application.  Simply define the variable in a code modules declaration section (as below).

Global lngMyID as long

This variable can then be referenced in all code modules throughout your application.  Although this value can be set and evaluated from within any code module or the VBA immediate window, it cannot be used directly in a query or as a value in a control source of a control on a form.  However, you can overcome this drawback by defining a set of procedures to set and get the value of these variables:

Public Sub SetMyID (VariableValue as long)
    lngMyID = VariableValue
End Sub
Public Function GetMyID() as Long
    GetMyID = lngMyID
End Function

An additional drawback of this technique is that variables defined using this method will lose their values when an unhandled error is encountered in an application.  Regardless of how hard we try, most of us fail to include error handling in every procedure in our applications, and if an error occurs in one of these procedures, the variable will lose its value.  When this happens it can cause a myriad of problems that cascade through your application.  For this reason alone, I NEVER use this method.

2.  Public variables:  Public variables are similar to global variables, but are generally used specifically within a single object, whether that object is a form, report, or standard code module.  They have a scope which is generally limited to the object in which they are defined.  They are declared using the "public"  prefix rather than the "dim" prefix which is normally used to declare a variable in a code module.

Public myVariable as integer

One nice aspect of public variables is that they can be referenced from outside their scope by referring to the object (form, report, or module) in which the variable was declared, with a syntax similar to:

Forms("FormName").MyVariable = 3

This is a useful technique for passing a value from one form, maybe a popup form, back to its calling form.

These values cannot be used within a query and are also susceptible to losing their values when an unhandled error is encountered (did I mention how important it is to add error handling to your code).  I frequently place public variables in my form modules as they are very useful for passing multiple values between forms (both to and from).

3.  Storing variables in controls on a form:  A third method of storing variables which can be used throughout your application involves creating a form with numerous textboxes.  When I do this, I generally create a form (frm_Variables) specifically for this purpose and add an unbound textbox to the form for each of the variables I want to keep track of.  This is handy because I can simply open this form during application development, type values into these controls, and then run the query or open the form which depends on these values.  

The nice thing about this method is that you can refer directly to the controls on this form from anywhere in your application (queries, reports, forms, macros, and code modules), and they do not lose their values when an unhandled error is encountered.  The syntax for referring to these controls is similar to:

Forms("frm_Variables").txtMyID

or

Forms!frm_Variables.txtMyID

or, in a query as:

SELECT * FROM yourTable WHERE ID = [Forms]![frm_Variables].txtMyID

This is the method I used most frequently prior to the release of A2007, and which I still use occasionally.   I've found that when I use this technique in a query, it is helpful to define a parameter to ensure that Access/Jet correctly interprets the data type of the value.

4.  Tempvars:  When Access 2007 was released, one of the new features was tempvars.  Tempvars are a built-in collection which can contain up to 255 items.  As with any other collection, tempvars have Add, Remove, and RemoveAll methods and Count, Item, and Parent properties.  

I like using TempVars because they do not lose their value when an unhandled error occurs and because they can be referred to directly from within queries. Additionally, I can set the value of a tempvar item in the immediate window and can immediately test the performance of a query, form, or report.

    a.  Creating TempVars:  You can create a Tempvars in several ways:

         (1)  tempvars.Add VarName, varValue

         (2)  tempvars!VarName = varValue

         (3)  tempvars!VarName = forms!formname.Controlname.Value

Note: tempvars cannot save objects, they only save values, which is why the method shown in (3) above specifically uses the ".Value" property of the control.  If you fail to specify the ".Value" property of the control, this line of code will raise run-time error #32538: "Tempvars can only store data they cannot store objects"

    b.  Retrieving TempVar values:  You can retrieve a value from the TempVars collection with the following syntax:

         (1) tempvars!VarName

         (2) tempvars(VarName)

         (3) [tempvars]![VarName]

Note: in order to use the TempVar directly in a query, you must use the syntax referred to in 4b(3).

    c. Removing TempVars:  You can remove a TempVar from the collection with the following syntax:

        (1) tempvars.Remove VarName

    d.  Tempvar values: You can even add TempVars to the VBE watch list if you want, but I generally just add a procedure to my applications to list the values of the current variables:

Public Sub ListTempvars()
    Dim intLoop As Integer
    For intLoop = 0 To TempVars.Count - 1
        Debug.Print TempVars(intLoop).Name, TempVars(intLoop).Value
    Next
End Sub

5.  OpenForm / OpenReport method OpenArgs argument:  You can also pass values into a form or report using the OpenArgs argument (the last argument of these methods).  This is a string argument and can pass multiple values separated by any delimiter.  When I do this, I generally pass the argument with a name, something like:

strOpenArgs = "ClientID=" & me.cboClientID & ";ContactID=" & me.cboContactID
docmd.OpenForm "formname", acNormal, , , , acDialog, strOpenArgs

Then, in the Forms Load or Timer event, I will parse that value and retrieve the Client and ContactID values like:

Dim arrArgs() as string
Dim intLoop as integer
Dim lngClientID as long, lngContactID as long
arrArgs = Split(me.openargs, ";")
for intLoop = lbound(arrArgs) to ubound(arrArgs)
    if instr(arrArgs(intLoop), "ClientID") <> 0 then 
        lngClientID = clng(Split(arrArgs(intLoop), "=")(1))
    elseif instr(arrArgs(intLoop), "ContactID") <> 0 then 
        lngContactID = clng(Split(arrArgs(intLoop), "=")(1))
    end if
Next

 Unfortunately, you cannot pass values back to the calling form using this method.  So, as I mentioned above, I will generally either set the value of a public variable in the calling form or set the value of a tempvar variable.       

6.  Persisting values across multiple sessions:  There are several other ways to store variable values which can be saved and recalled between application sessions:

     a.  Database properties:  I use the term "database" loosely here because although I generally only create properties for the currentdb, you can also create properties for other objects (forms, reports) within your application.  Before you can save a value as a database property, you must first create that property and assign it a data type and initial value.  You can do this in the immediate window, like below, or you can create your own procedure with code similar to this.

set db = currentdb
set prp = db.CreateProperty("MyID", dblong, 12345)
db.properties.append prp

After instantiating the property, you can then refer to the property with syntax similar to:

currentdb.properties("PropertyName") = 23
or
X = currentdb.properties("PropertyName")

One downside of using database properties is that uses will lose the values stored using this technique whenever you deploy a new application front end.  Another down-side is that you cannot use the above syntax to refer to one of these database properties in a query, so you will have to store this value using one of the methods mentioned above if you want to display the value in a query.

     b.  Registry entries:  You can use the SaveSetting( ) and GetSetting( ) functions to save and retrieve settings from the HKCU (current user) section of the Windows registry.  This technique is useful because it allows you to save settings that persist across application sessions (I use it to store the left, top, height, and width of sizable forms to accommodate the sizes of user monitors) and which are also available when new versions of the application FE are deployed.  The syntax for these two functions are:

SaveSetting(AppName, Section, Key, Value)

GetSetting(AppName, Section, Key, Value)

Note: All four of these arguments as strings, so if you are actually storing a numeric value (I do this with form positions), you will have to wrap the GetSetting( ) function call within a conversion function.

     c.  Database table: You can also store these values in a database table (you are using a database after all).  If you choose to use a local table for this functionality, you must understand that you will lose those values when you deploy a new front-end.  If you choose to store these values in your database back-end, then you will need to include a field for the UserID, along with fields for VarName, VarType, and VarValue.  When I do this, I generally set the data type of each of those fields to a string, and then use either a Case statement or an If statement to convert the value from the VarValue field into the appropriate data type.  With this technique, you will need to include procedures to SetVarValue and GetVarValue.

I hope this article is helpful when you are contemplating how to store values for use within your database applications.

Dale Fye