It is time for me to share another pearl of wisdom, the third in the string. This concerns the highlighting of the selected (current) row in a list form. This job is already done by the Access Record Selector but perhaps you want something more colourful, something with a little more impact. Well here is a way to achieve this but it does require some VBA programming using the FormatCondition object; I don’t think this solution can be implemented from the form design, but perhaps some of you know different.

In summary the solution is to set the BackColor of an empty text box to a colour different from the BackColor of the form but only for the current row. The text box normally covers the entire area of the row and is the deepest control: that is, it is ‘sent to back’. Other controls in the row normally have their BackStyle set to ‘Transparent’ such that the colour of the text box shows through. Once you have understood the method you can ring the changes to produce all manner of effects. Oh, a prerequisite: each row has to contain a unique key, even if this is not visible to the user.

OK, here is the method in detail. In form design add a text box to the row detail which for the purpose of this example is named ‘txtHighlight’. Delete the label and set the text box properties such that it is locked, disabled, has transparent lines, is of flat effect, and is not a tab stop. Finally adjust the size of the text box to cover the whole row area, send it to the back and move it ‘underneath’ the other controls in the row.

Optionally set the BackStyle property of each visible control in the line to ‘Transparent’.

In the event listener for the form’s OnCurrent event include the following.

Me.Controls("txtHighlight").FormatConditions.Delet e
Me.Controls("txtHighlight").FormatConditions.Add acExpression, , "[id] = " & Me![id]
Me.Controls("txtHighlight").FormatConditions(0).Ba ckColor = vbWhite

The first line is necessary since format conditions are persistent and there is a limit to the number allowed. Without the delete this number is soon exceeded. Also it ensures that the condition being added is the first in the collection so it can be referred to as (0).

The second line adds a new condition to the (now empty) collection. The control named ‘id’ is the control that holds the unique key. The actual expression shown assumes that the value in the control is numeric. If the unique key is alpha then the expression is written “[id] = ‘” & Me![id] & “’”

The third line sets the back colour of the text box to white when the expression is true; that is, for the current row and only the current row if the value of ‘id’ is unique.

There are a couple of caveats. I notice in some of my code that I have added an additional line that disables the text box even though it was disabled in the design.

Me.Controls("txtHighlight").FormatConditions(0).En abled = False

It seems that the format conditions resets some of the controls properties; I know from other situations that if the text box contained bold text the format condition would reset it to normal.

The other caveat is that different implementations of Access have different default background colours for forms. So if you are porting an application, txtHighlight may not match the forms' background colour. Unfortunately BackStyle is not among the properties you can set with format conditions otherwise the solution would be simple. However you could always test to see what the background colour is and set txtHighlight to match.