databasedev.co.uk - database solutions and downloads for microsoft access

Conditionally Formatting Microsoft Access Form Data

Most Microsoft Access database applications will contain user forms, both for data entry and for viewing the data. These database forms are what most of your users will see and work with every day.

Whilst most of the form design is intended to allow the user to enter the correct information into the correct database tables, sometimes, you may wish to draw your user's attention to a particular control or value on the form. This article will address this topic, and will deal with changing the format of a form control based upon a condition that you set.

As database users can make mistakes, and this number of errors can increase when the user is distracted or hurried, anything that you as the form designer can do to reduce the errors or draw the users attention to important information will help.

Changing the display of a form, based upon the data that the user enters allows you to provide immediate feedback to the user about the data that they just entered. An example of this would be if we changed the associated text to Red in a field to indicate that the data entered was unacceptable for that field.

Conditional Formatting - an Access tool that enables you to change the formatting of a control based upon conditions that you have defined. Microsoft Access allows you to set up to three conditions. You can use conditional formatting in Microsoft Access forms and reports.

How To Change The Display Of Data Conditionally - Apply Conditional Formatting to a Control

1. Select the Control
2. Choose Format » Conditional Formatting
   
   
3. Enter the condition(s) under which the formatting is to be applied
4. Select the formatting that you want to apply
5. Click OK

Suppose we have a Microsoft Access form that displays information to Products In Stock and Reorder Levels for these Products. It would be beneficial to highlight any records where the Units In Stock had fallen below the Reorder Level for that item.

We can do this be using an expression as the condition in the conditional formatting dialog box.

Below we can see our Product form, that details the Products in Stock and their ReOrder Levels:

We would like to highlight when a Product Reorder Level is greater than the amount of Units In Stock (meaning that we need to Reorder that Product. To do this we need to:

1. In form design view, select the Reorder Level Control
   
   
2. Choose Format » Conditional Formatting
3. For Condition 1, select "Expression Is" and enter the expression. In this example, we use [intReorderLevel]>[intUnitsInStock], which is checking whether the Reorder Level is greater than the amount of Units In Stock
4. Choose the format to apply. In our example, we have applied a Red backcolour to the text box
   
   
   The Conditional Formatting applied to the form.

When we view the form data, and a record meets the above condition, we can see the formatting applied as shown below:

Showing the formatting when a record meets the conditions