Tricks of the Microsoft Office Gurus
| 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:
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:
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.
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])
Table 13.5 lists the values you can use for the Buttons parameter.
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.
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).
Following is a summary of the five actions performed by this macro:
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.
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.
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.)
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.
|