M a c r o s  

CPT 106 · Franklin College · Erich Prisner · 2002-2006

Remember the four categories of objects in Access you should know already:

Access allows so-called macros, which are small programs. Macros are objects of a fifth category.

Creating Macros

To create a macro you click on "macros" in the database window. Other than for the other objects, there is no wizard to create them, you just click on "new" and a design window appears, showing (initially) two columns, one for the name of the action, and one for your comment. You should make it a habit to write comments--- you will be surprised how strange and and unintelligible your own macros look to you after 3 months.

your browser cannot display this hint on column width
your browser cannot display this hint on how to run macros

A macro consists of a sequence of basic actions. Each action is written in a different row. There are 56 of these actions to choose from, and you see the list of possibilities when clicking in the name column and clicking on the arrow for the dropdown menu. Explanation for each action is shown in the lower right corner. Some actions have arguments, see the lower left part.

For our first macro, try the following sequence of actions:

  1. MsgBox: Write some message and give the box also a title.
  2. Beep
  3. OpenTable: Let's open the Employees table, and let's open it in Database view as read only.
  4. GoToRecord: Choose the Employees table, select "Go To" in the "Record" and "5" in the "offset" Field.
  5. Beep: You can also choose by typing "B" into the field.
  6. MsgBox: another one, try to experiment with the type here.
  7. Maximize: the active window, which should be the Employees table.
  8. GoToRecord: next in the Employees table.
  9. MsgBox: a third message.
  10. FindRecord: Lever to match the start of the field, search all and not only the current field.

your browser cannot display this hint on single steps

Save it as macro "mcr1", and test it by clicking on the exclamation mark button . Next open the macro again and include several empty lines by right clicking at the very left of any action row and choosing "Insert Rows". Empty lines are ignored and may be used for structuring your macros. You can also rearrange the action rows by double clicking again on the very left, and then grabbing and moving the row, but be careful here---it changes what the macro does.

Macro Groups

your browser cannot display this hint on dummies

If you have many macros, or if macros with some inner connection, or if you use macros as subprograms, it makes sense to use macro groups. Create a new macro. Click on the Names button and a new column for the names will appear. Here you write the names of the macros in your group. It's best to separate them by empty rows.

If you run the macro group, only the first macro in it is executed. 

your browser cannot display this hint on stopping macros
your browser cannot display this hint on avoiding flicker


A fourth column in macros or macro groups is possible. It is generated using the conditions icon in the design view. ...............

No "else" is possible here. If you want to repeat the condition of the previous row, just type an ellipsis "..." into the conditions column. Use "NOT" to negate the condition.

For our second macro, we want to create something that goes through the customers table and gives some comment if the country is Switzerland. We create a macro group called "helloswiss", containing an (empty) dummy at the top, the main macro, called "main", a (sub)macro called "loop", and finally a (sub)macro called "greeting". The "main" macro should contain the following actions:





If you want to run a certain maction a fixed times, just type in the number of repetitions as argument into the "Run Macro" action.

Main Menu

In the Database Window, click on "Forms" and then double click on "Create Form in Design View". Right click on the form and click on "Toolbox" to make it visible. Click on the button symbol there and click where you want to place it on your form. Choose "Miscellaneous" and "Run Macro" as the action that should be performed when clicking the button. Click "Next" and choose the macro. Choose text and name for the button. Repeat this for other buttons.

After that, you can start your macros by starting the form and clicking the buttons there. But how do we conveniently start this form? Well ...


You can name one macro "AutoExec". This macro will be executed when the database is loaded and opened. 


Erich Prisner, 11/22/2003