Adding Conditions to a Macro

When you want a macro to run only under specific circumstances, you can add a condition to one or more of the macro actions. The macro condition states this effectively: If this condition is true, run this action. If it is not true, go to the next action, if any. This is a highly useful tool when programming an application. You can use conditions to set values of controls or control properties and even run additional macros. Note that such test comparisons are not case-sensitive.

The following are some examples of using conditions:

• If the balance of an account is negative, change the color of the number to red.
• If a student's grades are exemplary, print a congratulatory message.
• If the inventory level of an item is low, display a message to remind you to reorder.
• If the order exceeds a specific total, calculate the amount due with a volume discount.

NOTE: Don't confuse the macro condition that determines whether the action takes place with the Where Condition that limits the records in the form or report. The macro condition is entered in the Condition column of the macro sheet and the Where Condition is an argument of many macro actions.

To add the Condition column to the macro sheet, in the Show/Hide group, click the Conditions command. Type the logical expression for the condition in the row with the action that you want to carry out if the condition is True. If you want to use the Expression Builder to help with the expression, right-click in the Condition column and choose Build from the shortcut menu. You can also click the Builder command in the Tools group.

Normally, a condition applies only to the action on the same row in the macro sheet. If the condition isn't met, the next action is executed. To continue the condition to the next action, enter an ellipsis (...) in the Condition column of the next row. You can apply the condition to several sequential actions.

TIP: When you're debugging a macro, you can temporarily disable an action by entering False in the Condition column. This can help to isolate the problem.

You can also use conditions to create an If...Then...Else structure in a macro. This conditional logic runs one or more actions if the condition is met and a different set if the condition evaluates to False.

NOTE: You cannot use a SQL expression as a condition in a macro. SQL expressions are used only in Where Condition arguments.

Running a Macro with a Condition

When you run the macro, Access evaluates the condition and does the following:

• If the condition is True, Access runs the action on that row, and all actions directly following it that have an ellipsis (...) in the Condition column. Then Access runs any additional actions that have blank conditions until it encounters another condition, a macro name in the Macro Name column, or the end of the macro.
• If the condition is False, the action is ignored as are any additional actions with an ellipsis (...) in the Condition column. Then Access moves to the next action, if any.

Table-1 shows some examples of expressions that you can use as conditions with macro actions. All the fields in the expressions are in the form from which the macro originates, unless otherwise specified.

NOTE: Notice the use of identifiers to specify a control in a form other than the one from which the macro was launched. The referenced form must be open at the time the macro runs.

Choosing between Two Actions

You can use a macro to make one thing happen if a condition is true and make something else happen if it's false: you simply create two versions of the same condition. If you follow the conditional action with another action without using the opposite condition, the second action is always carried out.

For example, you want to do one thing if the Start Date is empty and carry out a different action if it shows a date. You could use the IsNull function in both conditions as follows:

• IsNull("Start Date") Carry out the action when the Start Date is empty.
• Not IsNull("Start Date") Carry out the action for a Start Date value.

ExpressionEvaluates to True If:[State]="NY"NY is the value in the State control.Forms![Alpha Entry]![Purge]<Date()The Purge field on the Alpha Entry form is earlier than the current date.[State] In ("NY","AZ","NV","NM") And Len([ZipCode])<5The State value is one of those in the list and the value in the ZipCode field contains fewer than five characters. Combines two conditions into a single expression.DCount("*","Alpha Entry", "[Index]=Forms![Alpha Card]![Index]")>5More than five records are in the Alpha Entry table whose Index value matches an Index value in the Alpha Card table. Uses the DCount aggregate function.