Access 2007 includes a feature that allows you to define dynamic modification of the formatting of text boxes and combo boxes. 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 13–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 13–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 Design tab, in the Font group, click the Conditional button to see the Conditional Formatting dialog box.
In the Default Formatting box, you can see the currently defined format for the control. You can use the Bold, Italic, Underline, Fill/Back Color, Font/Fore Color, and Enabled buttons to modify the default. When you first open this dialog box, Access displays a single blank Condition 1. 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 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:
Set the formatting properties you want the control to have if the test is true by using the buttons to the right. In this case, set the Fill/Back Color to a bright yellow as shown in Figure 13–28, and click OK.
Figure 13–28: Define conditional formatting for the ProductName field using the Conditional Formatting dialog box.
Switch to Form view to see the result as shown in Figure 13–29, and move to the third company record. You can find the sample saved as fsubXmplCompanyContactProducts2 and the inner subform saved as fsubXmplContactProducts2.
Figure 13–29: You can now see the effect of defining conditional formatting for the ProductName field.
You can make a similar change to frmContacts2 that 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 button in the Font group on the Design tab to see the Conditional Formatting dialog box.
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 highlight the text as shown in Figure 13–30, and click OK to close the dialog box and set the conditional formatting for the controls you selected.
Figure 13–30: You can also define conditional formatting for a group of controls.
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 button in the Font group again, choose Expression Is in the leftmost list, enter [DefaultAddress]=2 in the Condition field, and underline and highlight the text. Click OK to save the change, and save your form as frmContacts3. Switch to Form view to see the result as shown in Figure 13–31. All the records in the database have work address as the default, so try changing the Default Address option 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 13–31: 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 Add button at the bottom of the Conditional Formatting dialog box. Each time you click this button, Access displays an additional Condition definition row. In the second and subsequent rows, you can choose from Field Value Is or Expression Is in the leftmost list. (You can check for focus only in the first test.) For example, you might want to set the background of the product name to one color if it’s a trial version and use another color for products priced greater than $200.