KB193207 - ACC: Displaying Colors Based on a Condition in Continuous Forms

Article ID: 193207

Article Last Modified on 1/23/2007

Microsoft Access 2.0 Standard Edition
Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

This article was previously published under Q193207

Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY

When you set a property of a control, that property is set for all instances of that control on the form. On a continuous form, you may want to set a property, such as foreground color, based on some condition. This article describes how you can use a second control, with a different foreground color, to overlay the first control based on a condition. The effect is that the continuous form will appear to have one control whose foreground color is set conditionally.

MORE INFORMATION

The following example uses the Order Details table from the Northwind.mdb sample database and describes how to create a continuous form that appears to use conditional formatting on the Discount control. The value of the control appears red if the discount is greater than zero percent; otherwise, it appears black. To accomplish this, two controls are used. The first (bottom) control always displays the discount in black. The second (top) control is transparent and displays a value only if the discount is greater than zero. Because the top control is on top and displays the value in red, it covers the value of the bottom control.

NOTE: Conditional formatting is limited to three conditions and a default format. You cannot add a fourth condition through the user interface nor through Visual Basic for Applications code.

Creating Conditional Format on a Continuous Form

Open the sample database Northwind.mdb (or Nwind.mdb in version 2.0).
On the Insert menu, click Form to open the New Form dialog box.
Select AutoForm: Tabular from the list of available wizards, and Order Details from the "Choose the table or query where the object's data comes from" list. Click OK.
On the File menu, click Save and save the form with the name frmTextForm.
On the View menu, click Design View. (Click Form Design in versions 2.0 and 95.)
On the View menu, click Properties.
Note the original size of the detail section, and then increase the height of the detail section. For example, set the height to 0.5".
Make a copy of the Discount control. To do so, select the Discount control. On the Edit menu, click Copy, and then on the Edit menu, click Paste. A new control should appear just below the original control.
Rename the new control to DiscountTop. To do so, select the new control, and then on the View menu, click Properties. In the property sheet for the new control, click the All tab, and then type DiscountTop in the Name property box.

Change the other properties of the DiscountTop control as follows:

   Text Box: DiscountTop
      Control Source: =Iif(Discount>0,Discount,"")
      Enabled: No
      Locked: Yes
      Back Style: Transparent
      Fore Color: 255

To ensure that the original Discount control stays on the bottom, select it, and then on the Format menu, click Send to Back.
Place the DiscountTop control on top of the Discount control. To do so, select the Discount control. Note the Top and Left properties. Then, select the DiscountTop control and set its Top and Left properties to the Top and Left properties you noted from the Discount control. For Example:
```
    Text Box: Discount
       Left: 3.125"
       Top: 0.0417"

    Text Box: DiscountTop
       Left: 3.125"
       Top: 0.0417"
					
```
Reduce the size of the detail section to its original size.
On the View menu, click Form View and inspect the results. (Click Form in versions 2.0 and 95.)

For more information about changing a control's property setting in a continuous form, please see the following article in the Microsoft Knowledge Base:

119992 ACC: Changing Control Properties Affects All Records in a Form

Additional query words: inf colour

Keywords: kbhowto KB193207