Automating a Client Application Using Macros in Microsoft Access 2010

Understanding Conditional Expressions

In some macros, you might want to execute some actions only under certain conditions. For example, you might want to update a record, but only if new values in the controls on a form pass validation tests; or you might want to display or hide certain controls based on the value of other controls. You can use an If block in macros to test conditions and then perform different actions based on the outcome of the conditional expression.

The PrintIt submacro in the DoReport macro group is a good example of a macro that uses conditions to determine which action should proceed. Right-click the DoReport macro in the Navigation pane, and then click Design View on the shortcut menu to see the Logic Designer, shown in Figure 20-11

As you saw earlier, this macro is triggered by the On Click property of the Print button on the PrintOptions form. This form allows the user to print a specific report by selecting the appropriate option button and then clicking Print. If you look at the form in Design view (see Figure 20-10), you’ll see that the option buttons are located within an option group control on the form. Each option button sets a specific numeric value (in this case, 1 for the first button, 2 for the second button, 3 for the third button, and 4 for the fourth button) in the option group, which you can test using an If program flow construct.

As you learned in Chapter 7, when you include an If block in a macro, Access won’t run the action on that line unless the condition evaluates to True. The text box next to If is where you type your conditional expression. Each condition is an expression that Access can evaluate to True (nonzero) or False (0 or Null). A condition can also consist of multiple comparison expressions and Boolean operators. If the condition is true, Access executes the action or actions immediately following the Then keyword. If the condition is false, Access evaluates the next Else If condition or executes the statements following the Else keyword, whichever occurs next. If no Else or Else If condition exists after the Then keyword, Access executes the next action following the End If keyword.

Figure 20-11 In the PrintIt submacro, you can see that we added an If block in the DoReport macro group.

In this particular example, the expressions next to If and the three Else If conditions in the PrintIt submacro test the value of the option group control on the form. You can reference any control on an open form by using the syntax

FORMS!formname!controlname

where formname is the name of an open form and controlname is the name of a control on that form. In this case, the direct reference is [FORMS]![PrintOptions]![optPrint]. (optPrint is the name of the option group control. You can see this in the Name property on the Other tab of the property sheet for this control.) See “Referencing Form and Report Objects,” on page 1229, for more details about the rules for referencing objects in Access.

After you understand how to refer to the value of a control on a form, you can see that the PrintIt submacro tests for each of the possible values of the option group control. When it finds a match, PrintIt runs the appropriate named submacro within the macro object to open the requested report and then stops. If you look at the individual report macros, you’ll see that each runs a common submacro, DoReport.Cancel, to close the PrintOptions form (which isn’t needed after the user chooses a report) and then open the requested report in Print Preview and put the focus on the window that displays the report.