Access includes a feature that allows you to define dynamic modification of the formatting of text boxes and combo boxes on client forms. (Web forms do not support conditional formatting.) You can define an expression that tests the value in the text box or combo box or any other field available in the form. If the expression is true, Access will modify the Bold, Italic, Underline, Back Color, Fore Color, and Enabled properties for you based on the custom settings you associate with the expression.
This feature can be particularly useful for controlling field display in a subform in Continuous Forms view. For example, you might want to highlight the ProductName field in the innermost subform shown in Figure 15-21 when the product is a trial version; or you might want to change the font of the address fields in the form shown in Figure 15-27 depending on the value of the DefaultAddress field.
For the first example, you can use the fsubCompanyContactProducts subform that you built earlier (or the fsubXmplCompanyContactProducts sample form you’ll find in the sample database). To define conditional formatting, first open the form you need to modify in Design view. Click the subform control, and then click the ProductName field within the subform to select it. On the Format tab, in the Control Formatting group, click the Conditional Formatting button to see the Conditional Formatting Rules Manager dialog box, as shown in Figure 15-28. In the Show Formatting Rules For combo box, Access displays a list of all names of controls that support conditional formatting. You can create, edit, and delete conditional formatting rules for the controls on your form by selecting the control name from this combo box, without having to close the dialog box to select a different control. In previous versions of Access, you were limited to three conditional formatting rules for each control. In Access 2010, you can now have up to 50 conditional formatting rules for each control.
Figure 15-28. You can create, edit, and delete conditional formatting for controls using the Conditional Formatting Rules Manager dialog box.
To begin creating a new conditional formatting rule for the ProductName text box, select ProductName in the Show Formatting Rules For combo box (it should already be selected), and then click the New Rule button to open the New Formatting Rule dialog box, as shown in Figure 15-29. In Select A Rule Type, you can choose to create a conditional formatting rule that checks other values in the current record or use an expression. You can also choose to compare the value of this control with values in the same control for other records (we’ll do the second option in just a moment). For this ProductName control rule, select Check Value In The Current Record Or Use An Expression. Access displays a single blank condition rule under the Select A Rule Type list box. In the leftmost list, you can choose Field Value Is to test for a value in the field, Expression Is to create a logical expression that can test other fields on the form or compare another field with this one, and Field Has Focus to define settings that the control will inherit when the user clicks in the control.
When you choose Field Value Is, the dialog box displays a second list with logical comparison options such as Less Than, Equal To, or Greater Than. Choose the logical comparison you want, and then enter the value or values to compare the field with in the text boxes on the right.
In this case, you want to set the format of ProductName based on the value of the TrialVersion field. So, choose Expression Is, and in the expression box, enter the following:
[TrialVersion]=True
If you need assistance creating your expression, you can click the Builder button to the right of the expression text box to open the Expression Builder. Set the formatting properties you want the control to have if the test is true by using the buttons to the right. You can use the Bold, Italic, Underline, Background Color, Font Color, and Enabled buttons to modify the default. In this case, set the Background Color to a bright yellow, as shown in Figure 15-29, and click OK to save the new rule.
Figure 15-29. Define conditional formatting for the ProductName field using the New Formatting Rule dialog box.
You should now be back in the Conditional Formatting Rules Manager dialog box. Click Apply to save the rule changes you’ve created so far. Select UnitPrice in the Show Formatting Rules For combo box to define a new conditional formatting rule for the UnitPrice control. Note that if you did not click Apply before choosing a new control, Access prompts you that you will lose the rule changes if you don’t apply the changes. You can choose to continue and discard your changes, continue but have Access apply the changes first, or cancel and stay in the current control. Click New Rule to open the New Formatting Rule dialog box again.
Access 2010 includes a new feature for conditional formatting called data bars. In the previous example, you created a conditional formatting rule that compares values only within the current record. Data bars allow you to compare values in the same control to other records. Data bars give a visual indication of how the value in this control compares to values in other records by displaying a colored bar inside the control. Access fills a proportional amount of the background color from the left side to indicate what percentage the current value has compared to the other records. For example, if the value in the current record is the highest value in all records, Access fills the entire control from left to right with a background color to indicate that it has the maximum value. (If you’re using a right-to-left form orientation, Access fills the control from right to left.) Select the option Compare To Other Records under the Select A Rule Type to display the data bar rules description, as shown in Figure 15-30.
If you leave the Show Bar Only option cleared, Access displays the value of the control and fills the background color of the control. Select this option if you want Access only to fill the background color, not show control values. Under Shortest Bar and Longest Bar, Access displays two options—Type and Value. In the Type combo box, you can select to use Lowest Value (or Highest Value under Longest Bar), Number, or Percent. If you use the default, Lowest Value or Highest Value, Access uses the lowest and highest values it finds in the control for all records returned by the form’s record source. If you select Number or Percent, Access enables the Value text box controls. In the Value text boxes, you can specify a specific value or percent for Access to use when it compares the control values for each record. On the right side of the Value text boxes, you can click the Builder button to open the Expression Builder if you want assistance building an expression to be used for the Value text boxes. For our example, leave the options here set on their defaults.
Inside Out: Opening Conditional Formatting Rules in Previous Versions of Access
If you open a form that includes the new data bar conditional formatting in previous versions of Access, you will not see the data bars in your form controls. Previous versions of Access do not know how to interpret these types of conditional formatting rules across other records, so they ignore these rules. If you define more than three conditional formatting rules on a form in Access 2010 and open that form in previous versions of Access, you will see only the first three rules applied. Previous versions of Access can understand only three conditional formatting rules, so that is all you will see displayed.
You can click the down arrow on the Bar Color combo box to select a background color to use for the data bar. Access displays a preview of your color choice to the right of the Preview label. In this example, click the down arrow next to Bar Color and set the bar color to a lighter blue than the default dark blue, as shown previously in Figure 15-30. Click OK to save your changes. Access displays your new data bar rule in the Conditional Formatting Rules Manager dialog box, as shown in Figure 15-31. Access lists Data Bar for each conditional formatting rule you define as a data bar. Access displays the conditional expression in this space if you define a rule that uses an expression.
Figure 15-31. Access displays any saved rules for your controls in the Conditional Formatting Rules Manager dialog box.
If you need to edit your saved conditional rule, highlight the rule in the bottom half of the dialog box and then click Edit Rule. Access opens the New Formatting Rule dialog box again, where you can adjust your rule settings. If you want to delete an existing rule, highlight the rule and then click Delete Rule. If you have more than one conditional formatting rule for a control, you can use the Move Up and Move Down buttons to the right of the Delete Rule button to change the order in which Access evaluates your rules. Select a rule and then click Move Up to move the rule up one position in the sequence. Similarly, highlight a rule and click Move Down to move the rule down one position in the sequence. Click OK to save your changes for these two controls.
Inside Out: Using Multiple Data Bar Rules on the Same Control
You can define more than one data bar rule for a specific control. When you view the form in Form view, Access displays the colors you define in equal proportions across the height of the control.
Switch to Form view to see the result, as shown in Figure 15-32, and move to the third company record. You can find the sample saved as fsubXmplCompanyContactProducts2 and the inner subform saved as fsubXmplContactProducts2.
Figure 15-32. You can now see the effect of defining conditional formatting for the ProductName and UnitPrice fields.
Inside Out: Using Field and Data Bar Rules on the Same Control
Access allows you to create conditional formatting rules that compare values in the same record and values across other records (data bars) for the same control. You need to be aware that if you have the data bar rule first, Access ignores any formatting rules after the data bar rule that compares values in the same record. To work around this issue, move any data bar rules to the bottom of the sequence order.
You can make a similar change to frmContacts2, which you saved earlier, or you can use the sample frmXmplContacts2 form. Open that form in Design view, click the WorkAddress text box control to select it, and hold down the Shift key as you click the WorkPostalCode, WorkCity, and WorkStateOrProvince controls to add them to the selection. (Yes, you can set conditional formatting for multiple controls at one time.) Click the Conditional Formatting button in the Control Formatting group on the Format tab to see the Conditional Formatting Rules Manager dialog box.
In the Show Formatting Rules For combo box, Access displays Multiple to indicate you have selected multiple controls to apply conditional formatting rules. Click New Rule to begin defining a new rule for these four controls. Select Check Values In The Current Record Or Use An Expression under Select A Rule Type. Next, choose Expression Is in the leftmost list, and enter [DefaultAddress]=1 in the Condition field to test whether the default is the work address. Underline and set the Background Color to yellow to highlight the text, as shown in Figure 15-33, and click OK to close the dialog box and set the conditional formatting for the controls you selected. Now, click OK on the Conditional Formatting Rules Manager dialog box to save your changes and close the dialog box.
Click the HomeAddress text box, and hold down the Shift key as you click HomePostalCode, HomeCity, and HomeStateOrProvince to add them to the selection. Click the Conditional Formatting button in the Control Formatting group again, click New Rule, and then select Check Values In The Current Record Or Use An Expression to begin defining a new rule for these controls. Choose Expression Is in the leftmost list, enter [DefaultAddress]=2 in the condition field, and underline and set the Background Color to yellow to highlight the text. Click OK to save the change to this rule on the New Formatting Rule dialog box, click OK on the Conditional Formatting Rules Manager dialog box, and then save your form as frm-Contacts3. Switch to Form view to see the result, as shown in Figure 15-34. All the records in the database have the work address as the default, so try changing the Default Address in one of the records to Home, and you should see the highlight move to the home address fields. You can also find this form saved as frmXmplContacts3 in the sample database.
Figure 15-34. The default address fields are highlighted and underlined in the contacts form based on the value of the DefaultAddress field.
To define additional tests, click the New Rule button in the bottom half of the Conditional Formatting Rules Manager dialog box. For example, you might want to set the background of product name to one color if it’s a trial version and use another color for products priced greater than $200.