Today, I thought I’d discussion a great feature that was added to Access 2007+, TempVars.

There used to be an excellent WIKI article on UtterAccess.com that covered this subject and I used to simply refer people to, but it is no longer, so I decided to create this post in the hopes that I could replicate the information lost and helps other developers.

What are TempVars and Why Use Them?!

TempVars are ‘sort of’ like global variables.

Then why use them you may ask, why not just use traditional Global Variables?

Excellent question! TempVars offer several benefits, mainly:

• They are global in nature
• They are no affected, reset, by VBA runtime unhandled errors
• They can be used directly within queries, forms, VBA
• They can be create/modified with macros and VBA alike
• Can accept different variable types automatically

How To Use TempVars

They are extremely easy to work with and there are a couple ways to work with them, but I am going to show you what I view as the easiest and most straightforward way to do so.

I’ve broken this discussion up into 2 major sections:

• VBA
• Macros

Using VBA to Work with TempVars

Setting a TempVars

The general syntax is:

TempVars!YourTempVarName = "SomeValue"

A couple real life example:

TempVars!FirstName = "John"

TempVars!ConversionRate = 1.33

TempVars!Active = True

Retrieving a TempVars

Retrieving a TempVars is a piece of cake!  Simply call it wherever you need it by doing

TempVars!YourTempVarName

Example:

MsgBox "The conversion rate is " & TempVars!ConversionRate

Deleting/Removing a TempVars

Yet again, this is very straightforward.

Deleting a Single TempVars

To delete/clear a single TempVars the basic syntax is:

TempVars.Remove("YourTempVarName")

Example:

TempVars.Remove("ConversionRate")

Deleting ALL the TempVars

It is also possible to delete/clear all existing TempVars in one command as shown below.

TempVars.RemoveAll

Testing the Existence of a TempVars

How can I test for the existance of a TempVars?

Another excellent question and once again something very easy to accomplish.

Long story short, if a TempVars isn’t set yet it will be Null, so we can simply use the IsNull function to test and then act accordingly, so something like:

If IsNull(TempVars!ConversionRate) = True Then
    TempVars!ConversionRate = Nz(DLookUp(...), 1.25)
End If

Checking a TempVars Variable Type

It can be useful at times to check what type of variable is being passed, and you can do so with TempVars as well by simply using the VarType function.  The basic syntax is:

VarType(TempVars!YourTempVarName)

Example

VarType(TempVars!FirstName)
'Returns 8 -> String

VarType(TempVars!ConversionRate)
'Returns 5 -> Double

VarType(TempVars!Active)
'Returns 11 -> Boolean

Listing Existing TempVars

If ever you wanted to get a listing of existing TempVars, you can do something along the lines of:

Sub ListTempVars()
    Dim i                     As Long
    For i = 0 To TempVars.Count - 1
        Debug.Print TempVars(i).Name, TempVars(i).Value, VarType(TempVars(i))
    Next i
End Sub

Using Macros to Work with TempVars

One of Tempvars strengths is their flexibility and the fact that you can work with them with both VBA and Macros. So let’s look at how we can interact with TempVars using Macros.

Below I provide a screenshot of the required Macro code.

Setting a TempVars

As you can see, setting a TempVars using a Macro is very straightforward and one must simply use the SetTempVar action and then provide a TempVars Name and Value (Expression).

Retrieving a TempVars

To retrieve a TempVars value through a Macro we actually use the same syntax as in VBA:

TempVars!YourTempVarName

Example:

Note the = sign at the begging of the Message!

Deleting/Removing a TempVars

Deleting a Single TempVars

To delete/clear a single TempVars is done using the RemoveTempVar action where you need only supply the name of the TempVars to delete.

Deleting ALL the TempVars is done using the RemoveAllTempVars action

Testing the Existence of a TempVars

Once again, the same principle that we used in VBA is applicable here.  So to check if a TempVars exists we need to see if it is null or not, and to do so we use the IsNull() function.   Thus, using an If statement in conjunction with the IsNull() function we can check and react to whether or not a TempVars is set or not.

TempVars, VBA Variables and Runtime Errors

I wanted to briefly elaborate on the statement “They (TempVars) are no affected, reset, by VBA runtime unhandled errors”.

One might point out that standard VBA variables can achieve the exact same thing and you can create public functions to make them accessible through Macros, Forms…, so why use TempVars?

One issue with standard VBA variable is that they get reset when an unhandled error occurs.  The critical word here being ‘unhandled’.  This is why implementing error handling throughout your VBA code is so important.

I should also point out that the above statement is true of uncompiled versions (mdb, accdb, …).  Compiled versions (mde, accde, …) do not re-set local, or even global variables because of un-handled errors.

To prove this, a very simple example.

Public GlobalVarFirstName As String
Sub CompareTechniques()
    GlobalVarFirstName = "Jane"
    TempVars!FirstName = "John"
    Debug.Print "'" & GlobalVarFirstName & "'", "'" & TempVars!FirstName & "'"
    Err.Raise 11 'Division by 0 error
End Sub

So run the above and then execute

Debug.Print "'" & GlobalVarFirstName & "'", "'" & TempVars!FirstName & "'"

in the immediate window you will see:

'Jane' 'John'
'' 'John'

So, as you can see, the GlobalVarFirstName variable looses its value when the error occurs.

Now, if you add error handling, any error handling it won’t

Public GlobalVarFirstName As String
Sub CompareTechniques2()
On Error Resume Next
    GlobalVarFirstName = "Jane"
    TempVars!FirstName = "John"
    Debug.Print "'" & GlobalVarFirstName & "'", "'" & TempVars!FirstName & "'"
    Err.Raise 11 'Division by 0 error
    Debug.Print "'" & GlobalVarFirstName & "'", "'" & TempVars!FirstName & "'"
End Sub

Now, in the immediate window you will see:

'Jane' 'John'
'Jane' 'John'

So, by adding an On Error … the VBA Global Variable retains its value, but regardless the TempVars always retained its value.  So they persist regardless of what happens making them very dependable!

Limitations

I have come across 2 limtations with TempVars so far that have impacted my coding:

• You cannot pass an object to a Tempvars (objects, arrays, collections, dictionaries, …) and you will received an Run-time error: 32538 – TempVars can only store data. They cannot store objects.
• You cannot pass another TempVars to a TempVars
• You cannot use them directly in a Query and seem to need to use a conversion function on them for them to appear properly (more testing is needed)

You cannot pass an object to a Tempvars

The following will fail:

Dim oExcel                As Object
Set oExcel = GetObject(, "Excel.Application")
TempVars!sHostPrev = oExcel

and there is simply no workaround for this case. Your only solution is to use good old Public Variables in such instances or perhaps build a separated string of the elements that you can then parse when need be.

You cannot pass another TempVars to a TempVars

The following will fail:

TempVars!sHost = "."
TempVars!sHostPrev = TempVars!sHost

but a workaround is:

TempVars!sHost = "."
TempVars!sHostPrev = CStr(TempVars!sHost)

Now why the Dev Team didn’t simply use VarType to detect the TempVars type and then apply the appropriate conversion so this would work natively is anyone’s guess! So here’s a simple sub I created to do the job:

'---------------------------------------------------------------------------------------
' Procedure : PassTempVars
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Sub to pass a TempVar value to another TempVar
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References: https://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/vartype-function
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' TVScr     : TempVar holding the value you wish to pass to the other TempVar
' TVDest    : TempVar that will receive the TVSrc TempVar value
'
' Usage:
' ~~~~~~
' TempVars!CurrentDate = #12/31/2021#
' PassTempVars TempVars!CurrentDate, TempVars!HoldingDate
' ?TempVars!HoldingDate
'       -> 12/31/2021
' ?VarType(TempVars!HoldingDate)
'       -> 7 => Date
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-02-23              Initial Public Release
'---------------------------------------------------------------------------------------
Public Sub PassTempVars(TVScr As TempVar, TVDest As TempVar)
On Error GoTo Error_Handler

    Select Case VarType(TVScr)
        Case 0    'Empty
        Case 1    'Null
        Case 2    'Integer
            TVDest = CInt(TVScr)
        Case 3    'Long
            TVDest = CLng(TVScr)
        Case 4    'Single
            TVDest = CSng(TVScr)
        Case 5    'Double
            TVDest = CDbl(TVScr)
        Case 6    'Currency
            TVDest = CCur(TVScr)
        Case 7    'Date
            TVDest = CDate(TVScr)
        Case 8    'String
            TVDest = CStr(TVScr)
        Case 9    'Object --- Not supported
        Case 10    'Error
        Case 11    'Boolean
            TVDest = CBool(TVScr)
        Case 12    'Variant
        Case 13    'Data access object
        Case 14    'Decimal
            TVDest = CDec(TVScr)
        Case 17    'Byte
            TVDest = CByte(TVScr)
        Case 20    'LongLong
        Case 36    'User-defined
        Case 8192    'Array
    End Select
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PassTempVars" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

and as shown in the procedure header Usage section, you would use it by simply doing

TempVars!CurrentDate = #12/31/2021#
PassTempVars TempVars!CurrentDate, TempVars!HoldingDate

and now TempVars!HoldingDate has the same value as TempVars!CurrentDate.
 
 
Another solution is to use the .Value property, like:

TempVars!CurrentDate = #12/31/2021#
TempVars!HoldingDate = TempVars!CurrentDate.Value

Like the previous example, now TempVars!HoldingDate has the same value as TempVars!CurrentDate.

Unique to Access

Note, TempVars are unique to Access.  They are not available in other VBA applications: Word, Excel, Outlook, …

One more tool to include in your Access toolbox!

Additional Resources

Although I provided a couple links below to the official Microsoft Learn pages on the subject, you’ll see that the information provided is truly minimal and Google is your best friend for getting more information on the subject.