A Safer Way to Use TempVars

Microsoft Access has a TempVars collection that holds up to 255 TempVar objects.

The Problems with TempVars

Unlike traditional global constants, TempVar values survive a code reset.  There are a few problems with TempVars, though.  

• TempVars are not "visible" (as opposed to, say, a Global form)
• TempVars provide no type safety (they are of type Variant)
• TempVars do not appear in IntelliSense
• TempVars are prone to typos (no benefit from Option Explicit)
• TempVars cannot be directly called from queries

The Solution to (Most of) These Problems

One way to mitigate most of these shortcomings without sacrificing the TempVar benefits is to wrap them inside read/write properties of a VBA class module.  Here's the basic idea:

1. Create a class named TV
2. Set its PredeclaredID attribute = True
3. Add a property Getter/Letter for each TempVar
4. Set TempVar values in code like so: TV.MyVar = 42
5. Retrieve TempVar values in code like so: ?TV.MyVar

Benefits of the TV Class Module Approach

This approach adds...

• Visibility
• Type safety
• IntelliSense
• Compile-time checks for typos

...to the TempVars object.  

Further Reading

For more information, read these articles that cover related TempVars concepts:

• A sample TV class module to demonstrate this concept in action.
• A function you can use to incorporate TempVar values into queries.
• Code to populate a list box with the contents of the TempVars collection.
• Code to populate a local table with the contents of the TempVars collection.
• A design-time routine to generate a TV class module from a local table.

Referenced articles

Image by Jason Dexter from Pixabay

UPDATE [2022-10-22]: Change the heading and text in the final paragraph to reflect that all the articles are now complete.