This article explains what macros are in Access databases 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 Access.
What is a macro?
A macro in Access 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 opens 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. Either way, when you click the button, the macro runs and opens the report. These types of macros are generally referred to as user interface macros.
Access 2010 was the first version to introduce the concept of data macros. A data macro allows you to automate tasks and add functionality directly to your tables. Data macros and their actions are attached to specific table events such as when you add a new record to a table.
You create a macro by using the Macro Builder, an example of which is shown in the following illustration.
Note: Beginning in Access 2010, the Macro Builder has been redesigned to make it even easier to create, modify, and share Access macros. If you're using Access 2007, the Macro Builder is a set of rows and columns where you define the macro actions and conditions.
To display the macro builder for user interface macros:
On the Create tab, in the Macros & Code group, click Macro.
To display the macro builder for data macros:
Open any table in Datasheet view. On the Table tab in the Before Events and After Events groups, click one of the event commands such as Before Change or After Insert.
To learn more about the Macro Builder and how to build and test user interface and data macros, see the related articles in the See Also section at the bottom of this article.
Macro features in Access
In earlier versions of Access, many commonly used functions could not be performed without writing VBA code. In current versions of Access, 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 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 Access provides 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 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 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.
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.