Global variables are a wonderful way to make your code consistent, powerful and easy to use, but the native Access global variable, one declared in a stand alone module has a critical flaw: it will reset if there is a problem in your code, and it does not retain it’s value once you close Access.

Use TempVars if you don’t need multi-session
If your looking for a reliable global variable than look no further: use TempVars in your code. But they don’t last between sessions so I came up with a hearty replacement I use everywhere in my code, hence it’s my first coding article of 2011, I’ll be referring back to this article constantly in the future.

ADVERTISING
SQL Server Hosting

So what are multi-session global variables, (GV)? They are used to store information between starts in your Access program. You may use your program on Monday and the variables will still retain their value on Tuesday, even after restarting Access.

I’ve created three methods of global variables:

  • ReadGV(VariableName, VariableType): Will read the value of the global variable
  • WriteGV(VariableName, VariableType, VariableValue): Will write the value of the global variable
  • DeleteGV(VariableName): Will delete the global variable

Doc with All Code
All of the code can be found here.

Step 1: Create tblProgramOptions table to store your global variables
You could use other methods, but the fastest way to store variables are in a local access table. Each user has their own copy, allowing you to customize the experience for each. If you’re looking for a GV that can be consistent across all users, then you’re probably better off  with another technique.

Enum ProgramOptions
The first time you use one of the three methods you are going to be pleasantly surprised with the intellisense when you get to the VariableType portion of the method. The list you defined in the Enum is the same one that pops when you press space bar after the first comma.

Example Uses
The first thing you need to do is invoke WriteGV in your code to store the variable name. If it’s the first time you’re using it the code will create a record, otherwise it’ll replace the prior value:
WriteGV "strLastName", strText, "Soto"
The method will save the value “Soto” in a new record with a variable named strLastName. Notice the lack of parenthesis around the function call, you are expected to use WriteGV on it’s own line of code.

To read the value use ReadGV:
Me.lblUserLastName.Caption = ReadGV("strLastName", strText)
Here I’m using the function to read the variable into the Caption property of the label control lblUserLastName.

Finally, if I want to delete the variable, (which I often don’t do), I would use:
DeleteGV "strLastName"
This article introduced you to Enum possibilities in your code and provides you with a easy way to store variables too. Let me know in the comments section your thoughts and if you’ve developed any alternatives to this problem.

If you love this article than consider subscribing to my blog here. I’m also available for speaking engagements and consulting on Access with SQL Server projects, you can contact me here.