How to use Access' conditional formatting feature with muliple controls

Recently, I learned that Access' conditional formatting feature handles multiple controls. For instance, you can use conditional formatting to highlight an entire row in a continuous form. To do so, simply select all the controls in the form's Detail section, choose Conditional Formatting from the Format menu, and then choose the conditional formatting options. (In Access 2007, you'll find Conditioning in the Font group under the Design tab.) Unfortunately, it doesn't always work the way you expect, as I'm about to demonstrate.

The following continuous form uses conditional formatting to, seemingly, highlight the third and fifth record. In this case, I used the Expression Is item for Condition 1, entered the expression [TableID] =3 Or [TableID] = 5, and chose red from Font/Fore Color.



As a result, Access will highlight any record where the TableID value equals 3 or 5. That's not the same thing as highlighting the third and fifth record. It only appears to work, and it works only as the values in TableID match their corresponding record positions. That's just not going to happen in the real world. In addition, you can't sort, which is impractical. Look what happens when I sort by the color names in Field1:


This solution has too many inherent problems to be of value, but I can see someone thinking it works, at least until it doesn't work anymore. There's nothing wrong with the feature. The logic I used is flawed.

A more reliable, but still troubled solution is to add a record-counting field to the form's underlying query. For this solution to work, the table needs one numeric field sorted in ascending order. At the query level, add an expression in the following form:

SELECT T1.field1, T1.field2, (SELECT Count(numericfield) AS RecordCount

FROM source

WHERE source.numericfield <= T1.numericfield) AS RecordCount

FROM source AS T1

ORDER BY T1.numericfield

Neither numericfield nor RecordCount has to make it to the form, but numericfield must be in the underlying data source. This next form uses the same conditional formatting settings as the first with somewhat more reliable results because it actually formats the records by their positions. I used the same technique, but it relies on a dependable conditional value.


On the downside, you still can't sort the records by any field other than numericvalue. Look what happens if you sort by the color names:


It's an easy solution if you don't need to sort, but you should probably disable the sort options if other people use the form. I hesitate to advise anyone not to use a simple solution when it works, but document your choice well. Months from now, someone -- despite your best efforts to prevent it -- will change the sort order and the conditional formatting will cease to work as expected. This type of solution usually comes back to haunt you.

Despite the flaws in this particular example, I am excited about one thing-conditional formatting lets you select multiple controls. Now that should come in handy.