Associating Macros with Events

Although it's certainly possible to run an Access macro on its own, most macros run as a result of a specific event occurring. Following is a partial list of events that you can use to initiate a macro:

• Clicking a button

• Moving between fields

• Making changes in a record

• Opening or closing tables and forms

• Selecting a custom command from a custom menu

Depending on what you're trying to do, you might want a macro in a form, in a section of a form, in a report, or in a section of a report. All of these options are covered in this section.

Adding a Macro to a Form

To add a macro to a form, you can either create a control (such as a command button) that you associate the macro with, or you can edit the Event properties of the form itself, one of its sections, or one of its fields to call the macro. In each case, you must first open the form in Design view to make the necessary changes.

In Design view, display the properties sheet for the object either by clicking the object and then selecting View, Properties, or by right-clicking the object and then clicking Properties in the shortcut menu. (If you want to associate a macro with a form-level event, select the Edit, Select Form commandor press Ctrl+Rand then select View, Properties.)

In the properties sheet, display the Event tab to see a list of the events you can use to trigger the macro. For example, a macro associated with the On Click event will run whenever the user clicks the control. For each event, a drop-down list contains all the available macros, as shown in Figure 13.10.

Figure 13.10. The Event tab lists all the events available for the selected object. You can associate a macro with any of those events.

If you need to create a macro from scratch, click the ellipsis (...) button to display the Choose Builder dialog box. Select the Macro Builder option and then click OK to begin building your macro directly.

To determine which event to use, think about when you want your actions to occur and then match the macro accordingly. If you're unsure of how a specific event works, click in the field for the event and then press F1 to bring up the online help for the event.

Creating a Macro Command Button

One of the most common macro scenarios is to add a command button to the form and then associate a macro with that button so that it runs when the user clicks the button. The easiest way to set this up is to use the Command Button Wizard, as shown in the following steps:

1.	In the Toolbox, make sure the Control Wizards button is activated.
2.	Click the Command Button control and then click and drag within the form to create the button. When you release the mouse button, the Command Button Wizard appears.
3.	In the Categories list, select Miscellaneous.
4.	In the Actions list, select Run Macro.
5.	Click Next.
6.	Select the macro you want to associate with the command button and click Next.
7.	Select a button label and click Next.
8.	Type a name for the command button and click Finish.

Example: Confirming Changes to a Record

If you have a form where you don't expect users to make changes to the records, you can prevent accidental edits by asking the user to confirm any changes he or she has made. This way, the user can cancel the changes before committing them to disk.

First, create the macro as shown in Table 13.4, and save the macro with the name ConfirmChanges.

Table 13.4. An Access Macro That Asks the User to Confirm Changes

Condition	Action	Arguments	Values
MsgBox("Save changes to this record?",289)=2	Cancel Event	None
...	SendKeys	Keystroke	{ESC}

The ConfirmChanges macro's first action uses a MsgBox function in the Condition column. This function displays a message using the following syntax:

 MsgBox(Prompt[, Buttons] [, Title]) 

Prompt	The message you want to display in the dialog box. (You can enter a string up to 1,024 characters long.)
Buttons	A number that specifies, among other things, the command buttons that appear in the dialog box (explained next). The default value is 0.
Title	The text that appears in the dialog box title bar. If you omit the title, the name of the underlying application appears (for example, Microsoft Office Access).

Table 13.5 lists the values you can use for the Buttons parameter.

Table 13.5. The MsgBox Buttons Parameter Options

Value	Description
Buttons
0	Displays only an OK button. (This is the default.)
1	Displays the OK and Cancel buttons.
2	Displays the Abort, Retry, and Ignore buttons.
3	Displays the Yes, No, and Cancel buttons.
4	Displays the Yes and No buttons.
5	Displays the Retry and Cancel buttons.
Icons
16	Displays the Critical Message icon.
32	Displays the Warning Query icon.
48	Displays the Warning Message icon.
64	Displays the Information Message icon.
Default Buttons
0	The first button is the default (that is, the button selected when the user presses Enter).
256	The second button is the default.
512	The third button is the default.

You derive the Buttons argument by adding up the values for each option. For this example, we want to use a dialog box with the OK and Cancel buttons (value 1), the Warning Query icon (32), and with the Cancel button as the default (256). So the Buttons argument is 1+32+256, which equals the 289 value shown in the condition in Table 13.4.

The MsgBox function returns a value depending on which button the user clicked, as detailed in Table 13.6.

Table 13.6. The MsgBox Function's Return Values

Value	User Clicked...
1	OK
2	Cancel
3	Abort
4	Retry
5	Ignore
6	Yes
7	No

In the macro, the condition checks to see if the user pressed the Cancel button (value 2). If that's true, the macro runs the CancelEvent procedure, which prevents the event the macro is associated with from firing. The second macro action (which also runs if the MsgBox function returns 2) uses SendKeys to send an Escape keystroke to the form, which cancels any changes the user made.

With the macro set up, you now add it to your form's BeforeUpdate event (see Figure 13.11), which fires if the user has made changes to the current record and is trying to save those changes (for example, by navigating to a different record). In this case, the dialog box shown in Figure 13.12 appears so that the user can confirm or cancel the changes.

Figure 13.11. Associate the ConfirmChanges macro with the form's BeforeUpdate event.

Figure 13.12. When the user attempts to save changes to a record, the confirmation dialog box appears.

Example: Transferring Data from One Form to Another

One powerful application of macros within forms is the capability to transfer data between forms. For example, suppose you're viewing the Northwind sample database's Suppliers form, and you decide to add a new product for the current supplier. Normally you'd start this process by opening the Products form and selecting the supplier. However, with a macro you can perform these steps with the click of a button.

Table 13.7 presents a macro named AddProducts that comes with the default Northwind database (see the Suppliers macro object).

Table 13.7. A Macro That Transfers Data from One Form to Another

Action	Action Arguments	Values
Echo	Echo On	Off
Close	Object Type
	Form Object Name	Product List
	Save	No
OpenForm	Form Name	Products
	View	Form
	Data Mode	Add
	Window Mode	Normal
SetValue	Item	Forms!Products!SupplierID
	Expression	SupplierID
GoToControl	Control Name	ProductName

Following is a summary of the five actions performed by this macro:

1.	Run the Echo action and set the Echo On argument to Off. This freezes the screen display until the macro finishes, which is useful for preventing the user from seeing unnecessary information as the macro goes about its business.
2.	Run the Close action to close the Product List form, just in case (another macro in the Suppliers object opens the Product List form).
3.	Run the OpenForm action to open the Products form in Add mode.
4.	The SetValue action takes the SupplierID value from the current record in the Suppliers form (this is the Expression argument's value) and stores it in the SupplierID field of the Products form (this is the Item argument's value).
5.	The GoToControl action sets the focus on the Products form's Product Name field.

Go to Product Name, Instead

In the original version of AddProducts that ships with the Northwind sample database, the GoToControl action is used to set the focus on the Category field instead of the Product Name field. This is inefficient because the first field you need to fill in when using the Products form is the Product Name field. Therefore, I edited the macro to use the more efficient method.

In Northwind, the Suppliers form includes an Add Products button (see Figure 13.13) with which the AddProducts macro is associated (via the On Click event). In Figure 13.13, notice that the current supplier name is Exotic Liquids. When you click Add Products, the Products form that appears already has Exotic Liquids in the Supplier field, as shown in Figure 13.14.

Figure 13.13. In the Northwind sample database, the Supplier form has an Add Products button with which the AddProducts macro is associated via the On Click event.

Figure 13.14. When you click Add Products, the Products form appears with the current supplier name already filled in.

Adding a Macro to a Report

Reports are another area where macros are often very useful. Just like forms, reports have two levels of events. Figure 13.15 shows the events associated with the report object. You display this dialog box by selecting View, Select Report (or by pressing Ctrl+R) and then selecting View, Properties.

Figure 13.15. Access reports generate seven different events.

For more details on these events, you can again rely on the online help system to assist you in choosing the proper event for your macro.

Report Section Events

Within a report there are also the Report Header, the Page Header, the Detail, the Report Footer, and the Page Footer sections, each of which can have its events associated with a macro. These events are listed in Table 13.8.

Table 13.8. Report Section Events

Event	Description
On Format	Fires after Access has accumulated or calculated the data for the section but before it has printed the section.
On Print	Fires after the data in a section is laid out but before printing.
On Retreat	Fires when Access returns to a previous report section during report formatting. (This event doesn't fire with the Page Header and Page Footer sections.)

Example: Calculating Page Totals

One of the most frustrating quirks of the Access reporting engine is that is doesn't allow you to sum a field over a page. That is, if you place an unbound text box in the Page Footer section and include a Sum function that totals one of the report fields, Access always displays #Error as the result. Dumb! Fortunately, you can work around this limitation by using a macro that does the summing for you.

The first thing you need to do is insert an unbound text box in the Page Footer section of your report. Right-click the text box, click Properties, display the Other tab, and then change the Name property to PageTotal, as shown in Figure 13.16.

Figure 13.16. Create an unbound text box named PageTotal in your report's Page Footer section.

For more information on using calculated text boxes in reports, see "Adding Calculations to a Report," p. 174

Next, you need to create two macros that you'll later associate with events in the report. Because both macros are related, you should place them both into a single macro object and use the Macro Name column to separate them. Table 13.9 shows the macros. (You can find these macros in the Northwind sample database in the Sales Totals by Amount macro object.)

Table 13.9. Access Macros Control the Summing of a Field Over a Page

Macro Name	Action	Arguments	Values
Page Total	SetValue	Item	[PageTotal]
		Expression	[PageTotal]+[SaleAmount]
New Page	SetValue	Item	[PageTotal]
		Expression	0

The Page Total macro uses the SetValue action to set the value of the unbound PageTotal text box to the following:

[PageTotal]+[SaleAmount]

This expression takes the current value of the PageTotal field and then adds the value of the SaleAmount field. For this to work properly, you must associate this macro with the On Print event in the report's Detail section. By using the Detail section, the macro runs after each record, so you get a running sum of the records on the page.

After each page is done, you need to reset the PageTotal field to 0, and that's the job of the New Page macro. To make this work, you must associate this macro with the On Print event of the report's Page Header section.

Figure 13.17 shows a print preview of the report with the sum at the bottom of the page.

Figure 13.17. Using two simple macros, you can work around the Access reporting engine's inability to calculate totals over a page.