Highlighting data for impact on a form in Access

Applies to
Microsoft Access 2000

In Access, the appearance of a control on a form or report can differ from record to record, depending on whether:

  • The control has the focus — you're currently in the control.
  • The value in the control meets specified criteria.
  • The value of an expression or a Microsoft Visual Basic® for Applications (VBA) function is TRUE or FALSE.

You can use conditional formatting to specify that a control has a different background color, a different font style or color, or is available or unavailable when a certain condition is met. Unavailable controls appear dimmed.

You can use conditional formatting to highlight data that meets or exceeds the criteria that you have specified. Conditional formatting is particularly useful in a continuous form, which displays more than one record at a time, because it's easy to spot trends in the set of records that a continuous form displays. For example, in the following continuous form, records whose unit price is greater than $50.00 are highlighted in red, and records whose unit price is between $25.00 and $50.00 are highlighted in yellow. It's easy to see which products have unit prices that meet certain criteria. This article shows you how to create the form shown below.

Conditional formatting applied to controls on a continuous form

This article shows you how to use conditional formatting in forms that are based on the Products table in the Northwind sample database. The default location of the Northwind sample database on your computer is the Program Files\Microsoft Office\Office\Samples folder. If you don't see Northwind.mdb in the Samples subfolder of your Office folder, or if you need more information about opening Northwind, type open the Northwind sample database in the Office Assistant, or click Search on the Answer Wizard tab in the Help window.

ShowHighlighting Your Current Location in a Form

You can make data entry easier by using conditional formatting to highlight the control that has the focus. When the control you're located in is highlighted, it's easier to work with a form that contains many controls, or with a continuous form or a datasheet form that contains many records.

To create the form used in this example

  1. Open the Northwind sample database.
  2. Under Objects, click Forms, and then click New.
  3. In the New Form dialog box, click Form Wizard, click Products in the list of tables and queries, and then click OK.
  4. On the first page of the Form Wizard, move ProductName and UnitPrice from the Available Fields list to the Selected Fields list, and then click Next.
  5. On the second page of the wizard, click Datasheet, and then click Next twice.
  6. On the fourth page of the wizard, type ConditionFocus as the title of your form, click Modify the form's design, and then click Finish.

To change the font color based on your current location

  1. In the Detail section, click the ProductName box .
  2. On the Format menu, click Conditional Formatting.

Note that the default formatting is specified in the Conditional Formatting dialog box.

  1. Under Condition 1, click Field Has Focus in the first box.
  2. Click the arrow next to the Font/Fore Color button, click the blue box, and then click OK.

Condition that changes the font color to blue when the field has focus

  1. On the File menu, click Save.
  2. On the View menu, click Datasheet View to view the results.

It's easy to see your current location when you move to different records and click the ProductName field. Note that the color of the font does not change when you click records in the UnitPrice field because you have not set conditional formatting for that field.

 Note    To apply conditional formatting to several controls at once, just select those controls before you click Conditional Formatting on the Format menu. For more information about selecting more than one control, type select multiple controls in the Office Assistant, or click Search on the Answer Wizard tab in the Help window.

ShowUsing the Value in a Control as Formatting Criteria

When you base conditional formatting on the value in a control, the control's appearance changes if the value in the underlying field for that record meets criteria that you specify. For example, you can specify the UnitPrice field to be highlighted with a different color when it contains a value between $25.00 and $50.00. Also, you can specify an additional criteria to highlight the UnitPrice field with another color if it contains a value greater than $50.00. You can add up to three criteria to a control.

To create the form used in this example

  1. Open the Northwind sample database.
  2. Under Objects, click Forms, and then click New.
  3. In the New Form dialog box, click Form Wizard, click Products in the list of tables and queries, and then click OK.
  4. On the first page of the Form Wizard, move ProductName and UnitPrice from the Available Fields list to the Selected Fields list, and then click Next.
  5. On the second page of the wizard, click Tabular to create a continuous form that displays more than one record at a time, and then click Next twice.
  6. On the fourth page of the wizard, type ConditionFormat as the title of your form, click Modify the form's design, and then click Finish.

To change the appearance of a control based on its value

  1. In the Detail section, click the UnitPrice box .
  2. On the Format menu, click Conditional Formatting.
  3. Under Condition 1, make sure that Field Value Is appears in the first box.
  4. In the second box, click between; in the third box, type 25; and in the fourth box, type 50.

 Note    The between comparison phrase is inclusive. This means that the value in the control meets this condition if it contains a value of 25, a value of 50, or any value between 25 and 50.

  1. Click the arrow next to the Fill/Back Color button, click the yellow box, and click Add.
  2. Under Condition 2, make sure that Field Value Is appears in the first box.
  3. In the second box, click greater than; and in the third box, type 50.
  4. Click the arrow next to the Fill/Back Color button, click the red box, and then click OK.

Two conditions based on the value in the control

  1. On the View menu, click Form View to view the results.

Note that conditional formatting is applied to the UnitPrice field, but not to the ProductName field.

ShowUsing an Expression as Formatting Criteria

You can also change the appearance of a control when an expression or a VBA function returns a value of TRUE. For example, in the ConditionFormat form, you might want the appearance of the ProductName field to match the appearance of the UnitPrice field. The conditions that control the appearance of the UnitPrice field are based on the value in that field. To change the format of the ProductName field to match the format of the UnitPrice field, use expressions in the conditional formatting criteria for the ProductName field that check the value of the UnitPrice field.

To change the appearance of the ProductName field based on the value in the UnitPrice field

  1. Switch to Design view.
  2. In the Detail section, click the ProductName box .
  3. On the Format menu, click Conditional Formatting.
  4. Under Condition 1, click Expression Is in the first box, and type [UnitPrice] between 25 and 50 in the second box.
  5. Click the arrow next to the Fill/Back Color button, click the yellow box, and then click Add.
  6. Under Condition 2, click Expression Is in the first box, and type [UnitPrice] > 50 in the second box.
  7. Click the arrow next to the Fill/Back Color button, click the red box, and then click OK.
  8. On the View menu, click Form View to view the results.

The yellow and red formatting now fills both the UnitPrice field and the ProductName fields.

For more information about expressions, type create an expression in the Office Assistant, or click Search on the Answer Wizard tab in the Help window.