Amal Nagm

 

 

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.

 

You use the Macro Builder to create and modify macros. To open the 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.
	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.

 

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

 

If you want to group several related macros in one macro object, you can create a 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.

 

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.

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.

 

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.

Use conditions to control macro actions

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

Examples of macro conditions

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

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.
   The Macro Builder is displayed.
2. 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.
     
   • Under Action Arguments (in the lower part of the Macro Builder), specify arguments for the action, if any are required.
     
      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.
     Tips
     • For an action argument (action argument: Additional information required by some macro actions. For example, the object affected by the action or special conditions under which the action is carried out.) whose setting is a database object (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) 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 in the Comment column.
3. To add more actions to the macro, move to another action row, and then repeat step 2.

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.
   The Macro Builder is displayed.
2. 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 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.
3. In the Macro Name column, type a name for the first macro in the macro group.
4. 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.
     Tips
     • 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.
5. Move to the next empty row, and then type a name for the next macro in the Macro Name column.
6. Add the actions that you want the macro to carry out.
7. Repeat steps 5 and 6 for each macro in the macro group.

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

Create an embedded macro

1. In the Navigation Pane, right-click the form or report that will contain the macro, and then click Design View or Layout View .
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 .
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.

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 .
• To delete an action row   Right-click the action row that you want to delete, and then click Delete Rows .
• 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.

Use conditions to control macro actions

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

Examples of macro conditions