Microsoft Access 2010 Bible

Chapter 30: Using Access Macros

1065

Temporary Variables

In previous versions of Access, you could use variables only in VBA code. Macros were limited to

performing a series of actions without carrying anything forward from a previous action. Beginning

with Access 2007, three new macro actions — SetTempVar, RemoveTempVar, and

RemoveAllTempVars — let you create and use temporary variables in your macros. You can use

these variables in conditional expressions to control which actions execute, or to pass data to and

from forms or reports. You can even access these variables in VBA to communicate data to and

from modules.

Enhancing a macro you’ve already created

A simple way to demonstrate how to use variables in macros is to enhance the Hello World example

created earlier in this chapter (see “Creating a macro,” earlier in this chapter). Table 30.4 shows the

macro actions and action arguments for mcrHelloWorldEnhanced (shown in Figure 30.12).

The SetTempVar action has two arguments: Name and Expression. The Name argument

(MyName in this example) is simply the name of the temporary variable. The Expression argu-

ment is what you want the value of the variable to be. In this example, the InputBox() function

prompts the user for his name.

TABLE 30.4

mcrHelloWorldEnhanced

Action Action Argument Action Argument Setting

SetTempVar Name MyName

Expression InputBox(“Enter your name.”)

MessageBox Message =“Hello “ & [TempVars]![MyName] & “.”

Beep Yes

Type Information

Title Using Variables

RemoveTempVar Name MyName

The MessageBox action’s Message argument contains the following expression:

=“Hello “ & [TempVars]![MyName] & “.”

This expression concatenates the word Hello with the temporary variable MyName, created in the

SetTempVar action of the macro. When referring to a temporary variable created with the

SetTempVar action, use the following syntax:

[TempVars]![VariableName]