Automating a Client Application Using Macros in Microsoft Access 2010

Making Your Application Come Alive with Macros

Throughout this book, you’ve learned how to perform common tasks by using ribbon commands or by finding the object you want in the Navigation pane and opening it. In working with your database, you’ve probably also noticed that you perform certain tasks repeatedly or on a regular basis. You can automate these tasks by creating macros to execute the actions you perform and then associating the macros with various form or control events, such as the Current event of a form, the Click event of a command button, or the DblClick event of a text box. In the following sections, you’ll use examples from the Wedding List Macro sample database (WeddingMC.accdb) to understand how macros can help automate your application.

Referencing Form and Report Objects

As you create macros to automate tasks that you repeat frequently, you’ll often need to refer to a report, a form, or a control on a form to set its properties or values. Before we dig into some of the macros in the Wedding List Macro, you need to know how to code these references. You can find the syntax for referencing reports, forms, report and form properties, controls, and control properties in the following sections.

Rules for Referencing Forms and Reports

You can refer to a form or a report by name, but you must first tell Access which collection contains the named object. Open forms are in the Forms collection, and open reports are in the Reports collection. To reference a form or a report, you follow the collection name with an exclamation point to separate it from the name of the object to which you are referring. You must enclose an object name that contains blank spaces or special characters in brackets ([ ]). If the object name contains no blanks or special characters, you can simply enter the name. However, it’s a good idea to always enclose an object name in brackets so that your name reference syntax is consistent.

For example, you refer to a form named WeddingList as follows:

Forms![WeddingList]

You refer to a report named WeddingList as follows:

Reports![WeddingList]

Rules for Referencing Form and Report Properties

To reference a property of a form or a report, follow the form or report name with a period and the property name. You can see a list of most property names for a form or a report by opening the form or the report in Design or Layout view and displaying the property sheet while you have the form or the report selected. With macros, you can change most form or report properties while the form is in Form view, or from the Print, Format, and Paint events of a client report as Access prints or displays it.

You refer to the Scroll Bars property of a form named CityInformation as follows:

Forms![CityInformation].ScrollBars

You refer to the Caption property of a report named CityInformation as follows:

Reports![CityInformation].Caption

Rules for Referencing Form and Report Controls and Their Properties

To reference a control on a form or a report, follow the form or report name with an exclamation point and then the control name enclosed in brackets. To reference a property of a control, follow the control name with a period and the name of the property. You can see a list of most property names for controls by opening a form or a report in Design or Layout view, selecting a control (note that different control types have different properties), and opening its property sheet. You can change most control properties while the form is in Design view.

You refer to a control named State on the WeddingList form as follows:

Forms![WeddingList]![State]

You refer to the Visible property of a control named Accepted on a report named WeddingList as follows:

Reports![WeddingList]![Accepted].Visible

Rules for Referencing Subforms and Subreports

When you embed a subform in a form or a report, the subform is contained in a subform control. A subreport embedded in a client report is contained in a subreport control. You can reference a subform control or a subreport control exactly as you would any other control on a form or a report. For example, suppose you have a subform called RelativesSub embedded in the WeddingList form. You refer to the subform control on the WeddingList form as follows:

Forms![WeddingList]![RelativesSub]

Likewise, you can reference properties of a subform or a subreport by following the control name with a period and the name of the property. You refer to the Visible property of the RelativesSub subform control as follows:

Forms![WeddingList]![RelativesSub].Visible

Subform controls have a special Form property that lets you reference the form that’s contained in the subform control. Likewise, subreport controls have a special Report property that lets you reference the report contained in the subreport control. You can follow this special property name with the name of a control on the subform or the subreport to access the control’s contents or properties. For example, you refer to the LastName control on the RelativesSub subform as follows:

Forms![WeddingList]![RelativesSub].Form![LastName]

You refer to the FontWeight property of the LastName control as follows:

Forms![WeddingList]![RelativesSub].Form![LastName].FontWeight

Opening a Secondary Form

As you learned in Chapter 12, “Using Forms in an Access Application,” it’s easier to work with data by using a form. You also learned in Chapter 15, “Advanced Form Design,” that you can create multiple-table forms by embedding subforms in a main form, thus allowing you to see related data in the same form. However, it’s impractical to use subforms in situations such as the following:

• You need three or more nested subforms to see related data.

• The main form is too small to display the entire subform.

• You need to see the related information only some of the time.

The solution is to use a separate form to see the related data. You can open this form by creating a macro that responds to one of several events. For example, you can use a command button or the DblClick event of a control on the main form to give your users access to the related data in the secondary form. This technique helps reduce screen clutter, makes the main form easier to use, and helps to speed up the main form when you’re moving from record to record.

You could use this technique in the WeddingList form. It would be simple to create a macro that would respond to clicking the City Info button by opening the CityInformation form and displaying all records from the CityNames table, including the best airline to take and the approximate flying time from each city to Seattle, Washington. However, if you’re talking to your friend Jane in Albuquerque, New Mexico, it would be even more convenient for the CityInformation form to display only Albuquerque-related data rather than the data for all cities. In the following section, you’ll create a macro that opens the CityInformation form based on the city that’s displayed for the current record in the WeddingList form.

Creating the SeeCityInformation Macro

Open the Wedding List Macro sample database (WeddingMC.accdb) if you’ve closed it. Click OK in the opening message so that no objects are opened. Click the Macro button in the Macros & Code group on the Create tab to begin creating a new macro object. When the Logic Designer window opens, collapse the Navigation pane. Figure 20-28 shows the macro you are going to create. (If you simply want to view the macro, it is saved as XmplSeeCityInformation in the sample database.)

The macro contains only one action, OpenForm. The OpenForm action not only opens the CityInformation form but also applies a filter so that the city that will be displayed matches the city currently displayed in the WeddingList form. Add an OpenForm action to the macro design surface by dragging the action from the Action Catalog or by selecting OpenForm in the Add New Action combo box. In the Where Condition argument, enter the following expression:

[CityName]=Forms![WeddingList]![City]

The Where Condition argument causes the OpenForm action to open the CityInformation form showing only the rows in the form’s record source whose CityName field equals the value currently shown in the City combo box on the open the WeddingList form. (Later, you’ll learn how to create a macro to synchronize these two forms as you move to different rows in the WeddingList form.)

Set the rest of the action arguments for the OpenForm action, as shown in Figure 20-28. After you finish creating the action for the macro, it’s a good idea to add Comment blocks to the macro design surface to document your macro. Documenting your macro makes it easier to debug, modify, or enhance the macro in the future. It’s also easier to read in English what each macro action does rather than have to view the arguments for each action line by line. Refer to Figure 20-28 and enter the information displayed into several Comment blocks. You can see that we’ve added comments about the macro in general and about the specific action the macro is designed to perform. Click the Save button on the Quick Access Toolbar, and save the macro as SeeCityInformation.

Next, you can associate the macro with the City combo box control on the WeddingList form. Click the WeddingList form in the Navigation pane, right-click the name, and click Design View to open the form in Design view. Click the City combo box control, and then click the Property Sheet button in the Tools group on the Design tab. When the property sheet opens, click the Event tab. You’ll want to trigger the SeeCityInformation macro you just created from the DblClick event, so click the On Dbl Click property box, and select the macro from the On Dbl Click event property’s drop-down list. You’ll find a macro called SeeCityInfo already entered here, as shown in Figure 20-29. We created a slightly different version of the macro and saved it in the form so that the application is fully functional when you first open it. You can change the event property to your macro (SeeCityInformation) to test what you’ve built.

You can also associate the macro with the City Info button by changing the button’s On Click event property to point to the macro. To do this, click Save on the Quick Access Toolbar to save your changes and then switch to Form view. Scroll down one or two records, and double-click the City combo box. The CityInformation form opens, and the data displayed should be for the city in the current record in the WeddingList form. Your screen should look like Figure 20-30.

Linking two related forms in this manner is very useful, but what happens to the data displayed in the CityInformation form when you move to a new record in the WeddingList form? Try scrolling through the records using the record selector. You’ll find that the data in the CityInformation form changes as you move through records in the WeddingList form. The data changes because we’ve set one of the events on the WeddingList form to execute a macro that keeps the data displayed on the two forms synchronized. In the next section, you’ll walk through the steps to re-create this macro yourself. Close the two forms that are currently open to continue with the next section.

Synchronizing Two Related Forms

In the previous section, you learned how to open a secondary form from a main form based on matching values of two related fields in the two forms. In the following sections, you’ll create a macro that synchronizes the data in a companion form when the selected record changes in a main form.

Creating the SyncWeddingListAndCity Macro

Click the Macro button in the Macros & Code group on the Create tab to start creating a new macro object. Figure 20-31 shows the actions and arguments you’ll create for this macro. Note that in Figure 20-31, we collapsed most of the actions so you can see all the macro logic. (You can find this sample macro saved as XmplSyncWeddingAndCity.)

You’ll create this macro in the same basic manner that you created the SeeCityInformation macro. Enter the needed conditional expressions into the two If blocks, and add the actions from the Action Catalog (listed in Table 20-2 in the Action column). Type the associated arguments options, listed in the Setting column in Table 20-2, into the action arguments on the macro design surface. (You can ignore typing in the comments for this example.)

Table 20-2 Actions, Arguments, and Settings in SyncWeddingAndCity

If Condition	Action	Argument	Setting
Not IsFormLoaded (“CityInformation”	StopMacro
IsNull([Forms]! [WeddingList]! [City])	SelectObject	Object Type Object Name In Database Window	Form CityInformation No
	SetValue	Item	[Forms]![CityInformation]. [Visible]
		Expression	False
Else	SelectObject	Object Type Object Name In Database Window	Form CityInformation No
	Requery
	SetTempVar	Name Expression	AddFlag False
	SelectObject	Object Type	Form
		Object Name	WeddingList
		In Database Window	No

This macro has a couple of If block conditional expressions that determine which parts of the macro execute. The first If block condition uses the IsFormLoaded function, which is included in the modUtility module of the Wedding List Macro database. This function checks to see whether a form (whose name you’ve provided to the function) is currently open. (The form can be hidden.) The syntax for the function is IsFormLoaded(“formname”), where formname is the name of the form in question. You must enclose the name of the form in double quotation marks for the function to work. The Not before the function expression tells Access to evaluate the converse of the True/False value returned from the function. So, this condition will be true only if the form is not loaded. If the companion CityInformation form isn’t open, there’s nothing to synchronize, so the macro action inside the If block—StopMacro—executes and the macro ends.

Now that we know the companion CityInformation is open, we need to decide whether the value on which that form is filtered is valid. Remember, when you created the SeeCityInformation macro that opens the CityInformation form, you included a Where Condition to filter what’s displayed in the CityInformation form to match the city in the current record in the WeddingList form. However, it’s a bad idea to reference an empty value in a Where Condition argument. In fact, in some cases you’ll get an error message. When you move beyond the last row in the WeddingList form or click New Record under the Go To button in the Find group on the Home tab, you’ll be in a new blank row in which the City field has no value. In this case, if you force the CityInformation form to refresh, it will go blank because it’s a read-only form and there will be no rows returned if the filter compares to an empty value.

It probably makes more sense to test for an empty, or Null, value and hide the companion form if you’re in a new row in the WeddingList form. The second If block conditional expression in this macro uses the IsNull built-in function to check for this condition. If City is Null, the macro hides the CityInformation form by using the SetValue action. We reference the CityInformation form’s Visible property in the Item argument and use False in the Expression argument to hide the form. After the macro hides the CityInformation form, the macro ends because the rest of the macro actions exist inside the Else block. Note that the form is still open even though you can’t see it. If you move back to a row in the WeddingList form that contains data, this macro executes again, but the actions to hide the CityInformation form will be skipped because the City field won’t be Null anymore.

The CityInformation form displays the city details for the current record in the WeddingList form because your macro opened the CityInformation form with a filter pointing to the City control on the WeddingList form. However, the CityInformation form doesn’t “know” when you move to a different record in the WeddingList form, so Access never reapplies the filter. Access does save the Where Condition argument you specified in the Filter property of the CityInformation form. To display the appropriate city information when the user moves to a new record in the WeddingList form, all you need to do is requery the CityInformation form to make Access reevaluate the filter. In the Else block of the second If condition, the macro selects the CityInformation form to make sure it has the focus (this also reveals the form if it was hidden) and then executes a Requery action with no value specified in the Control Name argument. With no control name specified, Access knows to requery whatever form or report has the focus.

Finally, the SetTempVar action sets a value that’s tested by other macros, and the SelectObject action ensures that the form has the focus after setting the value of the AddFlag temporary variable. We’ll explain more about using SetTempVar in “Passing Status Information Between Linked Forms,” on page 1245.

After you have the synchronization macro you need, save it as SyncWeddingAndCity. The last step is to associate the macro with the Current event of the WeddingList form. To do that, right-click the WeddingList form in the Navigation pane, and click Design View to open the form in Design view. Click the Property Sheet button in the Tools group on the Design tab to open the property sheet for the form, and then click the On Current property box. Use the list to select your SyncWeddingAndCity macro. (You’ll find the example XmplSyncWeddingAndCity macro set in this property in the form.) Your screen should look like the one shown in Figure 20-32.

When you finish, save and close the form. Open the form in Form view, double-click the City combo box control and move to the second record. Your screen should look like the one shown in Figure 20-30 on page 1234, assuming that Jane Crowley’s record is the current one.

Test the macro by moving through the records in the WeddingList form. As you move from record to record, the data in the CityInformation form should change to reflect the city displayed in the current record of the WeddingList form. If you move to the blank record at the end of the recordset, the CityInformation form disappears. Move back to a row containing data, and it reappears.

Using a macro to synchronize two forms containing related data is a technique that works well with almost any set of forms, and you can use it in a number of situations. In the next section, you’ll learn how to create a more complex macro set of named submacros within a macro object. When you arrange submacros by task into macro objects, you’ll see that this is a good way to organize your work and to keep from cluttering your database with dozens of macro objects.

Validating Data and Presetting Values

Two tasks you’ll commonly automate in your applications are validating data that a user enters in a field and automatically setting values for specific fields. You’ll now explore several macro objects saved in the sample database and learn how they perform these tasks on both the WeddingList form and the CityInformation form.

Validating Data

A problem you’ll often encounter when you create database applications is ensuring that the data the users enter is valid. Three types of invalid data are unknown entries, misspelled entries, and multiple versions of the same entry:

• Unknown entries A good example of this error is an entry such as AX in a state field. No state name is abbreviated as AX, but a user who tries to enter AZ might accidentally hit the X key instead of the Z key.

• Misspelled entries This sort of error is quite common among users with poor typing or spelling skills and among very fast typists. In this case, you might see entries such as Settle, Seatle, or Saettle for Seattle.

• Multiple versions These errors are common in poorly designed databases and in databases that are shared by a number of users. You might see entries such as ABC Company, Inc.; ABC Company, Incorporated; ABC Co., Inc.; or A B C Company Inc.

You can use macros to validate data and help reduce errors. In the next section, you’ll create a macro for the WeddingList form that validates the city that the user enters in the City field. If the city doesn’t exist in the CityNames table, the macro then executes the following steps:

1. It displays a message indicating that the city is currently unlisted and asks whether the user wants to enter a new city name.

2. If the user wants to create a new city record, another macro runs that opens the CityInformation form in data entry mode and copies the city name that the user just typed.

3. If the user successfully saves a new row, a macro associated with the AfterInsert event of the CityInformation form sets a temporary variable.

4. Back in the WeddingList form, the city name gets revalidated, and if the city entry is a new one, a macro triggered by the AfterUpdate property of the City field sets the combo box to the new name. When the city name is validated, this macro also automatically enters the state name and the first three digits of the ZIP code.

Understanding the ValidateCitySetStateAndZip Macro

In the Navigation pane, find the ValidateCitySetStateAndZip macro and open it in Design view. Figure 20-33 shows the first submacro and its associated actions.

The first three lines of the macro are comments, and TestCity is the name of the first submacro in the object. You can see the actions for this submacro inside the If block listed in Table 20-3.

Table 20-3 Actions, Arguments, and Settings in the TestCity Submacro

Action	Argument	Setting
CancelEvent
RunMacro	Macro Name	ValidateCitySetStateAndZip.AskEdit

To understand how this macro works, let’s look at the conditional expression in the If block that validates the city name for the TestCity submacro. What we want to do is look up the name just entered in the CityName field to find out whether it exists in the CityNames table. If it doesn’t exist, the first action inside the If block of the submacro executes a CancelEvent action. The second action then calls another macro that we’ll examine later.

To see this conditional expression easily, click into the expression text box for the If block and then press Shift+F2 to open the expression in the Zoom box, as shown in Figure 20-34.

This conditional expression uses two built-in functions: DLookup and IsNull. The DLookup function looks up the city name in the CityNames table. The IsNull function checks the return value of the DLookup function. If the DLookup function doesn’t find the city name, it returns a Null value. This causes the IsNull function to return a True value because the return value of the DLookup function is indeed Null. If no row in the CityNames table matches the current city name in the WeddingList form, Access then executes the actions inside the If block because the condition evaluated to True. In this case, the CancelEvent macro action tells Access not to store the new value in the City field. So if the city doesn’t exist in the CityNames table, the RunMacro action inside the If block calls the AskEdit submacro, which we’ll look at in a moment.

On the other hand, if the DLookup function does find the city name, it returns the city name to the IsNull function. The IsNull function then returns a value of False because the return value of the DLookup function is not Null. Access disregards running any actions inside the If block, and since there is no Else or ElseIf associated with this If block, the submacro ends without taking any further action.

What’s the point of all of this? If you open the WeddingList form in Design view, click the City combo box, and look at its event properties, you’ll find this macro “wired” into the Before Update property. If you remember from the previous chapter, you can use the BeforeUpdate event of a form or control to verify what’s about to be saved. If the data is not valid, you can cancel the event to tell Access not to save the change. This is exactly what the CancelEvent action of this submacro is doing.

When you don’t cancel a BeforeUpdate event on a control, Access accepts the changes and gives you a chance to look at the result in the AfterUpdate event. You don’t want to use the AfterUpdate event to validate data because the data has already been saved, but it’s perfect for filling in other fields on the form based on what the user just entered. As you’ll see later, this application uses AfterUpdate on this control to fill in the correct state and part of the ZIP code.

So what happens if the user enters a city name that’s not yet in the database? The AskEdit submacro runs, and the first step it takes is to evaluate the conditional expression in the first If block at the beginning of the submacro. The conditional expression for the first If block is as follows:

6<>MsgBox("The city you entered is not in the 

    database. Do you want to enter a new one?",36)

You’ve seen the MessageBox action before. This conditional expression uses a built-in function called MsgBox that’s a lot more powerful. The MsgBox function lets you not only display a message but also specify what icon you want displayed, and it provides several options for buttons to display in the message box. You set these options by adding number selections and providing the result as the second argument to MsgBox. In this case, 36 is the sum of 32, which asks for a question icon, and 4, which requests Yes and No buttons. (Intuitive, isn’t it?) You can find all the option settings by searching for MsgBox Function in Access Help. For your convenience, we’ve listed all the option settings for the MsgBox function in Table 20-4. In addition, the function returns an integer value that depends on the button the user clicks in the message box. If you look at the MsgBox Function help topic, you’ll find out that when the user clicks Yes, MsgBox returns the value 6. Table 20-5 shows you the MsgBox return value settings. So if the user doesn’t click Yes, the action inside the first block—a StopAllMacros action—executes, and the macro ends. If the user does click Yes, the rest of the submacro executes. Table 20-6 lists all the actions and arguments for this submacro.

Table 20-4 Option Settings for the MsgBox Function

Value	Meaning
	BUTTON SETTINGS (CHOOSE ONE)
0	OK button only
1	OK and Cancel buttons
2	Abort, Retry, and Ignore buttons
3	Yes, No, and Cancel buttons
4	Yes and No buttons
5	Retry and Cancel buttons
	ICON SETTINGS (CHOOSE ONE)
0	No icon
16	Critical (red X) icon
32	Warning query (?) icon
48	Warning message (!) icon
64	Information message (letter i) icon
	DEFAULT BUTTON SETTINGS (CHOOSE ONE)
0	First button is the default
256	Second button is the default
512	Third button is the default

Table 20-5 Return Values for the MsgBox Function

Value	Meaning
1	OK button clicked
2	Cancel button clicked
3	Abort button clicked
4	Retry button clicked
5	Ignore button clicked
6	Yes button clicked
7	No button clicked

Table 20-6 Actions, Arguments, and Settings in the AskEdit Submacro

Action	Argument	Setting
StopAllMacros
Close	Object Type	Form
	Object Name	CityInformation
	Save	Prompt
OpenForm	Form Name	CityInformation
	View	Form
	Data Mode	Add
	Window Mode	Normal
SetValue	Item	[Forms]![CityInformation]![CityName]
	Value	[Forms]![WeddingList]![City]
GoToControl	Control Name	State
SetTempVar	Name	AddFlag
	Expression	True

The AskEdit submacro contains several actions that Access executes if the user enters the data for a new city name and responds by clicking Yes on the MsgBox that asks whether the user wants to add the new city. The submacro uses the IsFormLoaded function you saw earlier inside the second If conditional expression to determine whether the CityInformation form is open. If it is, the submacro instructs Access to close the form. Next, Access opens the CityInformation form in Add mode and copies the city name from the WeddingList form to the CityName field of the CityInformation form by using the SetValue action. (Note that SetValue has an exclamation mark icon on the macro design surface to the left of the action name indicating Access will not run this action in a database that is not trusted.) SetValue inserts the city name that the user typed for user convenience and to ensure that the user starts with the city name just entered. After the submacro copies the city name to the CityName field, it tells Access to move the focus to the State field using the GoToControl action. Finally, the submacro creates a temporary variable called AddFlag and sets the value to True to indicate that the CityInformation form is now opened in data entry mode. The submacro attached to the AfterInsert event checks this temporary variable to determine whether it should notify the AfterUpdate event of the City control on the WeddingList form to refresh its list.

Passing Status Information Between Linked Forms

As you just saw, the AskEdit submacro creates a temporary variable called AddFlag to tell the CityInformation form’s AfterInsert event macro that the WeddingList form needs to know whether a new row has been added successfully. Likewise, when the user adds a new row using the CityInformation form, the submacro that runs in response to an AfterInsert event (the event that Access uses to let you know when a new row has been added via a form) needs to check the flag and pass an indicator back to the submacro that responds to the AfterUpdate event of the City combo box on the WeddingList form. You’ll learn in later chapters that you can also do this sort of “status indicator” passing by using variables in Visual Basic procedures.

Figure 20-35 shows the submacro that you need to respond to the AfterInsert event of the CityInformation form. You might recall from Chapter 19, “Understanding Event Processing,” that Access triggers this event right after it has saved a new row. You could save the row by clicking Save in the Records group on the Home tab, moving to a new row, or closing the form. The If block at the beginning of the RefreshCityList submacro has a conditional expression that tests to be sure that the user asked to add a new row. The conditional expression is as follows:

Not [TempVars]![AddFlag]

If the AddFlag temporary variable is not true, the actions inside the If block close the form, and then the StopAllMacros action causes the submacro to end. If the variable is true, the SetTempVar action creates another temporary variable called RequeryFlag and sets the flag to let the submacro that responds to the AfterUpdate event of the City combo box know that it must refresh the list in the combo box at its earliest opportunity. Finally, the submacro closes the CityInformation form. Remember that the AfterInsert event could be triggered as a result of clicking the form’s Close button after entering new data. Normally, you would expect an error if you try to execute a Close command while the form is already in the process of closing (you will get an error in Visual Basic). Access does not generate any error from either of the Close actions in this submacro if this is the case.

If the user triggers the AfterInsert event by moving to another row, closing the form makes sense after adding the one row you need. If the user closes the form without entering any new data, the AfterInsert event won’t happen. The user will be back in the WeddingList form with the unmatched city data still typed in the City combo box. If the user attempts to save the unmatched name again, the BeforeUpdate event runs the TestCity submacro that cancels the update when the city isn’t in the CityName table. The user must either add the new value or enter a value in the list.

As a final touch, the SetTempVar action in the SyncWeddingAndCity macro that you created in Figure 20-31 (on page 1235) sets the AddFlag temporary variable to False when you move to a new row on the WeddingList form. When you have just moved to a new row, you clearly aren’t worried about adding a new row to the CityNames table. Also, there’s a SelectObject action in the macro to make sure the focus is back on the WeddingList form after the macro updates the temporary variable.

Presetting Values

Validating data is just one of the many ways you can ensure data integrity in a database. Presetting values for certain fields is another way. Although you can set the Default property of a field, sometimes you’ll need to set the value of a field based on the value of another field in a form. For example, you’ll want to set the values of the State field and the Zip field in the WeddingList form based on the value of the City field. You can accomplish this with a macro.

In this section, you’ll examine actions in the ValidateCitySetStateAndZip submacros that set the values of the State and Zip fields in the WeddingList form based on the city entered. If you scroll down the macro design surface, you can see the additional actions, as shown in Figure 20-36.

Table 20-7 lists the actions and arguments in this submacro.

Table 20-7 Actions, Arguments, and Settings in the SetStateAndZip Submacro

Action	Argument	Setting
SetValue	Item	[State]
	Expression	DLookup(“[State]”,“[CityNames]”,“[CityNames].[CityName]=City”
SetValue	Item	[Zip]
	Expression	DLookup(“[Zip]”,“[CityNames]”,“[CityNames].[CityName]=City”
GoToControl	Control Name	Zip
SetValue	Item	[Forms]![WeddingList]![Zip].[SelStart]
	Expression	255
Requery	Control Name	City
SetTempVar	Name	RequeryFlag
	Expression	False

When the user enters a valid city name, the first SetValue action uses the DLookup function to retrieve the matching State value from the CityNames table. If the value for State isn’t blank or Null in the conditional expression for the first If block, the second SetValue action retrieves the first three digits of the ZIP code from the table, moves the focus to the Zip control with a GoToControl action, and sets the SelStart property of the Zip control to a high value (255) to place the cursor at the end of the data displayed in the control. Pressing the F2 key after you move to a control also places the cursor at the end of the data in the control, so you could use a SendKeys action here instead. However, setting the SelStart property is faster and more reliable. The user can now enter the last two digits of the ZIP code on the main form before moving on to the Expected field. The conditional expression in the first If block is as follows:

Not ([State]="" Or [State] Is Null)

The set of submacros in this macro object is now complete. You can see how these submacros help implement data integrity by validating data and presetting specific values. This decreases the likelihood that users will make errors. Now, you’ll see how to associate these submacros with the appropriate events on the WeddingList form and the CityInformation form.

Right-click the WeddingList form in the Navigation pane, and click Design View to open the form in Design view. Click the City combo box control, and then click the Property Sheet button in the Tools group on the Design tab. After the property sheet opens, click the Event tab. You should see the ValidateCitySetStateAndZip.TestCity submacro associated with the BeforeUpdate event of the City combo box. Remember, this is the macro you should run to verify whether the user has entered a valid city name. The AfterUpdate event property should be set to ValidateCitySetStateAndZip.SetStateAndZip. This submacro automatically sets the matching State and Zip values whenever the user specifies a new City value. Figure 20-37 shows the result.

Close the WeddingList form. Open the CityInformation form in Design view, and click the Property Sheet button in the Tools group on the Design tab to open the property sheet. The ValidateCitySetStateAndZip.RefreshCityList macro is set in the form’s After Insert event property, as shown in Figure 20-38. Recall from Chapter 19 that you could also use the form’s AfterUpdate event to see changed data. However, in this case, you don’t care about existing rows that change. The AfterInsert event is more appropriate because Access fires this event only when a new row is saved, not when an existing row is saved.

Close the CityInformation form. Now that you’ve verified that the macros are associated with the appropriate objects and events, you’re ready to test how this works. Begin by closing all open objects, and then double-click the AutoexecXmpl macro in the Navigation pane to run the macro and open the WeddingList form. Move to a new record in the WeddingList form, and enter a title, a name, an address, and a group. When the cursor moves to the City combo box, enter Miami. After you press Enter or Tab, Access runs the ValidateCitySetStateAndZip.TestCity submacro. Because this city doesn’t currently exist in the CityNames table, the AskEdit submacro runs, and Access displays the message box shown in Figure 20-39.

After you click Yes, Access executes the remaining actions in the submacro. Access opens the CityInformation form in data entry mode, copies the city name to the City field of the form, and moves the cursor to the State field. Figure 20-40 shows the result of these actions.

After you enter information in the remaining fields and close the CityInformation form, the AfterInsert event of the form triggers the ValidateCitySetStateAndZip.RefreshCityList submacro. After the form closes, Access moves the focus back to the WeddingList form. When you finally leave the now valid City control, the macro triggered by AfterUpdate requeries the City combo box control and automatically updates the State and Zip fields.

Article 6, on the companion CD, summarizes all the actions you can include in client and web macros. You’ll find it useful to browse through that article to become familiar with the available actions and events as you automate your applications with macros.