Using Macros in Microsoft Access: Automation Without Writing Code

Macros are how you automate an Access database without writing code. A button that opens the right form for the record you are looking at, a check that refuses to save an order with no customer, a report that emails itself — all of that is a macro, built from dropdown lists rather than typed.

This guide covers what macros can genuinely do, how to build the ones people actually need, and the point at which you should stop and use VBA instead.

The two kinds, and why the difference matters

Access has had a confusing history here, and it is worth being clear because the two behave differently.

• Embedded macros live inside a specific control or form event — the On Click of one button. They travel with the object, and they are the default in modern Access. Start here.
• Standalone macros appear as named objects in the navigation pane and can be called from several places. Use them only when the same action genuinely runs from more than one button.

There is also a third kind you may inherit: the AutoExec macro, which runs automatically when the database opens. If a database does something odd on startup, look there first. (Holding Shift while opening the file skips it — useful when the startup macro is what is broken.)

Building your first useful one

The most common request is a button on a customer form that opens that customer's orders. Here is the whole thing:

1. Open the form in Design View and add a button. When the wizard offers to help, cancel it — you will learn more doing it directly.
2. With the button selected, open the Property Sheet (F4), go to the Event tab, and click the ... beside On Click.
3. Choose Macro Builder.
4. In the Add New Action box, choose OpenForm.
5. Set Form Name to your orders form, and in Where Condition type:
   [CustomerID]=[Forms]![frmCustomers]![CustomerID]
6. Save and close. Switch to Form View and press the button.

That Where Condition line is the part worth understanding, because nearly every useful macro contains one. The part before the equals sign is a field in the form being opened; the part after is where to get the value from. Read it as "show me the orders whose CustomerID matches the customer I am looking at".

The actions worth knowing

The list of available actions is long and most of it you will never use. These are the ones that carry real work:

• OpenForm / OpenReport — with a Where Condition, as above.
• MessageBox — tell the user something. Keep the text specific: "This order has no customer" beats "Error".
• SetValue / SetProperty — fill in a field, or hide, show, enable or disable a control depending on the record.
• RunMenuCommand with SaveRecord — commit the current record before doing anything that depends on it being saved.
• ApplyFilter — narrow what a form is showing.
• GoToControl — put the cursor where the user needs it after a failed check.
• CancelEvent — stop what was about to happen. This is what makes validation macros work.
• EMailDatabaseObject — send a report as an attachment.
• OutputTo — save a report or query as PDF or Excel.

Two you should avoid: RunSQL, which shows confirmation prompts and can fail without telling you, and SendKeys, which fakes keystrokes and breaks the moment anything about the screen changes.

Stopping a bad record from saving

This is the macro that earns its keep, and it goes on the form's Before Update event rather than on a button — that way it catches every route to saving, not just the one that goes through your button.

Add an If block with the condition:

IsNull([CustomerID])

Inside it, three actions in order: MessageBox ("Choose a customer before saving this order."), CancelEvent, then GoToControl pointing at the customer field.

The order matters. CancelEvent stops the save; GoToControl afterwards puts the user where the problem is. Without the last step people are told something is wrong and left to hunt for it.

Trusted locations, or nothing runs

Macros in a database opened from an untrusted location are disabled, and Access is quiet about it — a thin yellow bar appears and the buttons simply do nothing. Every "my macros stopped working" report is worth checking here first.

Fix it once, properly: File → Options → Trust Center → Trust Center Settings → Trusted Locations, and add the folder the database lives in (tick the subfolders option for a network share). Do this on each machine that uses it, and do it for the folder rather than teaching people to click "Enable Content" every time — that habit is exactly what real malware relies on.

Data macros: rules the tables enforce themselves

A less-known feature, and often the right tool. Under Table → Create Data Macros you can attach logic to the table itself — after insert, after update, before delete — so it runs no matter how the data arrives.

Typical uses: stamping a "last changed" date, writing a row into an audit log whenever a price changes, or blocking a delete that would orphan related records.

The advantage over a form macro is that nothing can bypass it. The disadvantage is that it is invisible — someone reading the forms will not know it exists. Write down that it is there.

Debugging, when nothing seems to happen

• Single Step (in the Macro Design ribbon) runs the macro one action at a time, showing you each one and whether its condition was met. It is the first thing to reach for.
• Check the trust settings before assuming the macro is wrong.
• Check the record is saved. A macro that reads a field the user has typed into but not committed sees the old value. Add SaveRecord first.
• Check your control names. A Where Condition pointing at a renamed control fails silently. Name controls deliberately — txtCustomerName, not Text42.

When to stop and write code instead

Macros cover a lot, but they have a hard ceiling. Move to VBA when you need to:

• Loop through records one at a time. Macros cannot.
• Handle errors properly. Macros stop on failure; VBA can catch it and tell the user something useful.
• Talk to Excel, Word or Outlook beyond emailing an attachment.
• Reuse logic across many forms without copying it.
• Do anything with real complexity. A macro with fifteen nested conditions is harder to maintain than the ten lines of code it replaces.

The reverse is also true, and worth saying: if a macro does the job, use the macro. Code written because it felt more professional is code someone has to maintain for no benefit.

Where automation stops helping

Macros make an Access database pleasant to use. They do not move the format's limits: 2GB per file, around ten concurrent users, and no browser or mobile access since Access Web Apps were discontinued in 2018.

If people cannot reach the database from outside the office, or it slows to a crawl when several of them are in it, automation is not the answer — that is a platform limit, and the usual answer is rebuilding as a web application. What you have automated is not lost, though. Each macro is a business rule written down, and those rules carry across intact.

Our Microsoft Access services page covers both sides of that, and a free system review will tell you which one you need.