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.
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.
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.
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.
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.
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, “Understanding Visual Basic Fundamentals,” 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.
Inside Out: Enabling the Choose Builder Dialog Box
In Chapter 24, 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. Unfortunately, there is no corresponding [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 Build. You must leave the property blank, click Build, 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 Form/Report Design View 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. When you’re working with client forms and client reports, 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 option cleared.
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.
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.
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 File tab on the Backstage view, and click Save Object 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 Versions of Access
If you create a database in the .mdb file format, Access 2010 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 2010 that will be opened and run with a previous version of Access, do not create embedded macros for your application.