Introduction to macros
This article explains what macros are and how they can save you time by automating tasks that you perform often. It explores the fundamentals behind creating and using macros and also touches on improvements to macros in Microsoft Office Access 2007.
In this article
What is a macro?
A macro is a tool that allows you to automate tasks and add functionality to your forms, reports, and controls. For example, if you add a command button to a form, you associate the button's OnClick event to a macro, and the macro contains the commands that you want the button to perform each time it is clicked.
In Access, it is helpful to think of macros as a simplified programming language that you write by building a list of actions to perform. When you build a macro, you select each action from a drop-down list and then fill in the required information for each action. Macros enable you to add functionality to forms, reports, and controls without writing code in a Visual Basic for Applications (VBA) module. Macros provide a subset of the commands that are available in VBA, and most people find it easier to build a macro than to write VBA code.
For example, suppose that you want to start a report directly from one of your data entry forms. You can add a button to your form and then create a macro that starts the report. The macro can either be a standalone macro (a separate object in the database), which is then bound to the OnClick event of the button, or the macro can be embedded directly into the OnClick event of the button itself — a new feature in Office Access 2007. Either way, when you click the button, the macro runs and starts the report.
You create a macro by using the Macro Builder, which is shown in the following illustration.
To display the macro builder:
-
On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.
Tip: The Macro Builder has been redesigned in Access 2010 to make it even easier to create, modify, and share Access macros.
Understand macros
The term macro is often used to refer to standalone macro objects (that is, the objects that you see under Macros in the Navigation Pane), but in reality, one macro object can contain multiple macros. In that case, it is referred to as a macro group. A macro group is displayed in the Navigation Pane as a single macro object, but a macro group actually contains more than one macro. Of course, it is possible to create each macro in a separate macro object, but often it makes sense to group several related macros into a single macro object. The name in the Macro Name column identifies each macro.
A macro consists of individual macro actions. Most actions require one or more arguments. In addition, you can assign names to each macro in a macro group, and you can add conditions to control how each action is run. The following sections discuss each of these features in more detail.
Macro names
If your macro object contains only one macro, macro names are unnecessary. You can just refer to the macro by the name of the macro object. However, in the case of a macro group, you must assign a unique name to each macro. If the Macro Name column is not visible in the Macro Builder, click Macro Names 
in the Show/Hide group on the Design tab. More information about running macros in macro groups appears later in this article.
Arguments
An argument is a value that provides information to the action, such as what string to display in a message box, which control to operate on, and so on. Some arguments are required, and some others are optional. Arguments are visible in the Action Arguments pane at the bottom of the Macro Builder.
A new feature of the Office Access 2007 Macro Builder is the Arguments column, which allows you to view (but not edit) an action's arguments on the same line as the action. This makes it a little easier to read your macro because you no longer need to select each action to display its arguments. To display the Arguments column, click Arguments 
in the Show/Hide group on the Design tab.
Conditions
A condition specifies certain criteria that must be met before an action will be performed. You can use any expression that evaluates to True/False or Yes/No. The action will not be executed if the expression evaluates to False, No, or 0 (zero). If the expression evaluates to any other value, the action will be run.
You can have one condition control more than one action by typing an ellipsis (...) in the Condition column for each subsequent action that you want the condition to apply to. If the expression evaluates to False, No, or 0 (zero), none of the actions are performed. If the condition evaluates to any other value, all of the actions are performed.
To display the Conditions column in the Macro Builder, on the Design tab, in the Show/Hide group, click Conditions 
.
| Use this expression to carry out the action | If: |
| [City]="Paris" | Paris is the City value in the field on the form from which the macro was run. |
| DCount("[OrderID]", "Orders")>35 | There are more than 35 entries in the OrderID field of the Orders table. |
| DCount("*", "Order Details", "[OrderID]=Forms![Orders]![OrderID]")>3 | There are more than 3 entries in the Order Details table for which the OrderID field of the table matches the OrderID field on the Orders form. |
| [ShippedDate] Between #2-Feb-2006# And #2-Mar-2006# | The value of the ShippedDate field on the form from which the macro is run is no earlier than 2-Feb-2006 and no later than 2-Mar-2006. |
| Forms![Products]![UnitsInStock]<5 | The value of the UnitsInStock field on the Products form is less than 5. |
| IsNull([FirstName]) | The FirstName value on the form from which the macro is run is null (has no value). This expression is equivalent to [FirstName] Is Null. |
| [Country/Region]="UK" And Forms![SalesTotals]![TotalOrds]>100 | The value in the Country/region field on the form from which the macro is run is UK, and the value of the TotalOrds field on the SalesTotals form is greater than 100. |
| [Country/region] In ("France", "Italy", "Spain") And Len([PostalCode])<>5 | The value in the Country/region field on the form from which the macro is run is France, Italy, or Spain, and the postal code is not 5 characters long. |
| MsgBox("Confirm changes?",1)=1 | You click OK in a dialog box in which the MsgBox function displays "Confirm changes?". If you click Cancel in the dialog box, Access ignores the action. |
| [TempVars]![MsgBoxResult]=2 | The temporary variable that is used to store the result of a message box is compared to 2 (vbCancel=2). |
Tip: To cause Access to temporarily skip an action, enter False as a condition. Temporarily skipping an action can be helpful when you are troubleshooting a macro.
Macro actions
Actions are the basic building blocks of macros. Access provides a large number of actions from which to choose, enabling a wide variety of commands. For example, some of the more commonly used actions can open a report, find a record, display a message box, or apply a filter to a form or report.
New macro features in Office Access 2007
In earlier versions of Access, many commonly used functions could not be performed without writing VBA code. In Office Access 2007, new features and macro actions have been added to help eliminate the need for code. This makes it easier to add functionality to your database and helps make it more secure.
-
Embedded macros You now have the ability to embed macros in any of the events provided by a form, report, or control. An embedded macro is not visible in the Navigation Pane; it becomes part of the form, report, or control in which it was created. If you create a copy of a form, report, or control that contains embedded macros, the macros are also present in the copy.
-
Increased security When the Show All Actions
button is not highlighted in the Macro Builder, the only macro actions and RunCommand arguments that are available for use are those that do not require trusted status to run. A macro built with these actions will run even when the database is in disabled mode (when VBA is prevented from running). Databases that contain macro actions that are not on the trusted list —or databases that have VBA code — need to be explicitly granted trusted status. -
Error handling and debugging Office Access 2007 provides new macro actions, including OnError (similar to the "On Error" statement in VBA) and ClearMacroError, that allow you to perform specific actions when errors occur while your macro is running. In addition, the new SingleStep macro action allows you to enter single-step mode at any point in your macro, so that you can observe how your macro works one action at a time.
-
Temporary variables Three new macro actions (SetTempVar, RemoveTempVar, and RemoveAllTempVars) allow you to create and use temporary variables in your macros. You can use these in conditional expressions to control running macros, or to pass data to and from reports or forms, or for any other purpose that requires a temporary storage place for a value. These temporary variables are also accessible in VBA, so you can also use them to communicate data to and from VBA modules.
Create a macro
In Office Access 2007, a macro or macro group can be contained in a macro object (sometimes called a standalone macro), or a macro can be embedded into any event property of a form, report, or control. Embedded macros become part of the object or control in which they are embedded. Standalone macros are visible in the Navigation Pane, under Macros; embedded macros are not.
Macro Builder features
You use the Macro Builder to create and modify macros. To open the Macro Builder:
-
On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.
Access displays the Macro Builder.
You use the Macro Builder to build the list of actions that you want to carry out when the macro runs. When you first open the Macro Builder, the Action column, the Arguments column, and the Comment column are displayed.
Under Action Arguments, you enter and edit arguments for each macro action, if any are required. A description box that gives you a short description of each action or argument is displayed. Click an action or action argument to read its description in the box.
The following table shows the commands that are available on the Design tab of the Macro Builder.
| Group | Command | Description |
| Tools | Run | Performs the actions listed in the macro. |
| Single Step | Enables single-step mode. When you run the macro in this mode, each action is performed one at a time. After each action is complete, the Macro Single Step dialog box is displayed. Click Step in the dialog box to advance to the next action. Click Stop All Macros to stop this and any other running macros. Click Continue to exit single-step mode and to perform the remaining actions without stopping. | |
| Builder | When you enter an action argument that can contain an expression, this button is enabled. Click Builder to open the Expression Builder dialog box, which you can use to build the expression. | |
| Rows | Insert Rows | Inserts one or more blank action rows above the selected row or rows. |
| Delete Rows | Deletes the selected action row or rows. | |
| Show/Hide | Show All Actions | Displays more or fewer macro actions in the Action drop-down list.
|
| Macro Names | Shows or hides the Macro Name column. Macro names are required in macro groups to distinguish the individual macros from each other, but otherwise macro names are optional. For more information, see the section Create a macro group, later in this article. | |
| Conditions | Shows or hides the Condition column. You use this column to enter expressions that control when an action is performed. | |
| Arguments | Shows or hides the Arguments column. This column displays the arguments for each macro action and makes it easier to read through your macro. If the Arguments column is not displayed, you have to click each action and read the arguments under Action Arguments. You cannot enter arguments in the Arguments column. |
Create a standalone macro
-
On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.
-
The Macro Builder is displayed.
-
Add an action to the macro:
-
In the Macro Builder, click the first empty cell in the Action column.
-
Type the action that you want to use, or click the arrow to display the action list, and then select the action that you want to use.
-
Under Action Arguments, specify arguments for the action, if any are required. To see a short description of each argument, click in the argument box, and then read the description on the right side of the argument.
Tips-
For action arguments whose settings are a database object name, you can set the argument by dragging the object from the Navigation pane to the action's Object Name argument box.
-
You can also create an action by dragging a database object from the Navigation pane to an empty row in the Macro Builder. If you drag a table, query, form, report, or module to the Macro Builder, Access adds an action that opens the table, query, form, or report. If you drag a macro to the Macro Builder, Access adds an action that runs the macro.
-
-
Optionally, type a comment for the action.
-
-
To add more actions to the macro, move to another action row, and then repeat step 2. Access carries out the actions in the order in which you list them.
Create a macro group
If you want to group several related macros in one macro object, you can create a macro group.
-
On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.
-
The Macro Builder is displayed.
-
On the Design tab, in the Show/Hide group, click Macro Names
if it isn't already selected. The Macro Name column is displayed in the Macro Builder.Note: In macro groups, macro names are necessary to identify the individual macros. The macro name appears on the same line as the macro's first action. The macro name column is left blank for any subsequent actions in the macro. The macro ends at the next entry in the macro name column.
-
In the Macro Name column, type a name for the first macro in the macro group.
-
Add the actions that you want the first macro to carry out:
-
In the Action column, click the arrow to display the action list.
-
Click the action that you want to add.
-
Under Action Arguments, specify arguments for the action, if any are required. To see a short description of each argument, click in the argument box, and then read the description on the right side of the argument.
Tips-
For action arguments whose settings are a database object name, you can set the argument by dragging the object from the Navigation Pane to the action's Object Name argument box.
-
You can also create an action by dragging a database object from the Navigation pane to an empty row in the Macro Builder. If you drag a table, query, form, report, or module to the Macro Builder, Access adds an action that opens the table, query, form, or report. If you drag a macro to the Macro Builder, Access adds an action that runs the macro.
-
-
Optionally, type a comment for the action.
-
-
Move to the Macro Name column of the next empty row, and then type a name for the next macro in the macro group.
-
Add the actions that you want the macro to carry out.
-
Repeat steps 5 and 6 for each macro in the group.
Note:
-
If you run a macro group by double-clicking it in the Navigation Pane or by clicking Run
in the Tools group of the Design tab, Access runs the first macro only, stopping when it reaches the second macro name. -
When you save the macro group, the name that you specify is the name of the whole group of macros. This name is displayed under Macros in the Navigation Pane. To refer to an individual macro within a macro group, use this syntax:
macrogroupname.macroname
For example, Buttons.Products refers to the Products macro in the Buttons macro group. In a list of macros, such as the Macro Name argument list for the RunMacro action, Access displays the Products macro as Buttons.Products.
Create an embedded macro
Embedded macros differ from stand-alone macros in that embedded macros are stored in the event properties of forms, reports, or controls. They are not displayed as objects under Macros in the Navigation Pane. This can make your database easier to manage, because you don't have to keep track of separate macro objects that contain macros for a form or a report. Use the following procedure to create an embedded macro.
-
Open the form or report that will contain the macro in Design view or Layout view. To open a form or report, right-click it in the Navigation Pane, and then click Design View
or Layout View 
. -
If the property sheet is not already displayed, press F4 to display it.
-
Click the control or section that contains the event property in which you want to embed the macro.
To select the entire form or report, click Report in the drop-down list at the top of the property sheet.
-
In the property sheet, click the Event tab.
-
Click the event property in which you want to embed the macro, and then click
next to the box. -
In the Choose Builder dialog box, click Macro Builder, and then click OK.
-
In the Macro Builder, click in the first row of the Action column.
-
In the Action drop-down list, click the action you want.
-
Fill in any required arguments in the Action Arguments pane and then move to the next action row.
-
Repeat steps 8 and 9 until your macro is complete.
-
Click Save
, then click Close 
.
The macro will run each time that the event is triggered.
Note: Access allows you to build a macro group as an embedded macro. However, only the first macro in the group runs when the event is triggered. Subsequent macros are ignored unless they are called from within the embedded macro itself (for example, by the OnError action).
Example: Embedding a macro in the On No Data event of a report
When you run a report and its data source does not contain any records, the report displays an empty page — that is, a page that has no data. You may prefer to have a message box displayed instead, and that the report not be displayed at all. Using an embedded macro is the ideal solution for this situation.
-
Open the report in Design view or Layout view.
-
If the property sheet is not already displayed, press F4 to display it.
-
On the property sheet, click the Event tab.
-
Click On No Data.
-
Click
. -
In the Choose Builder dialog box, click Macro Builder, and then click OK.
-
Enter the actions and arguments from the following table.
| Action | Arguments |
| MsgBox | No Records Found., Yes, Information, No Data |
| CancelEvent | [no arguments] |
-
Note that the preceding table shows the arguments as they are displayed in the Arguments column. They are actually entered under Action Arguments, as detailed in the following table.
| Action Argument | Value |
| Message | No Records Found. |
| Beep | Yes |
| Type | Information |
| Title | No Data |
-
Click Close.
The Macro Builder closes, and the On No Data event displays [Embedded Macro].
-
Save and close the report.
The next time that you run the report and no records are found, the message box is displayed. When you click OK in the message box, the report cancels without displaying the empty page.
Edit a macro
-
To insert an action row Click the macro row above which you want to insert the new action, and then on the Design tab, in the Rows group, click Insert Rows
. -
To delete an action row Click the action row you want to delete, and then on the Design tab, in the Rows group, click Delete Rows
. -
To move an action row Select the action by clicking the row selector to the left of the action name. Drag the row selector to move the action to a new position.
Learn more about macro actions
While you are working in the Macro Builder, you can learn more about an action or argument by clicking it, and then reading the description in the box in the lower-right corner of the Macro Builder window. Also, each macro action has a Help article associated with it. To learn more about an action, click the action in the action list, and then press F1.
Run a macro
Standalone macros can be run in any of the following ways: directly (for example, from the Navigation Pane), in a macro group, from another macro, from a VBA module, or in response to an event that occurs on a form, report, or control. A macro embedded in a form, report, or control can be run by clicking Run on the Design tab while the macro is in Design view; otherwise, the macro will only run whenever its associated event is triggered.
Run a macro directly
To run a macro directly, do one of the following:
-
Navigate to the macro in the Navigation Pane, and then double-click the macro name.
-
On the Database Tools tab, in the Macro group, click Run Macro
, click the macro in the Macro Name list, and then click OK. -
If the macro is open in Design view, click Run
on the Design tab, in the Tools group. To open the macro in Design view, right-click it in the Navigation Pane, and then click Design View .
Run a macro that is in a macro group
To run a macro that is in a macro group, do one of the following:
-
On the Database Tools tab, in the Macro group, click Run Macro
, and then click the macro in the Macro Name list.Access includes an entry for each macro in each macro group, in the format macrogroupname.macroname.
-
Click OK.
-
Specify the macro either as an event property setting on a form or report or as the Macro Name argument of the RunMacro action. Refer to the macro by using this syntax:
macrogroupname.macroname
For example, the following event property setting runs a macro named Categories in a macro group named Form Switchboard Buttons:
Form Switchboard Buttons.Categories
-
Run a macro that is in a macro group from within a VBA procedure by using the RunMacro method of the DoCmd object, and by using the syntax shown earlier to refer to the macro.
Run a macro from another macro or from a VBA procedure
Add the RunMacro action to your macro or procedure.
-
To add the RunMacro action to a macro, click RunMacro in the action list in a blank action row, and then set the Macro Name argument to the name of the macro that you want to run.
-
To add the RunMacro action to a VBA procedure, add the RunMacro method of the DoCmd object to your procedure, and then specify the name of the macro that you want to run. For example, the following instance of the RunMacro method runs the macro named My Macro:
DoCmd.RunMacro "My Macro"
Run a macro in response to an event on a form, report, or control
Although you can now embed macros directly into event properties of forms, reports, and controls, you can also still create stand-alone macros and then bind them to events, which is how macros are used in earlier versions of Access.
-
After you build your stand-alone macro, open the form or report in Design view or Layout view.
-
Open the property sheet for the form or report, or for a section or control on the form or report.
-
Click the Event tab.
-
Click the event property for the event that you want to trigger the macro. For example, to start the macro on the Change event, click the On Change property.
-
In the drop-down list, click the name of the standalone macro.
-
Save the form or report.
No comments:
Post a Comment