Creating Simple Macros

3 4

Macros are used to run database actions automatically, as an alternative to performing the actions manually or writing VBA code. Macros are limited in functionality comparedto VBA code, but they’re also easier to learn to write. Even if you’re an expert code writer, you’ll find that several special types of macro are still genuinely useful.

Most database users will find an AutoKeys macro useful, to provide a set of custom hot keys. Some databases can use an AutoExec macro, because even though you can specify a database’s startup form and many other database behaviors in the Startup dialog box, you might need to perform some special tasks when a database is opened. For example, you might want to create a Word or an Excel object for the database to work with, create a lookup table filled with dates calculated from the current data, or set the value of a global variable.

A third special macro type (which doesn’t have a name) is a macro that runs a series of functions and/or action queries to automate the import and cleanup of data from an external data source. These three types of special macros are described in the section "Creating Special-Purpose Macros."

To create a simple macro, follow these steps:

1. In the Database window, click the Macros tab, and then click the New button to open the Macro Designer window.
2. Add actions to the macro by selecting them from the drop-down list in the Action column, as shown in Figure 14-1. When you select an action, its arguments appear in the lower portion of the Macro Designer. Type the values for arguments in the appropriate boxes (or, for some properties, select them from a drop-down list).

   

   Figure 14-1. Use the Action column to select an action to add to a new macro.

   tip

   ---

   To move back and forth between a macro row and its arguments, press the F6 hot key.

3. (Optional) In the Comments column, type a description of what the macro does.
4. Close the Macro Designer window, and type the macro name in the Save As dialog box. The standard naming convention tag for macros is mcr. See Chapter 4, "Creating a Database," for more details on using a naming convention.

note

---

Unlike other database objects, macros have only one view: Design view.

By default, each macro has only two columns: Action and Comment. These two columns are all you need to create a simple macro. However, two other columns—Macro Names and Conditions—can be made visible by clicking the appropriate buttons on the Macro Design toolbar, shown in Figure 14-2.

Figure 14-2. The Macro Names and Conditions buttons appear on the Macro Design toolbar.

The Macro Names column is used to enter macro names, which are used when you create a macro group (as opposed to a single macro). (See the sidebar "Macros and Macro Groups," for more details on macro groups.) The Conditions column is used to enter conditions that must be met before a macro action is run. For example, you could enter a condition specifying that a certain form must be open or that a field must have a certain value. Figure 14-3 shows a portion of the Suppliers macro group from the Northwind sample database, with macro names and a condition for a MsgBox action that displays the message box only if the SupplierID field is Null.

Figure 14-3. A macro group uses macro names to identify individual macros.

A macro condition is the equivalent of a simple If…Then construct in code. To use more complex logical structures, such as Select Case statements or nested If…Then structures, you need to write VBA code. If you need to deal with just one of two alternatives (say, a form that is open or closed), however, a macro condition can handle this situation very well.

See Chapter 20, "Customizing Your Database Using VBA Code," for more details on writing VBA code. See the section "Creating Macro Conditions," for more details on creating macro conditions.

Setting the Default Columns for the Macro Designer

If you want the Macro Names and Conditions columns to be visible by default in all new macros, select the appropriate check box on the View tab in the Options dialog box shown in Figure 14-4 (choose Tools, Options to open this dialog box). This step will save you time if you often create macro groups or macros with conditions.

Figure 14-4. You can specify that the Conditions column should be visible by default on the View tab of the Options dialog box.

Macros and Macro Groups

A macro group is not a separate type of database object; it is simply a set of macros, each identified by name in the Macro Names column. Macro groups are created in theMacro Designer, just as single macros are. The only difference is that in a macro group, a group of macro actions is identified by name as belonging together. In earlier versions of Access, a macro group with a set of macros designed to run from various events of a form was often used to automate form events. This use of macro groups has for the most part been superseded by VBA event procedures stored in a form’s class module.

The AutoKeys macro, described in the section "Using the AutoKeys Macro Group," is an example of a macro group.

Commonly Used Macro Actions

Some of the most frequently used macro actions are listed here, with a description of their usage:

• OpenForm Opens a form, with the following arguments:
  • Form Name The form’s name, as it appears in the Database window.
  • View A view in which the form will open.
  • Filter Name The name of a saved filter or query used to filter form data.
  • Where Condition A SQL WHERE clause used to filter form data.
  • Data Mode A selection of options for data entry.
  • Window Mode How the form window opens (Normal, Hidden, Minimized, and so on).

• OpenReport Opens a report, with the following arguments:
  • Report Name The report’s name, as it appears in the Database window.
  • View A view in which the report will open.
  • Filter Nam The name of a saved filter or query used to filter report data.
  • Where Condition A SQL WHERE clause used to filter report data.

• OpenQuery Opens a select or crosstab query or runs an action query, with the following arguments:
  • Query Name The query’s name, as it appears in the Database window.
  • View A view in which the query will open, usually Datasheet.
  • Data Mode Data entry mode for a select query opened in Datasheet view.

• Close Closes the Access window or the active database object window, with the following arguments:
  • Object Type The type of database object you want to close.
  • Object Name The name of the database object to close.
  • Save A flag specifying whether to save the object when closing it. (Possible values are Yes, No, or Prompt.)

• MsgBox Displays a message box with information or a warning, with the following arguments:
  • Message The text for the message box (up to 255 characters).
  • Beep A flag specifying whether to sound a beep when the message box is opened.
  • Type The type of message box, with a specific icon. (Possible values are None, Critical, Warning?, Warning! or Information.)
  • Title The text displayed in the message box’s title bar; if left blank, Microsoft Access is displayed.

• SetValue Sets the value of a field, control, or property on a form or report, with the following arguments:
  • Item The name of the field, control, or property whose value is being set.
  • Expression The expression used to set the value. You can click the Build button to the right of the argument to open the Expression Builder to help construct an expression.

• GoToRecord Goes to a specific record in an open table, form, or query, with the following arguments:
  • Object Type The type of database object that contains the record you want to go to.
  • Object Name The name of the database object that contains the record you want to go to.
  • Record The record you want to go to; that record will be made the current record. (Possible values are Previous, Next, First, Last Go To, or New.)
  • Offset The number of the record to go to (as shown in the Record Number box at the bottom of a form window).

tip

---

To see a Help topic for a macro action and its arguments, position the insertion point in the Any Argument box in the lower portion of the Macro Designer window, and press F1.