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.
Understand macros
The term macro is often used to refer to standalone macro objects (that is, the objects that you see underMacros 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 theShow/Hide group on the Design tab. More information about running macros in macro groups appears later in this article.
in theShow/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 Argumentscolumn, click Arguments 
in the Show/Hide group on the Design tab.
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, clickConditions 
.
.
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).
|
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, andRemoveAllTempVars) 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.