Using Embedded Macros

Access 2007 includes a new 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.

Editing an Embedded Macro

To edit an embedded macro, you must first open a form or report in Design 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 command button labeled Print These Instructions, or select cmdPrint from the selection list on the property sheet to view the properties for this command button, as shown in Figure 18–12.

Figure 18–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 a macro design window, as shown in Figure 18–13. Notice that in the macro title bar 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.

Figure 18–13: The macro design window shows the embedded macro we created to respond to the Click event of the cmdPrint button on the fdlgNotTrusted form.

With the macro design window 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 RunCom-mand action. In the Command argument, we selected the Print 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 trusted. We provide this print button so that you can print the instructions for creating a trusted location displayed on the form.

Close the macro design window for this embedded macro, and then click the Close 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 macro design window shown in Figure 18–14. This embedded macro uses the Close action to tell Access to close the fdlgNotTrusted form when the user clicks this command button.

Figure 18–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 macro groups in an embedded macro, Access executes only the actions defined in the first group when the event to which this macro responds occurs. To execute the additional named macro groups, you must create a call within the first set of actions to tell Access to execute the other groups-as the DoReport macro object demonstrated earlier in this chapter.

Inside Out-Embedded Macros Stay with Their Controls 

If you attach embedded macros to a specific control on a form or report, Access saves the macro with the control. If you cut or copy this control to the Clipboard and then paste it back on the form or report, Access keeps the embedded macro attached to the control.

Creating an Embedded Macro

Close the macro design window 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 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 18–15.

Figure 18–15: Select Macro Builder in the Choose Builder dialog box to create an embedded macro.

If you select the Macro Builder option, Access opens a new macro design 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 the next two chapters-Chapter 19 and Chapter 20, “Automating Your Application with Visual Basic.”) Select the Macro Builder option, and then click OK to begin creating a new embedded macro.

To display a message box, select MsgBox in the Action column. 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 18–16.

Figure 18–16: The MsgBox action displays a message box in Access.

Inside Out-Enabling the Choose Builder Dialog Box 

In Chapter 19, you’ll learn that you can select [Event Procedure] from the list in an event property and then click the Build button to open the Visual Basic Editor to create the appropriate procedure. In the initial release of Access 2007, Microsoft failed to also provide an [Embedded Macro] option that you could use in the same way to create a macro to respond to the event. You also cannot type [Embedded Macro] in the property and click the Build button. You must leave the property blank, click the Build button, and choose Macro Builder in the Choose Builder dialog box.

But there’s a catch. To see the Choose Builder dialog box, you must not select the Always Use Event Procedures check box in the Forms/Reports section in the Object Designers category of the Access Options dialog box. (The option is cleared by default) If you select that check box, Access always opens the Visual Basic Editor window when you click the Build button in any event property. The only way to create a new embedded macro is to select Macro Builder in the Choose Builder dialog box. If you intend to use embedded macros, you must leave the Always Use Event Procedures check box cleared.

Click Save on the Quick Access Toolbar to save this new embedded macro, and then close the macro design 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 the Save button again on the Quick Access Toolbar 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 18–17. Click OK in the message box, and Access then displays the not trusted form. Click the Close button to close the form.

Figure 18–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 Save on the Quick Access Toolbar to save your changes, and then close the form.

Caution 

Access does not warn you that it deletes the macro associated with an event property when you clear the property setting. You also cannot undo clearing the property to get the macro back. If you delete a complex macro that was previously saved in the form design, click the Microsoft Office Button, and click Save As to save the form with a new name. (Or close the form without saving if you’re willing to discard other changes.) You can then open the original form in Design view to recover the macro. Remember that when you copy and paste a control from one form to another, Access also pastes any attached embedded macros, so you can copy the control and its macro from the old form to the new one to get the macro back in the new form.

Inside Out-Embedded Macros Won’t Work with Earlier Access Versions 

If you create a database in the .mdb file format, Access 2007 allows you to create embedded macros for forms, reports, and controls just like you can in an .accdb file format database. But if you open the .mdb database with an earlier version of Access-2000, 2002, or 2003-the embedded macros do not function. In fact, you cannot see any [Embedded Macro] entries for event properties when you open an .mdb database with an earlier Access version. If you create an .mdb format database using Access 2007 that will be opened and run with a previous version of Access, do not create embedded macros for your application.