Some Common Uses for Macros

When you work with a database, Access causes many things to happen in response to your actions. You might be unaware that you can customize the database with macros to accomplish many similar operations according to your designs. Here are some of the more common applications for macros.

Displaying a Message Box

The MsgBox action is one of the most useful macro actions when interacting with the user. You can use it to display warnings, alerts, and other information. The MsgBox action has four arguments: Message, Beep, Type, and Title. Table 19-2 describes the values that you can enter in each of the arguments.

TIP: Don't enclose the message in quotation marks unless you also want the marks displayed with the message.

If you like the format of the built-in Access error messages, you can create the same effect with your own messages. The Message action argument can contain three sections separated by the @ character. The first section is text displayed as a boldface heading and can be used as an alert. The second section appears in plain text below the heading and is used for an explanation of the error. The third section appears below the text of the second section, also in plain text, with a blank line between. For example, the message, "Invalid Code@Look up the correct code in the log @Should have less than 6 numbers" displays this dialog box when a code of more than 5 numbers is entered.

ArgumentDescriptionMessageEnter the text of the message you want displayed when the macro runs. You can enter up to 255 characters; the box expands accordingly. You can also enter an expression preceded by an equal sign (=) that evaluates to a text message.BeepSpecifies whether to sound a beep signal when the message box opens. Set to Yes (default) or No.TypeSets the type of message box, each of which displays a different icon. Choices are None (default); Critical (a red circle with an X); Warning? (a bubble with a question mark); Warning! (a yellow triangle with an exclamation mark); or Information (a bubble with a lowercase i).TitleText that displays in the message box title bar. If left blank, the box is titled Microsoft Office Access.

Validating Data

Usually, you ensure that valid data is entered in a form by specifying a validation rule for the control in the form or by setting record and field validation rules in the underlying table design. For more complex data validation, use a macro or an event procedure to specify the rule.

The recommendation is that you use a macro or an event procedure if any of the following situations are present:

• You want to display different error messages for differing errors in the same field. For example, if the value is above the valid range, display one message; if the value is below the valid range, display another.
• You want the user to be able to override the rule. In this case, you can display a warning message and accept the user's confirmation or cancellation.
• The validation refers to controls in other forms or contains a function.
• The validation rule involves conditions based on more than one value. For example, if the user checks Credit Card as the method of payment, be sure that the number and expiration date are also entered in the form.
• You have a generic validation rule that can be used for more than one form. When you want to apply it to a control on a form, run the SetValue macro action that sets the Validation Rule property for the control.