Creating Conditional Expressions

The Expression Builder can help you enter your macros.

A conditional expression.

The updated mcrSales macro.

A condition takes action based on a certain condition. For example, if an employee's weekly sales are more than $2,500, then a condition could calculate a 5-percent commission bonus for the employee; otherwise, it wouldn't calculate a bonus. If you're at all familiar with programming, a condition is similar to an If...Then statement.

You enter conditions in the Condition column in the Macro window. If a condition is true, Access executes the action in that row. If a condition isn't true, Access skips the action in that row and moves to the next row. Conditions often compare values in a specific control on a form or report to a number, date, or constant. For example, the expression in figure evaluates if the value in a City field is not equal to "Minneapolis." Make sure that you use the proper Microsoft Access syntax when referring to controls in forms or reports.

1. Make sure you have the frmEmployees form from the previous lesson open. Click the View button on the toolbar to switch to Design view.

   We want to add a conditional expression to the mcrEmployees macro. If a macro is assigned to a control on a form or report, you can open and edit the macro directly from the form or report without having to access it through the Database window.

2. Select the command button, click the Properties button on the toolbar, click the Event tab, and click the On Click box.

   A Build button appears in every event property. Click this button to create or modify the macro or Visual Basic procedure assigned to the event.

3. Click the Build button.

   The mcrSales macro appears in Design view.

4. Click the Conditions button on the toolbar.

   The Condition column appears. This is where you need to add the conditions you want Access to evaluate before it executes an action. It's often easier if you use the Expression Builder to help you create your macro conditions.

5. Click the first blank cell in the Condition column and click the Build button on the toolbar.

   The appears, as shown in figure.

6. Double-click the Forms folder in the bottom-left window, double-click the All Forms folder, then click thefrmEmployees folder.

   When you select the frmEmployees folder in the left window, the middle window displays all the controls in the selected form.

7. Scroll down the middle window, and find and double-click the City control.

   Access adds Forms![frmEmployees]![City] to the expression area. Now you need to specify how you want to evaluate the City field.

8. Click in the Expression box and add "Minneapolis".

   Your expression should look similar to the one in figure.

9. Click OK.

   The Expression Builder dialog box closes. The condition you entered will execute the OpenForm action only if the City field is not equal to "Minneapolis." The condition you entered only affects the first row or action in the macrothe other actions in the macro will execute without being evaluated. If you want to evaluate the other actions, they must each have their own statement in the Condition column. Let's add some more actions to the macro.

10. Copy the first row in the Condition column and paste it in the second and third rows.

    Add another action that will execute only if the City is not equal to "Minneapolis."

11. Click the Action cell in the second row, click the list arrow and select MsgBox. Click the Message argument box and type This is the current commission for non-Minneapolis employees.

    Next you need to add an action to perform if the City is equal to "Minneapolis."

12. Edit the expression in the third row of the Condition column so it reads [Forms]![frmEmployees]![City]="Minneapolis".

    Your macro should look like figure. Now you need to specify the action to perform if the condition is true.

13. Click the Action cell next to the condition you edited, click the list arrow, and select MsgBox. Click the Message argument box and type Call Linda Ross for the Minneapolis Commission report.

    We're finished modifying the macro.

14. Save your changes and close the Macro Design window. Click the View button on the toolbar to display the form in Form view.

    Let's test our conditional macro.

15. Find a record whose City field is NOT "Minneapolis" and click the Sales Report button. Click OK and then close the commission report. Click the Save button and close the frmEmployees form.

TO CREATE A CONDITIONAL EXPRESSION IN A MACRO:

1. CREATE A NEW MACRO OR EDIT AN EXISTING MACRO.

2. IN DESIGN VIEW, CLICK THE CONDITIONS BUTTON ON THE TOOLBAR.

3. CLICK THE CONDITION CELL NEXT TO THE ACTION YOU WANT TO EVALUATE.

4. ENTER THE CONDITIONAL EXPRESSION IN THE CONDITION CELL, USING PROPER ACCESS SYNTAX. YOU CAN USE THE EXPRESSION BUILDER TO HELP YOU CREATE THE EXPRESSION BY CLICKING THE BUILD BUTTON ON THE TOOLBAR.

5. REPEAT STEPS 3 AND 4 FOR EACH ACTION YOU WANT TO EVALUATE.

6. SAVE THE MACRO AND CLOSE THE MACRO WINDOW.