Web.QHMIT.com

Create a Macro in Microsoft Access 2013

Microsoft Access provides the ability for you to create macros. A macro refers to a set of actions that can be run automatically, and on demand. Macros are usually configured to run whenever a particular event occurs. You specify what that event is when you create the macro. You could make a macro run when a user presses a certain key, or you could make a macro run everytime your database is opened.

Macro Example

For example, if say 90% of your database usage is entering new customers into the Customers form, you could create a macro that automatically opens that form every time the database is opened, and jumps straight to a new record. Doing this will save you from having to find the form, double-click on it, then click again in order to jump to a new record, every time you open the database.

Here's how you can go about creating a macro:

  1. Ensuring that the CREATE tab is selected from the Ribbon, click Macro:
    MS Access 2013: Create a macro - step 1
  2. Use the dropdown menu to select your first action - choose OpenForm:
    MS Access 2013: Create a macro - step 2
  3. In the bottom pane, select the form name you wish to open - choose Customers Form (our only form):
    MS Access 2013: Create a macro - step 3
  4. Use the dropdown menu to select your second action - choose GoToRecord. In the Record field, choose the record you want to go to - choose New. What we're doing here is ensuring that the form doesn't open up a previous record - it goes straight to the end and has a blank record ready for you to enter a new record (this saves time and unnecessary clicks):
    MS Access 2013: Create a macro - step 4
  5. You're now ready to save your macro. Click on the Save icon and enter a name for your macro. Make sure you name this macro AutoExec. By naming it Autoexec, we are instructing Access to run this macro everytime we open the database.
    MS Access 2013: Create a macro - step 5

Test your Macro

Now that we've created our macro, let's test it. To do this we should close our database and open it again. When we open it, the Customers form automatically opens and is ready for a new record to be entered.

To close the database, click the File tab from the Ribbon, then click Close.

You can also run macros by opening the Macros tab and double clicking on the macro you want to run (or selecting it and clicking Run from the Ribbon, or right-clicking it and selecting Run from the contextual menu).

Triggering Macros

Our above example is a little bit different to most macros in that the name is what determines when it will run (it will run as soon as the database is opened). For other macros, you will need to specify when the macro will run. You don't specify this in the macro. You specify it elsewhere in the database - wherever it is that you want the macro to run.

For example, you might want to run a macro whenever a button on a form is clicked. To do this, you simply:

  1. Create the macro
  2. Add a button to the form and point it to the macro

Whenever you add a button to a form, you need to provide an action for that button. One of the possible options is to run a macro.

Another Macro

Let's add a button to our form so that users can run the Customer Orders query. When the button is clicked, it will run a macro. The macro (which we will create here) will simply run the Customer Orders query.

Create the Macro

First we'll create the macro.

  1. Create another macro, but this time select the OpenQuery action
  2. Select the Customer Orders query from the Query Name drop down menu. Ensure Datasheet is selected under the View option
  3. Your macro should look like this:
    MS Access 2013: Create a macro - step 6

Modify the Form

Now we'll add the button to the form.

  1. Open the Customers Form in Design view, ensuring that the DESIGN tab is open
  2. Click on the Button icon in the Ribbon:
    Screenshot of adding a button to a form - step 1
  3. In the Form Detail area, click and drag the cursor so that it forms a square or rectangle (i.e. shape of a button). The Command Button Wizard will pop up. Select Miscellaneous > Run Macro and click Next:
    Screenshot of adding a button to a form - step 2
  4. Select Open Customer Orders (the macro we want to run) and click Next
  5. When prompted to put text or a picture on your button, select Text, call it Open Customer Orders and click Next
  6. Give your button a name. Let's call it CustomeOrdersButton
  7. Click Finish

At this point my form looks like this:

Screenshot of button in Design view

The button color doesn't really match the rest of the form but that's easily changed. Simply open the Property Sheet and change modify whichever settings you like under the Format tab.

Test the Button

So that's how to add a macro to a form. Now the fun part - testing our creation.

Switch to Datasheet view and click on the button. A datasheet should open with the results of the Customer Orders query. If you're happy with the result save the form.

Next we're going to look at converting an Access database to Excel.

There's also a tutorial for Access 2016.