Creating Macros

Performing scripts

To perform and automate certain predefined actions in your Microsoft Access database (e.g. accessing menu commands etc.) without the need to learn a programming language, you create macros.

Macros in Microsoft Access performing common actions such as navigating through records, deleting records, creating a new record, opening a form, printing a report and much more. The macros for performing these actions can be activated through the simple act of clicking a button, or by attaching the macros to an event on a form (such as when you Tab out of a field).

In the final section of this Microsoft Access course, we will discover that macros are really a subset of a much broader programming language known as Visual Basic for Applications (or VBA). If you ever need to create truly customised and powerful macros, you would go into VBA and do the work from here. But for most people (mainly designers and other non-programmers), macros are normally enough to achieve almost all the essential tasks needed to be done in an Access database.

As a general rule of thumb, most people are happy to develop Access databases to the stage of macros and leave it as is. For large companies relying on Access to manage their information needs and have sophisticated requirements, it is likely you will need to know something about VBA.

What the professional programmers normally do for large companies is use macros for developing the prototype of an Access database. In this way, the programmer can show to a client how the database will look. When the client is happy with the solution, programmers will convert the macros to VBA and add extra "bells and whistles" to create the final masterpiece.

How do I create macros?

To select one or more predefined/automated scripts called macros, you will see a Macro window (sometimes called a macro sheet) like the one shown below:

This is the important window by which all the macros (sometimes termed actions) to be performed are specified. To see the complete list of (and choose) the macro you want performed, click in any cell of the Action column. This column is found in the top half of the Macro window (i.e. the one that looks like the datasheet view of a table).

Here are the essential steps to creating macros for your Access database:

  1. Click on a blank cell in the Action column. A small grey button with an arrow pointing down will appear in the right side of the cell.
  2. Click this grey button in the empty cell to display a list of available macro actions.
  3. Choose a macro action and then press the Enter key.
  4. Some macro actions such as RunCommand have additional macros. Carefully check through the sublist at the bottom half of the Macro window (called arguments) and choose the macro suitable for your needs.

    NOTE: The RunCommand in Microsoft Access versions 2000 and higher have a range of useful macros for navigating records. Earlier versions of Access tend to have these navigating records macros under the GoToRecord macro.

  5. If you need help on any macro action, press F1.
  6. To set arguments for a given action, click the macro action and set the arguments for the given action (i.e. type a value, select a setting or submacro from the list, or use the Build button that appears in the argument cells) in the lower help of the Macro window. If you need help here, press F1.
  7. If you want some macro actions to be run under certain conditions (using the if...then...else for instance), you set the conditions for those actions by typing expressions in the Condition column. The actions will only be performed if the expression for the condition is found to be true. To view this column (hidden to the left of the Action column), click the Conditions button on the toolbar.

Tell me more about the macros I have available

When you click into the cell of the Action column and press the grey box with an arrow pointing down, you are shown a list of available macros. Here is a basic overview of what some of the macros do:

Close

Close an object in your database. The object can be a table, query, form, report, macro or module. In the arguments, specify the name of the object you want to close and whether or not you want to save changes made to the data in this object.

CopyObject, DeleteObject

This is a general copy and delete macro command for tables, queries, forms, reports, macros and modules. You can copy any of these types of objects from the current databases you are working in or from another database. The arguments that appear in the bottom half of the Macro window lets you enter the parameters for this macro to work.

Echo

This displays text in the Status Bar. This is the alternative to the MsgBox macro command. In the MsgBox macro command, the user is required to click on a button inside a pop-up dialog box containing the message before continuing with your macro script. The Echo macro command will only display the message in the Status Bar but not interfere with the running of your macro script.

MsgBox

You can display a pop-up dialog box with a message using this command. The arguments allow you to specify the message you want to show to a user, choose a picture to display inside the dialog box, and a title name to appear in the header of the dialog box. If you want a message box to return a condition when a user presses a button inside the dialog box, type the following in the Conditions column (as well as MsgBox in the Action column):


MsgBox("message you want to type in here", a+b+c)=y
 

where a is an integer number between 0 and 5 inclusive. The number specifies the type of button to display:


0 OK button only

1 OK and Cancel buttons

2 Abort, Retry and Ignore buttons

3 Yes, No and Cancel buttons

4 Yes and No buttons

5 Retry and Cancel buttons
 

b is an integer for displaying the type of icon you want in the message box:


0 No icon is displayed

16 Critical message

32 Warning query

48 Warning message

64 Information message
 

c is an integer value for specifying which button will act as the default (i.e. allow the user to press the Return key to activate a button):


0 First button is the default

256 Second button is the default

512 Third button is the default

768 Fourth button is the default
 

and y is the integer value returned by the MsgBox function when the user has clicked on a button:


1 for the OK button

2 for the Cancel button

3 for the Abort button

4 for the Retry button

5 for the Ignore button

6 for the Yes button

7 for the No button
 

In the row directly below this MsgBox function, type in the cell of the Condition column three full stops (i.e....). This tells Microsoft Access that if the condition of the MsgBox in the row above is satisfied perform the macro in the Action column of the row containing these full stops. You can repeat these full stops in as many rows as you like as these extra rows refer to additional macros you want to perform when the condition is satisfied.

A slight variation of MsgBox() condition is the InputBox() condition. Use InputBox() in the Condition column if you want the user to enter some text. The value returned by this function is the text entered by the user. For example,


InputBox("What's the password?")="open sesame"
 

Again the words MsgBox should appear in the Action column when invoking the InputBox() function.

OpenForm, OpenModule, OpenQuery, OpenReport, OpenTable

These macro commands are designed to open specific objects within your database. For example, if you want to open a form named "Splash Screen", you would use OpenForm and type "Splash Screen" in one of the arguments. If you open a table using OpenTable, you may also want to specify whether to open the table in design, datasheet or print preview mode, and whether you want to allow your users to have the ability to add records, to edit records or to leave it as Read Only.

PrintOut

A macro command from printing an object already opened in the database to a printer. The arguments for this macro allows you to specify page numbers, print quality, number of copies and whether to collate the copies.

RepaintObject

This is like the Refresh Window command for FileMaker Pro users. It is designed to refresh (or repaint in the language of Microsoft Access) the interface for a particular object. This means automatically recalculating values in fields so users can see what is happening.

RunSQL

Allows you to run SQL programs from within macros. Useful for SQL programmers wanting to extend the power and functionality of the macros base.

SetWarnings

This one is used to prevent Microsoft Access from displaying its own messages (or warnings — on top of the ones you may already have customised in the macro for your users). For example, if you want to delete an object such as a table and don't want Microsoft Access prompting you with a message about deleting the table, use this macro command.

These are just a handful of ready-made macros you can use to create your own customised database.

I would like to rearrange or delete my actions. Can I do that too?

All the techniques for rearranging and deleting rows in table design view work the same way when designing actions for your macro. Just click the row selector and press the Del key to permanently remove the row, or click and drag the row selector to move the row to another position.

Can I create several independent macros within a macro?

Yes you can. In the toolbar, you may recall clicking the Condition button to help you see the Condition column. Next to this button is the Macro Name button. By clicking on this, you will open up the Macro Name column to the left of the Action column. From here, you can give a name for a group of macros you use. If you separate the groups of macros for each macro name with a blank row, each macro name will run as an independent macro and will not run into the next one.

This technique is useful if you want to simplify your macro work by bringing together related macros into one main macro.

For example, you may want to create a macro called "mRecordNavigation". When the Macro window appears, open the Macro Name column and type four names in this column with at least one blank row inbetween. The names will be for navigating between the records. These can be "FirstRecord", "NextRecord", "PreviousRecord" and "LastRecord". In the cell next to these macro names (in the Action column), choose GoToRecord from the macro list. In the arguments, specify how to navigate to a record. So if you are in the "FirstRecord" submacro section, choose the argument for GoToRecord that gets you to the first record. This will be "First" in the Record argument.

Now save the macro.

The submacros can then be called up by typing "mRecordNavigation.FirstRecord" in the Properties window for a control in your form you want to click on or to activate an event. More details about how to call these macros in a form are shown below.

How do I save my macro?

To save a macro:

  1. Click the Save button on the toolbar, or choose Save from the File menu. If you haven't saved your macro before, Microsoft Access prompts you for a name.
  2. Type a name for the macro and then click OK. Microsoft Access saves your macro design. Now you can close the Macro window if you have finished designing your macro.

What do I need to press to run my macro?

To run a macro that is already opened, click the Run button on the toolbar. Otherwise, choose Run Macro from the File menu, click the arrow in the Macro Name dialog box, select the macro you want to run from the list, and click OK.

Once the macro is defined and saved, you may then decide to insert a Command button control in your form and click the Properties button on the toolbar to set the kind of action you want the button to perform. By convention for buttons, most Access developers select the OnClick property of the control and choose the name of this macro.

If you want to activate the macro as soon as a user has modified the data inside a fields and tabs or clicks outside of the field to let Microsoft Access update the field information, try AfterUpdate property.

Whichever property you choose to attach a macro, you will always have access to the macro names though the grey pop-up box with an arrow pointing down which appears when you click into the property field box. Otherwise you must create a new macro by clicking on the Builder button next to it (i.e. the button with the three full stops) and choose Macro Builder.

NOTE: Code Builder in the Builder dialog box is for creating VBA scripts. Expression Builder is for creating mathematical expressions needed for creating a calculation field.

My macro is not working the way I want it. Why?

If your macro (consisting of multiple macros in multiple Action cells) is not performing the way you want it to:

  1. Select the name of your macro from the Window menu.
  2. Click the Single Step button on the toolbar.
  3. Run the macro so that it will perform one action at a time to help you pinpoint the problem. This is called single stepping through a macro. You will be shown the Macro Single Step dialog box to let you know the action that would run next and any conditions associated with this action (if it has one).
  4. Click the Step button in the Macro Single Step dialog box to execute the action displayed. To stop the entire macro from running, click the Halt button in the Macro Single Step dialog box. To turn off single-stepping and allow the macro to run to its completion, click the Continue button.

NOTE: Make sure single-stepping mode is turned off. Otherwise, all your macros will be executed one action at a time.

Can I get my macro to perform an event every time a form has opened or closed?

Yes, you can. You will need to open the property sheet for your form and attach your macro from there. The process is:

  1. Select the name of the form from the Database window.
  2. Click the Properties button on the toolbar.
  3. Go Under the Edit menu and choose Select Form. The property sheet is displayed for the form.
  4. Click the button with the arrow in the upper portion of the property sheet and select Event Properties from the list.
  5. Now click into the event property you want to attach the macro. To run the macro when you open or close a form, you attach the macro to the OnOpen or OnClose property, respectively. To run the macro when you move from one record to another on the form, you attach the macro to the OnCurrent property. To run the macro before or after editing a record, you attach the macro to the BeforeUpdate or AfterUpdate property for the form, respectively. To run the macro when you delete a record, you attach the macro to the OnDelete property. And to run the macro immediately before or after you have added a new record, you attach the macro to the BeforeInsert or AfterInsert property, respectively.