Section 15.6. Conditional Macros | Access 2007[c] The Missing Manual

15.6. Conditional Macros

The macros you've seen so far are linear . They run all their actions from start to finish. If that seems a little boring, well, it is. But your macros don't need to stay that way. You can give let them make decisions and perform conditional actions. As a bonus, you can easily set them up.

In order to create a conditional macro, you need to use the Condition column. Ordinarily, this column isn't shown. To make it visible while you're using the macro builder, choose Macro Tools Design Show/Hide Conditions.

Unsurprisingly, the Condition column's the place you put conditions . A condition's a little like an expression, except it always produces one of two results: True or False. Access examines the condition, and uses it to decide whether or not to perform the corresponding action. (This is known as evaluating the condition in programmer-speak.)

Here's how it works:

If you leave the Conditions column blank (as with the actions in an ordinary macro), Access always runs that action, unless it's derailed by an error.
If you supply a condition, and the condition turns out to be True, Access runs the corresponding action.
If you supply a condition, and the condition turns out to be False, Access skips over the action and continues with the next one in the list.

Prestoyou have a way to make an action run only sometimes, when you need it.

15.6.1. Building a Condition

This discussion raises one excellent questionnamely, how do you build a condition? The simplest types of conditions compare two different values. Here's an example:

 [ProductName] = "Baloney"

This condition compares the current value of the ProductName control with the word Baloney . If ProductName currently contains that text (and only that text), this condition's True. If ProductName contains anything else, the condition's False.

Tip: Sometimes you wind up with exactly the opposite of the condition you want. In a pinch , you can always reverse a condition by putting the word Not at the beginning. Not [ProductName]="Baloney " is True only if the current item isn't everyone's favorite meat product.

The equal sign (=) is one of the staples of conditional logic, but it's not your only option. You can also use the greater than (>) and less than (<) symbols, and the "not equal to" (<>) operator. (You learned to use these operators with validation expressions in Section 4.3.2.2.) Here's an expression that checks if a numeric field's above a certain value:

 [Price] > 49.99

For even more fun, you can throw your favorite Access functions into the mix. (Chapter 4 and Chapter 7 describe plenty of useful functions.) Here's a condition that checks the length of a field, and then evaluates to True if the field's less than three characters :

 Len([FirstName]) < 3

Instead of using the operators you've seen so far to create your own conditions, you can use a function that gives you a True or False result. Programming nerds call a result that can be True, False, and nothing in between a Boolean value, after the British uber- mathematician George Boole.

Access has quite a few functions that return Boolean values, but the all-star most valuable one out of the bunch is named IsNull() . As you learned earlier (see Section 4.1.2), null fields are fields that don't have any information in them. IsNull( ) checks if a given field or control's empty. Here's how you can use it to pick up a missing last name :

 IsNull([LastName])

This condition evaluates to True if there's no value in the current LastName field.

This technique's a basic building block of validation logic (as you'll see in the next section). You use IsNull( ) to spot missing information, and then warn the person using your macro that they've left out something important.

Finally, the last trick you may want to try with conditions is combining more than one to make still more powerful super-conditions. You have two keywords that can help you join conditions: And and Or.

And enforces two conditions at once, making your condition that much more stringent. The following condition evaluates to True only if both the FirstName and the LastName fields are longer than three characters apiece:

 Len([FirstName]) < 3 And Len([LastName]) < 3

Or gives you two alternate ways to satisfy a condition. The following condition evaluates to True if the FirstName or the LastName field's empty. It evaluates to False only if both fields have text in them.

 IsNull([FirstName]) Or IsNull([LastName])

With all these building blocksconditional operators, functions, and the NOT, AND, and OR keywordsyou can build conditions galore. In the next section, you'll see an example that puts conditions to work.

15.6.2. Validating Data with Conditions

Many Access gurus use macros to prevent bad edits and other suspicious data operations (like insertions and deletions). Now that you understand how to write conditions, you can easily create this sort of validation logic.

Note: As you learned in Chapter 4, Access has several tools that can help safeguard the data in your table, including input masks, validation rules, and lookups. You should always try to use these features before you resort to macro code. However, there are many types of errors that require macro logic. One common example is when the validity of one field depends on the value in another field.

The first step's to react to the right eventsmainly, the Before Insert, Before Update, and Before Del Confirm events of a form. When these events occur, you can perform your conditional logic to check for error conditions. If you see something you don't like, then use the CancelEvent action to stop the process altogether (and thus cancel the insert, update, or delete operation).

Note: CancelEvent doesn't use any argumentsit simply halts the process currently underway. CancelEvent works with any event that starts with the word "Before," which indicates the actual operation's just about to take place, but hasn't happened yet.

Suppose you want to create a simple condition that stops certain record updates. Consider the form shown in Figure 15-21.

Figure 15-21. When you turn on the "Please notify me about special offers" checkbox, the WantsEmail field's set to Yes. However, this record has a glitchthere's no value in the Email field.

In this example, a missing email causes severe headaches . You could solve the problem by changing EmailAddress into a required field (Section 4.1.1), but what you really want is something a little more sophisticated. When WantsEmail is Yes, the EmailAddress field shouldn't be empty. A conditional macro can implement exactly this sort of logic.

Here's the condition you need:

 WantsEmail = Yes And IsNull([EmailAddress])

This condition evaluates to True if WantsEmail is Yes and the EmailAddress is blank. When that happens, it's time to cancel the update using the CancelEvent action.

Note: There's another option. You could use the SetValue action to fix up invalid values. But it's usually better to let the person who's making the update try to fix the problem.

When you cancel an event, it doesn't roll back the process altogether. Instead, it stops you from finishing the operation. If you modify a record, and then try to move to another record, the Before Update event fires. If a macro cancels the Before Update event, then Access doesn't let you move oninstead, you're locked in place. However, the current record stays in edit mode, with all the same edited values. It's up to you to fix the problem or hit Esc to cancel the update attempt altogether.

The conditional CancelEvent action's the heart of many validation macros. However, you still need one more finishing touch: an error message. Otherwise, the person who's making the update or insert won't have any idea what's wrong. They're likely to think that Access has gone completely off its rocker.

To show the error message, you can use the MsgBox action. Obviously, you want to show the error message only if the error's actually occurred, so both the CancelEvent and the MsgBox actions need to be conditional.

Access has a fantastic shortcut to help you out when you need to use the same action twice. Rather than repeating the same condition next to each action (which is a bit messy), just put three dots ( ) in the Condition column for each subsequent action. These three dots are a shorthand that tells Access to use the condition from the previous action.

Figure 15-22 shows the completed macro, and Figure 15-23 shows the macro in action.

15.6.3. More Complex Conditional Macros

As conditional macros get longer and more complex, they can become a little awkward to manage. You may have several conditions on the go at once, each requiring separate actions. If some conditions are true, then you may want to skip other conditions entirely. Or, you may want to stop running the macro.

To see a typical example of the challenges you'll face, it's worth revisiting the StopMissingEmail macro you saw in the previous section. But this time, you'll add a new wrinkle. Instead of canceling the update or insert, your macro will ask for confirmation that this really and truly is what you want to do, as shown in Figure 15-24.

Figure 15-22. This macro consists of two conditional actions. To lock out bad data, attach this macro to both the BeforeUpdate and the BeforeInsert event.

Figure 15-23. Here, the macro detects the missing email address and explains the problem.

Figure 15-24. Now it's up to you to decide whether to apply this apparently contradictory update: signing up for email updates, but not providing an email address. (Maybe you really do want regular emails, but you need to come back later to add the right email address.)

Creating a confirmation message is easy enough. You can do it all with this oddlooking condition:

 MsgBox("Is this really what you want to do?", 4) = 7

The condition's first part uses the MsgBox( ) function to show a Message box. The number 4 tells Access to that the Message box should include a Yes button and a No button. The MsgBox( ) function returns a result of 6 if you click Yes, and 7 if you click No, so this condition's True only if you click No to cancel the change.

Note: You could easily confuse the MsgBox( ) function with the MsgBox macro action. The two are closely related . However, it's the MsgBox( ) function that makes this example work, because you can trigger it from inside a condition. If you want to learn more about the MsgBox function (like what other options it provides for showing different buttons ), click the Access Help button, and then search for "MsgBox function."

To sum up, you need a macro that checks for invalid data and, if it exists, shows a confirmation Message box. Conceptually, this macro isn't too tricky. But if you try to build the macro you need, you'll find that there's no good way to combine the conditions. You can check for invalid input easily enough, but how do you make sure the second condition (the Message box) is shown only if the first condition was met?

The best way to solve problems like these is to use the StopMacro and RunMacro actions. StopMacro ends the current macro, which makes it a handy way to skip out of a macro if you know the following actions don't apply. RunMacro launches another macro, which makes it a good way to run a separate task when a specific condition's met.

Using StopMacro, you can complete the AskAboutMissingEmail macro. Here's what you need to do:

Use a condition to check if the data's valid. If it is, you don't need to take any more steps, so run the StopMacro action .
If the macro's still running, there's a missing email address. The next action uses the confirmation message as a condition. If No's clicked, run CancelEvent to stop the edit .

Figure 15-25 shows the complete macro.

Figure 15-25. The revised CatchMissingEmail macro needs just two actionsone to stop processing if everything's OK, and one to cancel the update if someone clicks No in the confirmation Message box.