Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 36 ยท Macros and VBA

Macros in Access

Automate Microsoft Access without code. Learn the Macro Builder, actions, conditions, submacros, embedded and standalone macros, AutoExec and data macros.

A macro is a saved list of actions that Access performs for you: open a form, show a message, run a query, export a report. You build macros by choosing actions from a list instead of writing code, which makes them the quickest way to automate a database. They also have a safety benefit: macros can run in a restricted mode that blocks anything dangerous, which is why they work even in databases that are not fully trusted (see security and trusted locations).

Macros come in two families. UI macros react to something the user does, such as clicking a button. Data macros are attached to a table and react to data changes, much like a database trigger. This lesson covers both and shows when you should switch to VBA instead.

The Macro Builder

Choose Create > Macro on the ribbon. The Macro Builder opens with an Action Catalog on the right and an empty design area. To add an action, pick it from the Add New Action drop-down or double-click it in the catalog. Each action shows its arguments below it, for example OpenForm has Form Name, View, Filter Name, Where Condition and Data Mode.

Commonly used actions:

Action What it does
OpenForm, OpenReport, OpenQuery Open an object, optionally filtered with a Where Condition
MessageBox Show a message with a title and an icon
SetValue / SetProperty Set a control value or a property such as Visible
GoToRecord, FindRecord Move through records
RunMenuCommand, SaveRecord Run a ribbon command or save the current record
ExportWithFormatting, OutputTo Export an object to a file
RunDataMacro Call a named data macro
CloseWindow, QuitAccess Close an object or Access

Press Run to test a macro, then save it with a clear name. A common convention is a prefix such as mcr (mcrOpenCustomerForm).

Conditions, Else and submacros

Macros can make decisions. Add an If block and type a condition as an expression. The condition can read controls with the bracket syntax, for example [Forms]![frmOrders]![Total] > 1000. Every If block can have Else If and Else parts:

TEXT
If [Forms]![frmOrders]![Total] > 1000 Then
    MessageBox  "Large order: needs manager approval"  Beep: Yes  Type: Warning
Else If IsNull([Forms]![frmOrders]![CustomerID]) Then
    MessageBox  "Choose a customer first"
Else
    SaveRecord
End If

A submacro groups actions under a name so that one macro object can hold many related routines. In the builder choose Submacro from the drop-down, give it a name and put actions inside. Other macros call it with RunMacro using the notation MacroName.SubmacroName. Use submacros to keep a form's handlers together rather than creating dozens of tiny macro objects.

Other useful flow tools are temporary variables (SetTempVar and the TempVars!name syntax) for passing values between macros and forms, and OnError, which decides what happens when an action fails: ignore it, go to a labelled place, or stop.

Embedded versus standalone macros

A macro created from a control's property sheet (the Event tab, then the ... button, then Macro Builder) is an embedded macro. It is stored inside the form or report. If you copy the form, the macro goes with it, and it cannot be called from anywhere else. A standalone macro is a separate object in the Navigation Pane that many controls can share.

Rule of thumb: embed behavior that belongs to one form, make a standalone macro when several objects need the same steps.

A standalone macro named AutoExec is special: Access runs it automatically when the database opens. Use it to open a start-up form, set up temporary variables or hide the Navigation Pane. Hold Shift while opening the file to skip it, which is the standard way to get into a database whose start-up routine is broken (an administrator can switch the Shift bypass off, see security).

flowchart LR
  A["User clicks button"] --> B["Event: On Click"]
  B --> C{"Embedded or<br/>standalone?"}
  C -->|embedded| D["Actions stored in the form"]
  C -->|standalone| E["Actions stored in a macro object"]
  D --> F["Actions run top to bottom"]
  E --> F

Both kinds run the same way; they differ in where they are stored.

Data macros

A data macro belongs to a table and runs when data changes, no matter which form, query or import caused the change. Open the table in Datasheet view and use the Table tab on the ribbon:

  • Before Change and Before Delete run before the change is written. You can validate data or raise an error to cancel the operation (RaiseError).
  • After Insert, After Update and After Delete run after the change. Typical uses: write a row to an audit log, update a running total, stamp a LastModified time.

Inside a data macro you work with the record via actions such as SetField, CreateRecord, EditRecord and LookUpRecord, and with the special [Old] values in an update. Named data macros can be called from any macro with RunDataMacro. Because data macros live in the table, they are the closest Access has to the triggers of server databases, and they keep working when you change the user interface.

The two stand-ins below show the same ideas in a language that runs on this site. The first is a SQL trigger: an audit log that fills itself on update, which is what an After Update data macro with a CreateRecord action does.

SQL Stand-in for an After Update data macro

Press Run (or Ctrl + Enter) to see the result here.

The second stand-in runs a tiny "macro interpreter": a macro is just an ordered list of actions with arguments, plus conditions, executed top to bottom.

JS A macro is an ordered list of actions

Press Run (or Ctrl + Enter) to see the console output here.

No output.

When to use VBA instead

Macros are great for opening objects, filtering and simple rules. Choose VBA when you need loops, variables, recordset processing, detailed error handling, calls to other Office programs or Windows, or code that several forms share through a module. You can convert any macro to VBA: right-click it and choose Convert Macros to Visual Basic in Design view, which is also a fine way to learn VBA.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which macro does Access run automatically when a database opens?

    A standalone macro named AutoExec runs at start-up. Holding Shift while opening the file skips it.
  2. 2. Where is an embedded macro stored?

    Embedded macros travel with their form or report and cannot be called from other objects.
  3. 3. A data macro that logs every price change should be attached to which event?

    After Update runs whenever a record is changed, from any form, query or import, so the log is always complete.
  4. 4. Which task is a good reason to switch from a macro to VBA?

    Loops, recordsets and detailed error handling are VBA territory; macros are best for simple actions.

Summary

  • A macro is a list of actions with arguments, built in the Macro Builder; use If, Else and submacros for logic.
  • Embedded macros belong to one form or report; standalone macros are shared objects; AutoExec runs at start-up.
  • Data macros attach to tables (Before Change, After Insert/Update/Delete) and behave like triggers.
  • Use VBA when you need loops, recordsets or rich error handling.

Next: VBA basics for Access