Getting to Know Microsoft Access, Part 9: Macros - Getting Fancy with Macros
(Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
