Getting to Know Microsoft Access, Part 9: Macros

The Event Model

In Access, an event is something that happens to an object, which in turn triggers a response of some sort. Usually events are caused by the user but can also be triggered by the program. For example, when Access loads you could make a form open automatically. This can happen because a form opening is an event.

Events are organized into eight different groups, each corresponding to a different type of effect. For example, some events work on data, others on keyboard actions, and so forth. Each event corresponds to a property of an object. For example, several controls have the "On Change" event property set. The eight different types of groups are: data events, focus events, error/timer events, keyboard events, filter events, mouse events, print events, and window events.

You can set event properties by opening the property sheet for an object and clicking into the Event tab. There you can specify what events you want to apply to the object. The following list shows the options available to you:

1. You can set the event property to a macro group.
   
2. You can set the event property to an existing macro.
   
3. You can set the event property to an existing VBA procedure.
   
4. You can set the event property to an existing user-defined function [enter an = sign followed by the function name and then empty parentheses () to so ]
   
5. Click Build… to create a new macro, VBA procedure (using the Code Builder), or function (using the Expression Builder).

Macros

A macro is essentially a list of actions that you apply to objects to respond to events. Each action carries out one task. You create your actions in the order you want them to execute. In addition, you specify the arguments of the actions, giving the program additional information as needed.

Finally, you can set conditions for each action in a macro to determine whether it runs or not. Run a macro by applying it to the event property of an object. Once the specified event occurs the macro will run by running the all the specified actions. Actions that have conditions applied to them may or may not run depending on whether or not they passed the conditional tests.

Once you've created your macros you'll see them listed in the Macros tab in the Database window. This way you can attach any macro to any event property in your database.

Creating Your First Macro

As in any programming endeavor it is helpful to first design your macro before you build it. To do this, list the actions you want to occur, and in what order you want them to occur. Also describe the event you want the macro to apply to. Check the Access help feature to determine if the macro you are building is right for the event property you are planning to apply it to. Determine in advance which arguments each action will have and whether it will be conditional or not. What are the conditions? What are the arguments? You should sort out all these details before you start building your macro. Use pseudocode if you know it.

To build your first macro, click "New" in the Macros tab of the Database window. Here's what the Create New Macro window looks like:

Select your first action from the Actions column in the Macro window. Remember to put in a comment so you remember what the action is for. When you select your first action the arguments pane opens up below. You can either type in the arguments you want, or select from what is available in the drop-down lists. Current arguments are displayed to the right of the argument pane.

If the argument requires an object name you can either type in the name or drag it from the Database window. Finally, you can set the argument to an expression that evaluates to the desired argument value. For instance, you could type in =[LastName] to set the argument value to the LastName control. The Expression Builder (click the Build… link) helps you build your expressions; not all arguments accept expressions.

Debugging

Once you've built your macro you need to debug it. You can do this in one of two ways: either you can run it and see what happens, or you can step through the macro one step at a time. Use the step-through method if you get unintended results from running the macro or if an error occurs when you run it.

To run the macro, just click the Run button on the toolbar. You can also run a macro from the Database window by choosing the Tools | Macro | Run Macro option. Select the macro you want to run when prompted. If an error occurs when you run the macro, read it and then open the Action Failed dialog box. This dialog tells you which action failed and the arguments being used when it failed. Click "Halt" to stop the macro's execution. Modify the macro appropriately by clicking the Design button while the macro you wish to modify is highlighted in the Database window. Use the Insert Rows and Delete Rows buttons to add new actions and delete unwanted actions from your macro.

Alternatively you can step through the macro, one action at a time, to see how it works. Open the Macro in design view, select the Single-Step toolbar button and then press the Run button. Your options in the step-through dialog box include Step, Halt, and Continue. Step moves you to the next action in the sequence; Halt stops the macro's execution; Continue stops single-step operation and simply runs the rest of the macro. If your macro causes other macros to run, the results of those macros will also be displayed. Here's what the basic step-through dialog box looks like:

Getting Fancy with Macros

You can easily add conditions (which make the macro run only under specific criteria) to your macros. You apply a condition to a certain action in the macro sheet and when you run the macro, if the condition is not met, the action is skipped and the next action in the sequence is run. You can make conditions apply to multiple sequential actions by putting an ellipsis (…) in the next action's Condition column.

You can also create If…Then…Else structures in macros using conditions. Add the Conditions column to the macro sheet by selecting the View | Conditions menu option. You cannot use SQL expressions in conditions.

To force a choice between two actions you can use two different versions of the same condition. Here's an example:

IsNull("First Name") The action runs if the field is empty

Not IsNull("First Name") Runs if the field is not empty

You can assign macros to specific event properties in your forms and reports. All you have to do is set the event property of the corresponding control to the name of the macro.

Some of the more common macro-related tasks include the following: validation, filtering, message boxes, and setting values. Let's start with validation. Of course, you could set a validation rule in the control you want validated. However, more complex data validation requires macros. Use a macro instead of a validation rule if any of the following is required for proper validation:

• The validation refers to another form's controls.
  
• Multiple error messages should be able to be displayed for different types of errors.
  
• The rule utilizes conditions based on more than one value.
  
• The user should be able to override the rule.
  
• The validation rule is generic and can be used for multiple forms.

You can use a filtering macro to, for example, set buttons that display different, filtered, data sets. You can do this by using an option group for the buttons. Each button, when pressed, returns a value. This value in turn is used as a condition for the ApplyFilter action. The Where Condition argument specifies the filter you want to use.

Use the MsgBox action to create message boxes in response to certain actions. Use it to display warnings, alerts, and so forth. You can set the Message, Beep, Type, and Title options for this action. Message is the message to display; Beep is just a sound and can be set to either yes (a sound) or no (no sound); Type sets the icon to use (such as the red circle with an x); and Title sets the message box's title bar.

Use the SetValue action to set values. SetValue has two arguments: Item and Expression. Item refers to the field you want to set the value of, and the Expression argument sets the new value for the field.

The last thing I'll mention in this week's lesson is AutoExec. AutoExec is a file that runs when a program opens. You can create an AutoExec macro that runs when you open the database by simply saving your macro as "AutoExec"; the database can have only one AutoExec macro.