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 in to 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” on page 928, 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 18–18, you can see that in the AskEdit macro in the ValidateCitySetStateAndZip macro group, we created a new temporary variable called AddFlag and set its value to True in the Expression argument.

Figure 18–18: The AskEdit macro in the ValidateCitySetStateAndZip macro uses a temporary variable to indicate that the CityInformation form has been opened in Data Entry mode.

The AskEdit macro 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 macro calls the IsFormLoaded custom Visual Basic function (in the modUtility module object) to determine whether the CityInformation form is open. If it is, the macro closes it. The macro 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 macro that executes in response to the AfterInsert event in the CityInformation form is also stored in the ValidateCitySetStateAndZip macro. The macro tests the AddFlag variable set in the AskEdit macro. Scroll down the macro rows in the macro design window until you come to the RefreshCityList macro, as shown in Figure 18–19.

Figure 18–19: The RefreshCityList macro in the ValidateCitySetStateAndZip macro tests and sets temporary variables.

In the Condition column for the first action in this macro, 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 macro closes the form and stops (the StopAllMacros action). If the AddFlag temporary variable is True, the macro resets the AddFlag temporary variable to False, sets another temporary variable (RequeryFlag, not shown in the figure) to let the macro that responds to the AfterUpdate event of the City combo box 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 2007 database. By using temporary variables in the various macros in the ValidateCitySetStateAndZip macro object, you can change the way Access executes the various macro actions based on actions taken in other macros.

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.

Note 

Access 2007 allows you to create temporary variables in macros if you save your database in the .mdb file format. If, however, you open the .mdb database with an earlier version of Access-2000, 2002, or 2003-the temporary variables do not function, and you will receive error messages when your macros run. If you have users still using previous Access versions, do not use temporary variables created in macros for your application.