The following sections explain how to work with the macro design facility in Access 2010.
In Access 2010, Microsoft has completely redesigned the design surface for creating macros. In Figure 7-1, you can see an example of the macro design window from Access 2007. In the upper part of the older macro window, you defined your macro, and in the lower part, you entered settings, called arguments, for the actions that you selected for your macro. The upper part of the macro window showed five columns—Macro Name, Condition, Action, Arguments, and Comment.
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.
In Access 2010, Microsoft redesigned the macro design window so that Access developers could be more productive when creating macros in their applications. The Access development team at Microsoft studied user interactions with the older macro design window and came up with five main goals in their redesign:
Increase productivity of application developers by adding features such as the Action Catalog, IntelliSense for writing expressions, support for copy and paste, and keyboard accessibility improvements.
Support more complex logic execution with macros by allowing nested
If/Else/Else Ifconstructs.Increase the readability of macros by having the macro design surface display the program flow more like Visual Basic code.
Create macros faster by reusing existing macros in other parts of the database.
Support the ability to share macros easily with other users and other applications through the use of Extensible Markup Language (XML).
Open the Back Office Software System backup copy sample web database (BOSSDataCopy.accdb) from the folder where you installed the sample files. As you’ll learn in Chapter 20, a special user interface macro called Autoexec runs each time you open the database. This user interface macro determines whether the database is trusted, opens an informational form for a few seconds, and then closes the informational form so you can see the Navigation pane.
To create data macros, you first need to open a local client table in Design or Datasheet view, or a web table in Datasheet view. To display all the tables in your BOSSDataCopy.accdb web database, click the bar at the top of the Navigation pane, click Object Type under Navigate To Category and then click Tables under Filter By Group. Double-click the table called tblCompanyInformation to open it in Datasheet view and then click the Table contextual ribbon tab under Table Tools to see the data macro events, as shown in Figure 7-2.
If you are working in the client database, you can add and edit data macros from Design view as well. When you have a local client table open in Design View, Access displays a group called Field, Record, & Table Events on the Design contextual ribbon tab. Click the Create Data Macros button on the Design tab and Access displays a list of the data macro events. In Figure 7-3, you can see an example of a table in the Housing Reservations client database opened in Design view and the table events listed, after clicking Create Data Macros.
Figure 7-3. Data macro events are listed under the Create Data Macros button on the Design contextual ribbon tab in table Design view for client databases.
You can attach data macros to the Before Change, Before Delete, After Insert, After Update, and After Delete events of tables. In Figure 7-2, with the Back Office Software System sample web database, you can see that Access highlighted the Before Change and Before Delete buttons on the Table contextual ribbon tab. When you create and save a data macro for a table event, Access highlights that event button in the ribbon as a visual cue for you to show that a data macro already exists for that event.
To create a new data macro for a table event or edit an existing one, you click the corresponding event button in the ribbon. Let’s explore the existing data macro that we defined for the Before Change event in the tblCompanyInformation table to show you the new Logic Designer for creating macros. Click the Before Change button on the Table contextual ribbon tab, and Access opens the new Logic Designer, as shown in Figure 7-4.
Whenever you need to create or edit data macros or user interface macros in Access 2010, this is the design surface that you use. Access selectively enables or disables the buttons on the Design contextual tab under Macro Tools based on whether you are working with data macros or user interface macros. (In Chapter 20 and Chapter 21, you’ll learn how to work with the Logic Designer to create and edit user interface macros.) 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, which means that you cannot open any other database objects until you close the designer window.
As you can see in Figure 7-4, 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. In the Close group, you can click Save to save any changes to your data macro. Click Close to close the Logic Designer window for now. Note that if you attempt to close the Logic Designer window with unsaved changes, Access 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, data blocks, and data actions that are applicable to the data macro event you are currently viewing. (When you create user interface macros, the Action Catalog similarly displays actions that you can use for user interface macros.) We’ll discuss the Action Catalog in more detail in the next section.
In the middle of the Logic Designer window is the main macro designer surface where you define your data macro. You add program flow constructs, macro actions, and arguments to the designer surface to instruct Access what actions to take for the data 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. You’ll notice in Figure 7-4 that Access displays any arguments directly beneath the action. Access displays a combo box called Add New Action at the bottom of the macro designer surface. This combo box displays a list of all the actions you can use for the type of data macro you are creating.
In the bottom right corner of the Logic Designer window is the Help window. Access displays a brief help message in this window depending upon where the focus is located in the Action Catalog. (Remember, you can always press F1 to open a context-sensitive Help topic.)
Click the Close button in the Close group on the Design contextual tab to return to the Datasheet view of the tblCompanyInformation web table, and then close the table.