Changing the Flow of Operations

You can control the flow of operations by adding conditions that determine whether a macro action is carried out. If the condition evaluates to True, the corresponding action takes place. You can add the MsgBox function to a macro condition to let the user decide which action to carry out.

The MsgBox function is similar to the MsgBox action with the exception that the function returns one of seven different values, depending on which button the user clicks in the message box. The MsgBox function displays a message box containing the message and waits for the user to click a button indicating a choice.

The MsgBox function has three main arguments; only the first is required:

• Prompt is a string expression displayed in the dialog box. You can display up to 1024 characters, depending on the font size.
• Button is a number equal to the sum of three values that specify the visual characteristics of the message box, such as the number and type of buttons, the default button, the icon style, and the modality of the message box.
• Title is a string expression displayed in the dialog box title bar.

Two additional optional arguments can specify a Help file and context number in the file where you can find context-sensitive help.

You can display seven different buttons in various arrangements, as well as a choice of four icons. You can also specify which of the buttons is the default. Table-3 lists these button arrangements and dialog box features with their numeric values. The buttons are placed in the message box in the order-from left to right-that they're listed in the table. For example, if you want the message box to display the Yes, No, and Cancel set of buttons (3) with the Warning Query icon (32) and set the Yes button as the default (0), enter 35 (3 + 32 + 0 = 35) as the second argument in the MsgBox function.

NOTE: The message box normally opens as an application modal, which requires the user to respond to the message box before continuing. Additional argument settings can be used to change that. For example, if you want to make it system modal, which suspends all applications in the system until the user responds, add 4096 to the other values in the second argument.

When the user clicks a button in the message box, the corresponding value is returned, which the macro condition can use to determine the next action to take. Table-4 lists the values returned by each type of button.

TABLE-3 MsgBox Function Button Argument Settings

ValueButtons to Display:0Display only the OK button.1Display the OK and Cancel buttons.2Display the Abort, Retry, and Ignore buttons.3Display the Yes, No, and Cancel buttons.4Display the Yes and No buttons.5Display the Retry and Cancel buttons.Icon to Display:0Display no icon.16Display the Critical Message icon.32Display the Warning Query icon.48Display the Warning Message icon.64Display the Information Message icon.Specify the Default Button:0Set the first button as default.256Set the second button as default.512Set the third button as default.768Set the fourth button as default.

TABLE-4 Values Returned by MsgBox Function

ButtonReturned ValueOK1Cancel2Abort3Retry4Ignore5Yes6No7

When you use the MsgBox function in a macro condition, you can compare the returned value to a specific number and carry out the action if the comparison is True. For example, you can use the MsgBox function to display a confirmation message before deleting a record. The box contains three buttons: Yes, No, and Cancel. If the user clicks the Yes button, the function returns 6, so if any other value is returned, the user didn't click Yes. This shows a macro that uses the MsgBox function in a condition that evaluates to True if the function returned any value except 6 (Yes). If the value isn't 6, the deletion event is canceled. You could add other conditions that carry out actions as a result of the other button selections.

The Verify Deletion macro should be embedded in or attached to the form's Before del Confirm event property. The message box displays when you select a record and press del. For example, the Alpha Entry record for Index 35 was selected before pressing del. You can see it's been deleted from the Form view, but isn't confirmed yet. If you respond by clicking Yes, Access displays another confirmation message if the deletion can result in cascade deletions of other records or interfere in some other way with the relationships in the database.

Nesting Macros

If you want to run one macro from another macro, use the RunMacro action and set the Macro Name argument to the name of the macro that you want to run. The RunMacro action is similar to clicking the Run Macro command in the Macro group on the Database Tools tab and selecting the macro name. The only difference is that the Run Macro command runs the macro only once. With the RunMacro action, you can repeat the macro many times.

The RunMacro action has two arguments in addition to the Macro Name:

• Repeat Count Specifies the maximum number of times the macro is to run.
• Repeat Expression Contains an expression that evaluates to True (-1) or False (0). The expression is evaluated each time the RunMacro action occurs. When it evaluates to False, the called macro stops.

The Repeat Count and Repeat Expression arguments work together to specify how many times the macro runs.

• If both are blank, the macro runs only once.
• If Repeat Count contains a number, but the Repeat Expression is blank, the macro runs the specified number of times.
• If the Repeat Count is blank, but the Repeat Expression contains an expression, the macro runs until the expression evaluates to False.
• If both arguments contain entries, the macro runs the specified number of times or until the expression evaluates to False, whichever occurs first.

When the called macro is finished, Access returns to the calling macro and runs the next action after RunMacro.

NOTE: You can call a macro in the same macro group, as well as a macro in another group. If you enter a macro group name as the Macro Name argument, the first macro in the group runs.

You can nest macros to more than one level. The called macro can, in turn, call another macro, and so on. As each macro finishes, it returns control back to the macro that called it.