Microsoft Access 2010 Bible

Chapter 30: Using Access Macros

1069

variables you’ve already implemented with macros? Well, you don’t have to abandon them;

instead, you can use them directly in your VBA code.

To access a temporary variable in VBA, use the same syntax used in macros:

X = [TempVars]![VariableName]

If you don’t use spaces in your variable names, you can omit the brackets:

X = TempVars!VariableName

Use the previous syntax to assign a new value to an existing temporary variable. The only differ-

ence is to put the temporary variable on the left side of the equation:

TempVars!VariableName = NewValue

Use the TempVars object to create and remove temporary variables in VBA. The TempVars

object contains three methods: Add, Remove, and RemoveAll. To create a new temporary vari-

able and set its value, use the Add method of the TempVars object as follows:

TempVars.Add “VariableName”, Value

Use the Remove method of the TempVars object to remove a single temporary variable from

memory:

TempVars.Remove “VariableName”

Tip
When adding or removing temporary variables in VBA, remember to put the variable name in quotation marks.

To remove all the temporary variables from memory, use the RemoveAll method of the

TempVars object as follows:

TempVars.RemoveAll

Any VBA variables you create are available to use in your macros, and vice versa. Any variables you

remove in VBA are no longer available to use in your macros, and vice versa. Using temporary vari-

ables, your macros and VBA code no longer have to be independent from each other.

Error Handling and Macro Debugging

In previous versions of Access, if an error occurred in a macro, the macro stopped execution, and

your users saw an ugly dialog box (shown in Figure 30.15) that didn’t really explain what was

going on. If they were unfamiliar with Access, they quickly became disgruntled using the applica-

tion. The lack of error handing in macros is one main reason many developers use VBA instead of

macros to automate their applications.