Macros

Return to Tutorial homepage

Building and using macros

What are macros?

Macros are one of the exciting things that make things happen in Access. Conceptually, macros are just a sequence of actions that are grouped together so they can all be executed together as a unit. For example, there may be a set of actions you want to happen when you click on some button (see below) on a form. You might want a button click to result in some form being opened and the contents of the form updated. These two actions (opening the form, setting the value of a control on the form) can be grouped together in a single macro. The button is then set so that the macro executes when the button is clicked. When the macro executes, both activities (in sequence) execute. The types of activities that can be done from within a macro are almost limitless: opening and closing forms, updating controls, executing queries, etc.

Defining a macro

1. From the database window, select the ‘Macro’ tab.
2. Click on ‘New’ to create a new macro
3. In the ‘Action’ column you will put in, one below the other, the sequence of actions you wish to be invoked when this macro is executed. For example, let’s say you wish to create a macro that will enable you to add a new record on a form, but will first place the cursor at a field you specify (two actions).
4. In the action column, pick ‘GoToRecord’ as the action you want.
   
   You will notice that at the bottom of the screen there are various fields you can fill in to tell Access more about what kind of record you want the system to go to. In the ‘Record’ field, select ‘New’. The macro will now cause Access to go to a new record in the currently active Form/Table
   
5. Put the cursor on the next line of the ‘Action’ column. This time, select ‘GoToControl’. Then, in the field at the bottom of your screen, type in the name of the control on which you wish the cursor to be placed.
6. Save the macro and give it a name
7. Exit the macro

Getting system events to kick off a macro

There are a number of ways to invoke a macro. Look in the property list of any control. Under ‘Event Properties’ you will see things like: ‘On Enter’ ‘On Exit’ ‘On Click’ ‘On Dbl Click’, etc.

These are all events that the system recognizes. In response to such an event, you can specify that you want a particular macro to execute.

1. Creating a button that will execute a macro when clicked. Create a button as described above. When the wizard asks you to select a category, select ‘Miscellaneous.’ In response to ‘When Button is Pressed:’ select ‘Run Macro.’ And so on...
   
2. Alternatively, you may specify the macro in one Event Property fields of any existing control. For example, if you have an existing button on your form, in the ‘On Click’ property of that form, you may select the macro from the combo box associated with that Event Property field.
   

Other uses of the Macro

The setvalue action

The setvalue action is used to assign a value to a control on, among other things, a form.  The value can take the form of a constant, a computed value, or the value of some other control.  In the current example, we would like to create a macro that will open the CD form, and for a new record put an initial value of "Jazz" in the combo box that enables a user to pick the CD category.

To use the setvalue action, begin with the Macro in design view.

You are now prompted for two arguments:  Item and Expression.   Item refers to the control that is to receive the new value.  Expression is the new value or, more precisely, an expression that resolves to some value.  

Specifying an Item

The Item must be specified using the full naming syntax which, in general looks something like this:   Forms![form name]![control name]  While you can type this in, it is in many cases more convenient to use something called the Expression Builder to create it.  To use the Expression Builder, click on the Elipsis (looks like three dots:   ...) on the right-hand side of the Item argument field. You will see the following:

What you will do is click your way forward until you find the control you want, and the "paste" it into the expression field where you see the cursor in the above window.  In the current example,

1. double-click on Forms
2. double-click on All Forms.  Double-clicking on All Forms gives you a list of all forms in your database.  Double-clicking on Loaded Forms gives you only those forms currently open.  The latter can be more useful if you have a large database and are working on only a few forms at a time.
3. single-click on the CD form.  At this point you will see all of the controls on that form in the middle window.  
4. single-click on the cmbCategory control.  This is the name I have given to the combo box on the CD form that lists categories.

In the right-hand column, you now see all of the properties of the control you selected in the middle column.   For right now, we are, however, only interested in the value of the combo box.   We will choose as our "expression" the value of the cmbCategory combo box by now clicking on the Paste button.   You will now see the full name of the combo box appear in the expression window.

When you click 'OK', this expression gets placed into the Item argument field in the Macro design view.

For the expression, we wish to enter a constant ("Jazz").  The rule is that we should enclose constants in quotation marks.  If we leave out the quotation marks, then Access will interpret our entry as the name of some other control and cause an error.

Now, save the macro.  

Return to top

Previous Section: Buttons and Switchboards