Home > Sample chapters > Microsoft Office > Access

Automating a Client Application Using Macros in Microsoft Access 2010

The Macro Design Facility—An Overview

In Chapter 7, you learned that Microsoft redesigned the macro design window in Access 2010 for Access developers to be more productive when creating macros in their applications. Although functional, the older macro design window was not the most intuitive interface to work with. For example, when you wanted to create a new action for a macro, you selected the action in the upper part of the window, but you entered the arguments in the bottom part of the window. The Arguments column in the upper part of the window was read-only, so you constantly needed to switch from the top window to the bottom window. You also could not create complex nested logic conditions for your macro execution and the readability of the macro elements was difficult to follow.

The following sections explain how to work with the macro design facility in Access 2010 in the context of user interface macros.

Working with the Logic Designer

Open the Wedding List Macro sample database (WeddingMC.accdb) from the folder where you installed the sample files. (The default location is \Microsoft Press\Access 2010 Inside Out on your C drive.) As you’ll discover later in this chapter, a special macro called Autoexec runs each time you open the database. This macro determines whether the database is trusted, opens an informational form for a few seconds, and then tells you which macro to run to start the application. We’ll look at that macro in some detail later.

On the Create tab, in the Macros & Code group, click Macro. Access opens the new Logic Designer for creating macros, as shown in Figure 20-1.

Figure 20-1

Figure 20-1 This is the new Logic Designer where you create data and user interface macros.

Whenever you need to create or edit data macros or user interface macros in Access 2010, this is the design surface that you use. When you’re working with user interface macros, Access enables the commands in the Tools group and displays the macro actions available for user interface macros in the Action Catalog. You’ll also notice that Access did not collapse the Navigation pane when you clicked the Macro command on the ribbon, as it does when you are working with data macros. When you are working with user interface macro objects—macros displayed in the Navigation pane—Access does not open the Logic Designer window modally, which means you can open other database objects while working on your macro. (You’ll learn later in this chapter that Access opens the Logic Designer modally when you’re creating embedded macros.)

As you can see in Figure 20-1, the new Logic Designer layout looks more like a Visual Basic code window. The Expand Actions, Collapse Actions, Expand All, and Collapse All buttons in the Collapse/Expand group selectively expand or collapse the actions listed in the macro designer surface. In the Show/Hide group on the Design tab, you can choose to hide the Action Catalog, shown on the right side of the Logic Designer window, by clicking the Action Catalog toggle button. When you’re working with macro objects, Access does not display the Close group on the Design tab as it does for data macros. When you want to save your macro changes, you can click the Save button on the Quick Access Toolbar or press Ctrl+S. Note that if you attempt to close the Logic Designer window with unsaved changes, Access prompts you and asks if you want to save your changes before closing the window.

On the right side of the Logic Designer window is the Action Catalog. The Action Catalog shows a contextual list of the program flow constructs and macro actions that are applicable for user interface macros. If you are working with a client macro, Access displays macro actions available for client macros. Similarly, when you’re working with a web macro, Access displays macros actions available for web macros. (We’ll discuss the Action Catalog in more detail in the next section, and we’ll discuss web macros in Chapter 21.)

In the middle of the Logic Designer window is the main macro designer surface, where you define your macro. You add program flow constructs, macro actions, and arguments to the designer surface to instruct Access what actions to take for the macro. If you have more actions than can fit on the screen, Access provides a scroll bar on the right side of the macro design surface so you can scroll down to see the rest of your actions.

In the lower-right corner of the Logic Designer window is the Help window. Access displays a brief help message in this window, which changes based on where the focus is located in the Action Catalog. (Remember: You can always press F1 to open a context-sensitive Help topic.)

To get you accustomed to using the Logic Designer for macros, let’s create a simple macro that displays a message. You can use the MessageBox action to open a pop-up modal dialog box with a message in it. This is a great way to display a warning or an informative message in your database without defining a separate form. To begin, let’s add a Comment block to the macro design surface. As you learned in Chapter 7, you’ll find the Comment block especially useful for documenting large macros that contain many actions. Click on the word Comments under the Program Flow node in the Action Catalog, hold the mouse key down, drag the comment onto the macro design surface, and then release the mouse button, as shown in Figure 20-2. Note that in Figure 20-2, we collapsed the Navigation pane so you can see more of the macro design surface.

Figure 20-2

Figure 20-2 Drag the Comment program flow element from the Action Catalog onto the macro design surface.

Assume that this message will be a greeting, so click inside the Comment block and type Greeting message. Click outside the Comment block onto the macro design surface and Access collapses the size of the Comment block to just fit the text you typed and displays the text in green. As you learned in Chapter 7, the /* and */ symbols mark the beginning and end of a block of comments. Access designates anything written between those symbols as a comment and they are there only to provide information about the purpose of the data macro or particular action to follow.

Click in the Add New Action combo box on the macro design surface now and drop-down the list of macro actions. In the Add New Action combo box, you can specify any of the 84 client macro actions and four program flow constructs provided by Access 2010. Select MessageBox from this drop-down list. After you select an action such as MessageBox, Access displays argument boxes for the specific action you choose, as shown in Figure 20-3, in which you enter the arguments for the action.

Figure 20-3

Figure 20-3 Enter arguments for a MessageBox action to display a greeting message.

You use the Message argument box to set the message that you want Access to display in the dialog box you’re creating. The setting in the Beep argument box tells Access whether to sound a beep when it displays the message. In the Type argument box, you can choose a graphic indicator, such as a red critical icon, that will appear with your message in the dialog box. In the Title argument box, you can type the contents of your dialog box’s title bar. Use the settings shown in Figure 20-3 in your macro.

Saving Your Macro

You must save a macro before you can run it. Click the Save button on the Quick Access Toolbar, or click the File tab on the Backstage view and then click Save. When you do so, Access opens the dialog box shown in Figure 20-4. Enter the name TestGreeting, and click OK to save your macro.

Figure 20-4

Figure 20-4 Enter a name for this test macro in the Save As dialog box.

Testing Your Macro

You can run some macros (such as the simple one you just created) directly from the Navigation pane or from the Macro window because they don’t depend on controls on an open form or report. If your macro does depend on a form or a report, you must link the macro to the appropriate event and run it that way. (You’ll learn how to do this later in this chapter.) However you run your macro, Access provides a way to test it by allowing you to single-step through the macro actions.

To activate single-stepping, right-click the macro you want to test in the Navigation pane and then click Design View on the shortcut menu. This opens the macro in the Logic Designer. Click the Single Step button in the Tools group on the Design tab. Now, when you run your macro, Access opens the Macro Single Step dialog box before executing each action in your macro. In this dialog box, you’ll see the macro name, the action, and the action arguments.

Try this procedure with the TestGreeting macro you just created. Open the Logic Designer, click the Single Step button, and then click the Run button in the Tools group on the Design tab. The Macro Single Step dialog box opens, as shown in Figure 20-5. Later in this section, you’ll learn how to code a condition in a macro. The Macro Single Step dialog box also shows you the result of testing your condition.

Figure 20-5

Figure 20-5 The Macro Single Step dialog box allows you to test each action in your macro.

If you click the Step button in the dialog box, the action you see in the dialog box will run, and you’ll see the dialog box opened by your MessageBox action with the message you created, as shown in Figure 20-6. Click the OK button in the message box to dismiss it. If your macro had more than one action defined, you would have returned to the Macro Single Step dialog box, which would have shown you the next action. In this case, your macro has only one action, so Access returns you to the Logic Designer.

Figure 20-6

Figure 20-6 Access displays the dialog box you created by using the MessageBox action in the TestGreeting macro.

If Access encounters an error in any macro during the normal execution of your application, Access first displays a dialog box explaining the error it found. You then see an Action Failed dialog box, which is similar to the Macro Single Step dialog box, containing information about the action that caused the problem. At this point, you can click only the Stop All Macros button. You can then edit your macro to fix the problem. We’ll discuss handling errors in “Trapping Errors in Macros,” on page 1219.

Before you read on in this chapter, you might want to return to the Logic Designer and click Single Step again so that it’s no longer selected. Otherwise, you’ll continue to single-step through every macro you run until you exit and restart Access or click Continue in one of the Single Step dialog boxes.