Create a macro

You can create a macro (macro: An action or set of actions that you can use to automate tasks.) to perform a specific series of actions (action: The basic building block of a macro; a self-contained instruction that can be combined with other actions to automate tasks. This is sometimes called a command in other macro languages.), and you can create a macro group (macro group: A collection of related macros that are stored together under a single macro name. The collection is often referred to simply as a macro.) to perform related series of actions.

In Microsoft Office Access 2007, macros can be contained in macro objects (sometimes called standalone macros), or they can be embedded into the event properties of forms, reports, or controls. Embedded macros become part of the object or control in which they are embedded. Macro objects are visible in the Navigation Pane, under Macros; embedded macros are not.

What do you want to do?


Learn about the Macro Builder

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. Button image

The Macro Builder is displayed.

Macro Builder

In the Macro Builder window, you 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 on the left side for each macro action, if any are required. A description box that gives you a short description of each action or argument is displayed on the right side. Click an action or action argument to read the description in the box.

You can use the commands on the Design tab of the Macro Builder to create, test, and run a macro.

The following table describes the commands that are available on the Design tab.

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 (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.), 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.

  • To display a longer list of macro actions, click Show All Actions. When the longer list of macro actions is available, the Show All Actions button appears selected. If you select a macro action from this longer list of macro actions, then you may need to grant the database explicit trust status before you can run the action.
  • To switch from a longer list of macro actions to a shorter list that displays only those macro actions that can be used in a database that has not been trusted, make sure that the Show All Actions button is not selected.

 Tip   If the Show All Actions button is selected, click the Show All Actions button to clear the selection. When the Show All Actions button is not selected, the shorter list of trusted macro actions is available.

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.
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.

 Tip    Try Office 2010 The Macro Builder has been redesigned in Access 2010 to make it even easier to create, modify, and share Access Macros. Watch a video or try Office 2010!

Top of Page Top of Page

Create a standalone macro

  1. 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. Button image

The Macro Builder is displayed.

  1. 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 list of available actions, and then select the action that you want to use.

Select a macro action

  • Under Action Arguments (in the lower part of the Macro Builder), specify arguments for the action, if any are required.

Enter action arguments

 Notes 

  • As you type arguments in the Action Arguments pane, they are displayed in the Arguments column in the action list. However, the Arguments column is for display only; you cannot enter arguments in that column.
  • To see a short description of each argument, in the Action Arguments pane, click in the argument box, and then read the description in the adjacent box.

ShowTips


  • Optionally, type a comment for the action in the Comment column.
  1. To add more actions to the macro, move to another action row, and then repeat step 2.

When you run the macro, Access carries out the actions in the order in which you list them.

Top of Page Top of Page

Create a macro group

If you want to group several related macros in one macro object, you can create a macro group.

  1. 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. Button image

The Macro Builder is displayed.

  1. On the Design tab, in the Show/Hide group, click Macro Names Button image 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 distinguish the individual macros from each other. 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 when the next macro name is encountered.

  1. In the Macro Name column, type a name for the first macro in the macro group.
  2. 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 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.

ShowTips

  • For an action argument whose setting is 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.
  1. Move to the next empty row, and then type a name for the next macro in the Macro Name column.
  2. Add the actions that you want the macro to carry out.
  3. Repeat steps 5 and 6 for each macro in the macro group.

The following illustration shows a small macro group. The macro group contains two macros, the names of which are displayed in the Macro Name column. Each macro contains two actions.

Macro group example

 Notes 

  • When you save the macro group, the name that you specify is the name of the group of macros. In the preceding example, the name of the macro group is Macro3. This name is displayed under Macros in the Navigation Pane. To refer to an individual macro in a macro group, use this syntax:

macrogroupname.macroname

For example, in the preceding illustration, Macro3.FoundMsg refers to the second macro in the macro group.

  • If you run a macro group either by double-clicking it in the Navigation Pane or by clicking Run Button image in the Tools group on the Design tab, Access executes only the first macro in the group, stopping when it reaches the second macro name.

Top of Page Top of Page

Create an embedded macro

Embedded macros differ from standalone macros in that they 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 containing macros for a form or report. Also, embedded macros are included with the form or report whenever you copy, import, or export it.

For example, if you want to prevent a report from displaying when there is no data, you can embed a macro into the report's On No Data event property. You might use the MsgBox action to display a message, and then use the CancelEvent action to cancel the report instead of displaying a blank page.

  1. In the Navigation Pane, right-click the form or report that will contain the macro, and then click Design View Button image or Layout View Button image.
  2. If the property sheet is not already displayed, press F4 to display it.
  3. Click the control or section that contains the event property in which you want to embed the macro. You can also select the control or section (or the entire form or report) by using the drop-down list under Selection Type at the top of the property sheet.
  4. On the property sheet, click the Event tab.
  5. Click the event property in which you want to embed the macro, and then click Button image.
  6. In the Choose Builder dialog box, click Macro Builder, and then click OK.
  7. In the Macro Builder, click in the first row of the Action column.
  8. In the Action drop-down list, click the action that you want.
  9. Enter any required arguments under Action Arguments.
  10. If you want to add another action, click in the next row of the Action column and repeat steps 8 and 9.
  11. When your macro is complete, click Save, and then click Close.

The macro runs each time that the event property 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 in the group are ignored.

Top of Page Top of Page

Edit a macro

  • To insert an action row     Right-click the action row above which you want to insert the new action row, and then click Insert Rows Button image.
  • To delete an action row     Right-click the action row that you want to delete, and then click Delete Rows Button image.
  • To move an action row     Select the action row by clicking the row header to the left of the action, and then drag it to a new position.

You can insert, delete, or move multiple rows by first selecting the group of rows, and then performing the operation you want. To select a group of rows, click the row header for the first row you want to select, hold down the SHIFT key, and then click the row header for the last row you want to select. (The row header is the shaded box to the left of each action row.)

An alternative method of selecting multiple rows is to position the pointer over the row header of the first row you want to select, and then click and drag up or down to select the other rows.

 Note   When selecting rows by clicking and dragging, the first row you select must not already be selected. If it is already selected, Access will assume you are trying to drag the row to a new location.

Top of Page Top of Page

Use conditions to control macro actions

You can use any expression that evaluates to True/False or Yes/No in a condition (condition: Part of the criteria that a field must meet for searching or filtering. Some conditions must be used with a value; for example, the field Author with the condition equals with the value Jane.). The macro action will be executed if the condition evaluates to True (or Yes).

To enter a condition for a macro action, you must first display the Condition column in the Macro Builder:

  • On the Design tab, in the Show/Hide group, click Conditions Button image.

Type an expression in the Condition column. Do not precede the expression with an equal sign (=). To make a condition apply to several actions at once, type ... in each subsequent row. For example:

Condition that applies to several macro actions

 Tip   To cause Access to temporarily ignore an action, enter False as a condition. Temporarily ignoring an action can be helpful when you are trying to find problems in a macro.

Examples of macro 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 three 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-2007# And #2-Mar-2007# The value of the ShippedDate field on the form from which the macro is run is no earlier than 2-Feb-2007 and no later than 2-Mar-2007.
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 (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) (has no value). This expression is equivalent to [FirstName] Is Null.
[Country]="UK" And Forms![SalesTotals]![TotalOrds]>100 The value in the Country 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] In ("France", "Italy", "Spain") And Len([PostalCode])<>5 The value in the Country field on the form from which the macro is run is France, Italy, or Spain, and the postal code is not 5 characters in length.
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]![MyVar]=43 The value of the temporary variable MyVar (created by using the SetTempVar macro action) equals 43.
[MacroError]<>0 The value of the MacroError object's Number property is not equal to 0, meaning an error has occurred in the macro. This condition can be used in conjunction with the ClearMacroError and OnError macro actions to control what happens when an error occurs.

For more information about expressions, see the article Create an expression.

Top of Page Top of Page

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 reading its description in the box in the lower-right corner of the Macro Builder window. Also, each macro action has a Help topic associated with it. To learn more about an action, choose the action from the action list, and then press F1.

Top of Page Top of Page

 
 
Applies to:
Access 2007