Automating a Client Application Using Macros in Microsoft Access 2010

Using Temporary Variables

You can use a temporary variable in Access to store a value that can be used in other macros, event procedures, expressions, and queries. As you’ll learn in Chapter 24, we use a variable to store the user name when you log into the Conrad Systems Contacts and Housing Reservations sample databases. Variables are very useful when you need Access to remember something for later use. You can think of a temporary variable in a macro as writing yourself a note to remember a number, a name, or an address so that you can recall it at a later time. All variables have a unique name. To fetch, set, or examine a variable, you reference it by its name. Temporary variables stay in memory until you close the database, assign a new value, or clear the value.

To see an example of using a temporary variable in the Wedding List Macro sample database, open the ValidateCitySetStateAndZip macro in Design view. We’ll study this macro in more detail in “Validating Data and Presetting Values,” but for now, we’ll focus on creating a temporary variable. Creating a temporary variable in a macro is easy—Access creates the variable for you when you reference it for the first time in a SetTempVar action. In Figure 20-18, you can see that in the AskEdit submacro in the ValidateCitySetStateAndZip macro object, we created a new temporary variable called AddFlag and set its value to True in the Expression argument.

Figure 20-18 The AskEdit submacro in the ValidateCitySetStateAndZip macro uses a temporary variable to indicate that the CityInformation form has been opened in data entry mode.

The AskEdit submacro runs from the BeforeUpdate event of the City combo box on the WeddingList form when the user enters a new city name that isn’t in the row source. The macro first executes a MsgBox function in the condition of the first action to ask the user whether the new city should be added. If the user clicks the Yes button in the dialog box displayed by the MsgBox function, the function returns the value 6. (We’ll explain more about the MsgBox function later.) If the user clicks No, the macro halts. When the user clicks Yes, the submacro calls the IsFormLoaded custom Visual Basic function (in the modUtility module object) to determine whether the CityInformation form is open. If it is, the submacro closes it. The submacro then opens the CityInformation form in data entry mode and copies the new city name from the WeddingList form to the CityInformation form.

The application uses the AddFlag variable to let code in another macro know that this macro has closed and reopened the CityInformation form in data entry mode. The RefreshCityList submacro that executes in response to the AfterInsert event in the CityInformation form is also stored in the ValidateCitySetStateAndZip macro. The RefreshCityList submacro tests the AddFlag variable set in the AskEdit submacro. Scroll down the macro design surface until you come to the RefreshCityList submacro, as shown in Figure 20-19.

Figure 20-19 The RefreshCityList submacro in the ValidateCitySetStateAndZip macro tests and sets temporary variables.

In the If conditional expression for the first action in this submacro, you can see the following expression:

Not [TempVars]![AddFlag]

This test checks to see whether the AddFlag temporary variable has been set. If not, then the user must be using the CityInformation form to add a new record independent of the WeddingList form, so the submacro closes the form and stops (the StopAllMacros action). If the AddFlag temporary variable is true, the submacro resets the AddFlag temporary variable to False, sets another temporary variable, RequeryFlag, to let the macro that responds to the AfterUpdate event of the City combo box to do a requery, and closes the CityInformation form.

Note the special syntax you need to use to reference a temporary variable anywhere other than in an action specifically related to temporary variables. When you create a temporary variable in a macro, Access adds the variable to the special collection of the database called TempVars. When an object is a member of a collection (Access treats temporary variables as objects), you can reference the object by naming the collection, using an exclamation point separator, and then naming the object. So, to reference a temporary variable in macros, queries, event procedures, and even Visual Basic code, use the following syntax:

[TempVars]![<name of temporary variable>]

You can have as many as 255 temporary variables defined at any time in your Access 2010 database. By using temporary variables in the various submacros in the ValidateCitySetStateAndZip macro object, you can change the way Access executes the various macro actions based on actions taken in other macros and submacros.

If you need to clear the value stored in a temporary variable and delete the variable, you can use the RemoveTempVar macro action. The RemoveTempVar action requires only one argument—Name—and it clears any value stored in the temporary variable of that name and then deletes the variable. If you need to delete all temporary variables from memory, you can use the RemoveAllTempVars action. This action requires no arguments because it clears all temporary variables, similar to what would occur if you closed the database.

Although removing a temporary variable technically deletes it from the TempVars collection, you won’t get an error if you attempt to reference a temporary variable that doesn’t exist. If you attempt to fetch the value of a nonexistent temporary variable, Access returns the value Null. For this reason, you should be careful when naming and using temporary variables. If you set a temporary variable in one macro and then think you’re referencing the same variable in another macro but slightly misspell the variable name, you won’t get the results you expect.