Automating a Client Application Using Macros in Microsoft Access 2010

Using Embedded Macros

Access 2010 includes a feature to create embedded macros in the event procedures for forms, reports, and controls. The macros you have been creating and opening thus far in this chapter are macro objects that you can access from the Navigation pane. You save embedded macros, however, within the event procedures for forms and reports. You cannot see or run these macros directly from the Navigation pane. You can think of embedded macros as similar to data macros in that data macros are not objects seen in the Navigation pane; they are attached to table events.

Editing an Embedded Macro

To edit an embedded macro, you must first open a form or report in Design or Layout view. The fdlgNotTrusted form in the Wedding List Macro database contains two embedded macros, each of which is attached to the Click event of one of the two command buttons. Select this form in the Navigation pane, and open it in Design view. Click the Property Sheet button in the Tools group on the Design tab to open the form’s property sheet. Next, click the Print These Instructions command button or select cmdPrint from the selection list on the property sheet to view the properties for this command button, as shown in Figure 20-12.

Figure 20-12 The property sheet lists any embedded macros attached to events.

Notice that [Embedded Macro] appears in the On Click property—this indicates that a macro is stored with the form design that responds to this event. To view and edit the macro attached to this event property, click the Build button on the right side of this property line. Access opens the Logic Designer, as shown in Figure 20-13. Notice that in the tab at the top of the macro design surface, Access displays the name of the form, the object name the embedded macro is attached to (in this case, the cmdPrint command button), and the specific event of the object that runs the embedded macro.

You’ll notice Access automatically collapsed the Navigation pane to show you more of the macro designer surface. Access also opens the Logic Designer window modally when you are working with embedded macros, which means you cannot open any other database objects until you close the designer window.

Figure 20-13 The Logic Designer shows the embedded macro that we created to respond to the Click event of the cmdPrint button on the fdlgNotTrusted form.

With the Logic Designer open, you can now view and edit the macro conditions (not used in this sample macro), actions, arguments, and comments. For the cmdPrint command button, you can see we attached a simple macro that executes the RunMenuCommand action. In the Command argument, we selected the PrintObject command, which tells Access to print the object that has the focus—in this case, the fdlgNotTrusted form. The application displays this form only if the database is not trusted. We provide this print button so that you can print the instructions for creating a trusted location displayed on the form.

Close the Logic Designer for this embedded macro by clicking the Close button in the Close group on the Design tab, and then click the Close command button on the form (or select cmdCancel from the selection list). You’ll see [Embedded Macro] displayed in the On Click property for this command button. Click the Build button for this property to open the Logic Designer shown in Figure 20-14. This embedded macro uses the CloseWindow action to tell Access to close the fdlgNotTrusted form when the user clicks this command button.

Figure 20-14 The Close button on the fdlgNotTrusted form executes an embedded macro to close the form.

We set the Save argument of the Close action to Prompt, which instructs Access to ask the user whether any changes to the form design should be saved on closing. (The form opens in Form view, so the user shouldn’t be able to make any changes.) We selected this setting because choosing any other option causes the Close action to be not trusted. We’ll discuss actions that are not trusted later in this chapter.

The two embedded macros you’ve seen on this form are simple macros with only one action each. You’re not limited to using only one action in an embedded macro. You can create a very complex macro, such as the DoReport macro you saw previously in this chapter, with several macro actions using several conditions. However, there is one important difference when designing a complex embedded macro. If you create named submacros in an embedded macro, Access executes only the actions defined in the first submacro when the event to which this macro responds occurs. To execute the additional named submacros inside the embedded macro, you must create a call within the first set of actions to tell Access to execute the other submacros—as the DoReport macro object demonstrated earlier in this chapter.

Creating an Embedded Macro

Close the Logic Designer if you still have it open, and let’s create a new embedded macro to display a message box when this form opens. From the list under Selection Type near the top of the property sheet, select Form to display all the properties of the form. Click the Event tab, and then click the On Open property. To create a new embedded macro, click the Build button at the right end of the property. Access opens the Choose Builder dialog box, as shown in Figure 20-15.

Figure 20-15 Select Macro Builder in the Choose Builder dialog box to create an embedded macro.

If you select the Macro Builder option, Access opens the Logic Designer window, where you can create your embedded macro. If you select Expression Builder, Access opens the Expression Builder dialog box, where you can build an expression to enter in the property. If you select Code Builder, Access opens the Visual Basic Editor, where you can write a Visual Basic code procedure for this event property. (We’ll discuss Visual Basic in Chapter 24 and Chapter 25, “Automating Your Application with Visual Basic,” on the companion CD.) Select the Macro Builder option, and then click OK to begin creating a new embedded macro.

To display a message box, select MessageBox in the Add New Action combo box. In the Message argument, enter the following text:

This database is not trusted, so it cannot execute all the code needed to automate this application. Please read and follow the instructions displayed in the form that opens after you close this message in order to have the application function properly.

In the Beep argument, leave the default setting Yes, and change the Type argument to Warning! to provide a visual cue that something is wrong and call attention to the message. In the Title argument, enter Embedded Macro Test. Your finished macro should look something like Figure 20-16.

Figure 20-16 The MessageBox action displays a message box in Access.

Click the Save button on the Quick Access Toolbar to save this new embedded macro, and then close the Logic Designer window. You’ll notice that Access now displays [Embedded Macro] on the On Open property line. Note that if you don’t click Save before closing the macro design window, Access prompts you to save the changes and update the property.

If you click No, Access does not save the embedded macro. Click Save again to save the changes to the form itself. Switch to Form view (or close the form and then open it in Form view from the Navigation pane), and notice that Access now displays a message box, as shown in Figure 20-17. Click OK in the message box, and Access then displays the not trusted form. Click Close to close the form.

Figure 20-17 Your embedded macro now displays a message box before the form opens.

Deleting an Embedded Macro

If you need to delete a saved macro object, you can easily delete it in the Navigation pane. For embedded macros, however, you need to delete the contents in the specific property. Open the fdlgNotTrusted form again in Design view, and then open the property sheet for the form. To delete the message box embedded macro you just created, find the On Open property on the Event tab, highlight [Embedded Macro], and then press Delete to delete the embedded macro. Click the Save button on the Quick Access Toolbar to save your changes, and then close the form.