Home > Sample chapters > Microsoft Office > Access

Automating a Client Application Using Macros in Microsoft Access 2010

Working with Submacros

You’ll find that most of the forms you design for an application require multiple macros to respond to events—some to edit fields, some to open reports, and still others to respond to command buttons. You could design a separate macro saved with its own unique name in the Database window to respond to each event, but you’ll soon have hundreds of macros in your application.

You can create a simpler set of more manageable objects by defining submacros within named macro objects. A submacro is a named collection of macro actions inside a macro object. One approach is to create one saved macro object per form or report. Another technique is to categorize macros by type of action—for example, one macro containing all the OpenForm actions and another containing all the OpenReport actions.

Let’s take a look at a form that depends on submacros. Figure 20-8 shows the PrintOptions form from the Wedding List Macro database in Form view. This form contains two command buttons, Print and Cancel, each of which triggers a different submacro. The two submacros are contained within a macro object called DoReport.

Figure 20-8

Figure 20-8 The two command buttons on the Print Options form run submacros.

To look at the macro object, right-click the DoReport macro in the list of macro objects in the Navigation pane, and then click Design view on the shortcut menu to open this macro object in the Logic Designer. Figure 20-9 shows the macro.

Figure 20-9

Figure 20-9 The DoReport macro group includes nine individual submacros.

To create a submacro within a macro object, you use the Submacro program flow construct in the Action Catalog. (You can also find the Submacro construct in the drop-down list of actions in the Add New Action combo box on the macro design surface. Access displays the four user interface macro program flow constructs—Comment, Group, If, and Submacro—first in the drop-down list.) You can create a series of actions at the beginning of the macro definition—not inside a submacro block—that you can reference from an event property or a RunMacro action by using only the name of the macro object. As you saw earlier in the AutoexecXmpl macro, naming a macro object in a RunMacro action (without any qualifier) asks Access to run the unnamed actions it finds in that macro object.

To create a set of named submacros within a macro object, you can drag a Submacro construct from the Action Catalog to the macro design surface or select Submacro from the Add New Action combo box. You then need to provide a name for your submacro. Note that Access always places submacro blocks below macro actions on the macro design surface. You cannot place macro actions that are outside a submacro block beneath any submacros. To execute a named submacro within a macro object from an event property or a RunMacro action, enter the name of the macro object, a period, and then the name of the submacro. For example, to execute the PrintIt submacro set of actions in the DoReport macro, enter DoReport.PrintIt in the event property or the Macro Name parameter.

In the sample DoReport macro, there are nine submacros within the object. (You must scroll down to see the other submacros.) The first submacro, Options (triggered by the Print Report button on the WeddingList form), opens the Print Options form, and the second submacro, PrintIt, determines which report was selected. The next four submacros (Groups, Alpha, Accepted, and PrintF) display the appropriate report in Print Preview mode, based on the result of the second submacro. The Cancel submacro merely closes the Print Options form if the user clicks the Cancel button. The NoRecords submacro cancels opening a report when the report’s record source has no data, and the ErrReport submacro handles errors. As you might have guessed, Access runs a submacro starting with the first action in the submacro block name specified and executes each action in sequence until it encounters a StopMacro action, another submacro, or no further actions. As you’ll see later, you can control whether some actions execute by adding conditional tests in the macro. Note that you can click Collapse All in the Collapse/Expand group on the Design tab to collapse all the actions quickly and see the submacro names.

If you open the PrintOptions form in Design view (see Figure 20-10) and look at the properties for each of the command buttons, you’ll see that the On Click property contains the name of the submacro that executes when the user clicks the command button. If you open the list for any event property, you can see that Access lists all macro objects and the named submacros within them to make it easy to select the one you want.

Figure 20-10

Figure 20-10 You can see that Access lists all macro objects and named submacros in the various event properties.

Remember, the macro name is divided into two parts. The part before the period is the name of the macro object, and the part after the period is the name of a specific submacro within the object. So, for the first command button control, the On Click property is set to DoReport.PrintIt. When the user clicks this button, Access runs the PrintIt submacro in the DoReport macro object. After you specify a macro name in an event property, you can click the Build button next to the property, and Access opens that macro in the Logic Designer.