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 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 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

Note 

The names of properties do not contain embedded blank spaces, even though the property sheet shows blanks within names. For example, BackColor is the name of the property listed as Back Color in the property sheet.

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 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 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 10, “Using Forms,” it’s easier to work with data by using a form. You also learned in Chapter 13, “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 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 CityInfor-mation 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). Click OK on the opening message so that no objects are opened. Click the arrow on the New Object button in the Other group on the Create tab, and select Macro from the list of three options. (The top half of the New Object button displays the last type of new object created-Macro, Module, or Class Module. If you see the Macro icon in the top half of the New Object button, you can click that button to begin creating a new macro.) When the Macro window opens, collapse the Navigation Pane. Next, click the Macro Names button and the Conditions button in the Show/Hide group on the Design tab to display the Macro Name and Condition columns in the Design window. Although you won’t use these columns for this macro, it’s a good idea to get in the habit of displaying them because you will use them often when creating new macros.

Inside Out-Always Show Macro Names and Conditions 

You can display the Macro Name and Condition columns by default by clicking the Microsoft Office Button, clicking Access Options, clicking the Advanced category, and selecting both the Names Column and Conditions Column check boxes under Show In Macro Design in the Display section. The next time you create a macro, the columns will be displayed automatically.

Figure 18–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.)

image from book
Figure 18–28: When triggered from an event on the WeddingList form, this macro opens the CityInformation form filtered on the city name.

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. Click in the Action column, and then choose OpenForm from the list of actions. In the Action Arguments section of the Macro window, enter the following Where Condition argument:

 [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 18–28. After you finish creating the action for the macro, it’s a good idea to use the Comment column 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 18–28, and enter the information displayed in the Comment column. 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 SeeCityInforma-tion macro 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 18–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.

image from book
Figure 18–29: Select the macro you created for the DblClick event of the City combo box control.

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. Click Save on the Quick Access Tool-bar to save your changes, switch to Form view, and then maximize the form. 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 the one shown in Figure 18–30.

image from book
Figure 18–30: The CityInformation form displays a matching city in the WeddingList form.

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.

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 SyncWeddingAndCity Macro

image from book Click the arrow on the New Object button in the Other group on the Create tab, and click Macro from the list of three options to create a new query. When the macro design window opens, maximize it so that it fills the entire screen. Figure 18–31 shows the actions and comments you’ll create for this macro. (You can find this sample macro saved as XmplSyncWeddingAndCity.)

image from book
Figure 18–31: You’ll create these conditions, actions, and comments for the SyncWeddingAndCity macro.

You’ll create this macro in the same basic manner that you created the SeeCityInformation macro. Enter the needed conditions in the Condition column, select the actions from the Action column, and type the associated comments in the Comment column. Table 18–2 lists the settings for the actions.

Note 

Some code and expression examples in this chapter are too long to fit on a single printed line. A line that ends with the image from book symbol means that the code shown on the following line should be entered on the same line.

Table 18–2: Actions, Arguments, and Settings in SyncWeddingAndCity
Open table as spreadsheet

Condition

Action

Argument

Setting

Not IsFormLoadedimage from book

(“CityInformation”)

StopMacro

   

IsNull([Forms]!image from book

[WeddingList]!image from book

[City])

SelectObject

Object Type

Object Name

In Database Window

Form

CityInformation

No

 

SetProperty

Control Name

Property

Value

Visible

0

 

StopMacro

   
 

SelectObject

Object Type

Object Name

In Database Window

Form

CityInformation

No

 

Requery

  
 

SetTempVar

Name

Expression

AddFlag

False

 

SelectObject

Object Type

Object Name

In Database Window

Form

WeddingList

No

This macro has a couple of conditions that determine which parts of the macro execute. The first 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 in order 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 on this line-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 line 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 setting the value of the Visible property to 0 (or false), and then the macro ends. On the third line, the ellipsis () in the Condition column tells Access to run this action only if the previous condition is true. This lets you enter the condition only once-and Access tests the condition only once. In this case, if the City is Null, Access runs not only the SetProperty action on line 3 but also the StopMacro action on line 4-which ends macro execution at this point. 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 and to stop 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. 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 command 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 934.

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 18–32.

image from book
Figure 18–32: Associate the SyncWeddingAndCity macro with the On Current event property of the WeddingList form.

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 18–30, 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 macros within a macro object, also sometimes referred to as a macro group. When you group macros by task, 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, OU 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 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 Group

In the Navigation Pane, find the ValidateCitySetStateAndZip macro, and open it in Design view. Be sure the Macro Name and Condition columns are displayed. Figure 18–33 shows the first macro and its associated actions.

image from book
Figure 18–33: This figure shows the Macro window for the first two macros in the ValidateCitySetStateAndZip macro group.

The first three lines of the macro are comments, and TestCity is the name of the first macro in the object. You can see the actions for this macro listed in Table 18–3.

Table 18–3: Actions, Arguments, and Settings in the TestCity Macro
Open table as spreadsheet

Action

Argument

Setting

CancelEvent

  

RunMacro

Macro Name

ValidateCitySetStateAndZip.AskEdit

To understand how this macro works, let’s take a look at the condition that validates the city name. 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 line of the macro executes a CancelEvent action. The second line then calls another macro that we’ll examine later.

To see this condition easily, click the first line of the macro in the Condition column. Press Shift+F2 to open the expression in the Zoom box, as shown in Figure 18–34.

image from book
Figure 18–34: The conditional expression in the TestCity macro uses the DLookup function to try to find the city in the CityNames table.

This condition 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 action associated with this condition because the condition evaluated to True. In this case, the CancelEvent macro action tells Access not to store the new value in the CityName field. By including an ellipsis () in the Condition column of the second action, you tell Access to run the second action only if the previous condition is true. (When you use an ellipsis, you enter the condition only once, and Access performs the evaluation only once.) So if the city doesn’t exist in the CityNames table, the RunMacro action on the second line calls the AskEdit macro, 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 the action associated with this condition. Because you included an ellipsis on the second condition line, the False evaluation applies there also, so the macro 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 first line of this macro 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. The AfterUpdate event isn’t what you want to use 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.

Inside Out-Why Aren’t We Using the NotInList Event to Test for a New City Name? 

The NotInList event occurs when the user types a name that’s not in the row source of a combo box. The CityNames table is the row source of the City combo box, so NotInList seems to be an ideal choice to detect a name that’s not in the CityNames table. But for NotInList to work properly, you need to be able to return a response code to the event to let Access know whether you’ve handled the problem and inserted a new city name. You can do that only in Visual Basic code, not in a macro. We set the Limit To List property of the combo box to No so that the NotInList event never happens. By trapping the problem in the BeforeUpdate event of the combo box, we can test the value and take appropriate action without having to return a response code to Access. As you’ll learn later in the Visual Basic chapters, the NotInList event is a much better choice as long as you can return a response code.

So what happens if the user enters a city name that’s not yet in the database? The AskEdit macro runs, and the first step it takes is to evaluate another condition. As you’ll learn in later chapters, this sort of IFTHENIF logic testing is much easier to do with procedures written in Visual Basic. With macros, you have to do the first test in one macro, and if that returns a True value, you have to call another nested macro to perform a further test.

The condition on the first line of the AskEdit macro is as follows:

 6<>MsgBox("The city you entered is not in the image from book     database. Do you want to enter a new one?", 36)

You’ve seen the MsgBox action before. This condition 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 18–4. In addition, the function returns an integer value depending 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 18–5 shows you the MsgBox return value settings. So if the user doesn’t click Yes, the first line of this macro-a StopAllMacros action-executes, and the macro ends. If the user does click Yes, the rest of the macro executes. Table 18–6 lists all the actions and arguments for this macro.

Table 18–5: Return Values for the MsgBox Function
Open table as spreadsheet

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 18–6: Actions, Arguments, and Settings in the AskEdit Macro
Open table as spreadsheet

Action

Argument

Setting

StopAllMacros

Close

Object Type

Object Name

Save

Form

CityInformation

Prompt

OpenForm

Form Name

View

Data Mode

Window Mode

CityInformation

Form

Add

Normal

SetValue

Item

Value

[Forms]![CityInformation]![CityNanne]

[Forms]![WeddingList]![City]

GoToControl

Control Name

State

SetTempVar

Name

Expression

AddFlag

True

The AskEdit macro contains several actions that Access executes if the user enters the data for a new city name and responds by clicking Yes in the MsgBox that asks whether the user wants to add the new city. The macro uses the IsFormLoaded function you saw earlier to determine whether the CityInformation form is open. If it is, the macro 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 selection button to the left of the action line indicating Access will not run this action in a database that is not trusted.) SetValue inserts the city name the user typed for user convenience and to ensure that the user starts with the city name just entered. After the macro 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 macro 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 macro 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 macro 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 macro 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 macro 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 18–35 shows the macro you need in order to respond to the AfterInsert event of the CityInformation form. You might recall from the previous chapter 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 first line has a condition that tests to be sure that the user asked to add a new row. The condition is as follows:

 Not [TempVars]![AddFlag]

image from book
Figure 18–35: The RefreshCityList macro sets a temporary variable to indicate a requery is needed.

If the AddFlag temporary variable is not true, the first action closes the form, and the StopAllMacros action causes the macro to end. If the variable is true, the SetTempVar action creates another temporary variable called RequeryFlag and sets the flag to let the macro 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 macro 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). Because Access assumes that macros are most often used by beginning programmers, it is kind enough not to generate any error from either of the Close actions in this macro 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 macro that cancels the update when the city isn’t in the CityNames 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 18–31 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 macro group 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 window, you can see the additional actions, as shown in Figure 18–36.

image from book
Figure 18–36: The SetStateAndZip macro uses SetValue actions to automatically fill in the State and Zip controls.

Table 18–7 lists the actions and arguments in this macro.

Table 18–7: Actions, Arguments, and Settings in the SetStateAndZip Macro
Open table as spreadsheet

Action

Argument

Setting

SetValue

Item

Expression

[State]

DLookup("[State]","[CityNames]",image from book

"[CityNames].[CityName]=City”)

SetValue

Item

Expression

[Zip]

DLookup("[Zip]","[CityNames]",image from book

"[CityNames].[CityName]=City”)

GoToControl

Control Name

Zip

SetValue

Item

Expression

[Forms]![WeddingList]!image from book

[Zip].[SelStart]

255

Requery

Control Name

City

SetTempVar

Name

Expression

RequeryFlag

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, 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 insertion point at the end of the data displayed in the control. Pressing the F2 key after you move to a control also places the insertion point 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. (See Access Help for more information about the SelStart property.) 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 Condition column for the second action is as follows:

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

The set of macros in this macro object is now complete. You can see how these macros 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 macros 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 macro 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 macro automatically sets the matching State and Zip values whenever the user specifies a new City value. Figure 18–37 shows the result.

image from book
Figure 18–37: The Before Update and After Update event properties for the City control on the WeddingList form are set to run macros in the ValidateCitySetStateAndZip macro.

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 18–38. Recall from the previous chapter 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, but not when an existing row is saved.

image from book
Figure 18–38: The ValidateCitySetStateAndZip.RefreshCityList macro executes when the AfterInsert event of the CityInformation form occurs.

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 insertion point moves to the City combo box, enter Miami. After you press Enter or Tab, Access runs the ValidateCitySetStateAndZip.TestCity macro. Because this city doesn’t currently exist in the CityNames table, the AskEdit macro runs, and Access displays the message box shown in Figure 18–39.

image from book
Figure 18–39: The AskEdit macro displays a message box if you enter a new city.

After you click the Yes button, Access executes the remaining actions in the macro. Access opens the CityInformation form in Data Entry mode, copies the city name to the CityName text box control on the form, and moves the insertion point to the State field. Figure 18–40 shows the result of these actions.

image from book
Figure 18–40: The AskEdit macro then opens the CityInformation form where you can enter the details of the new city.

After you enter information in the remaining fields and close the CityInformation form, the AfterInsert event of the form triggers the ValidateCitySetStateAndZip.RefreshCityList macro. 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.




Microsoft Office Access 2007 Inside Out
MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
ISBN: 0735623252
EAN: 2147483647
Year: 2007
Pages: 234

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net