One thing that Access doesn’t do is to highlight the current record in a continuous form,
and if you have wide forms with scroll bars users can easily become lost when they scroll across a record.
Even if you don’t have very wide records highlighting the current record can produce a nice effect.
This effect can be created by adding two controls and a few lines of VBA code.
Access 2003 and Conditional Formatting
There is a bug in Access 2003 when you have calculated controls and use conditional formatting
such as that described below, this leads to screen flicker as the calculated controls are
repeatedly updated. This problem is resolved by applying the Service Pack - SP1 or SP2.
Adding the controls
Start by creating a standard continuous form as shown below.
Then we add a text box which will act as our coloured bar, and we name the control txtRecordBar,
and most importantly we set the Control source to our primary key CustomerId.
So now when we display our form we see the following, with our txtRecordbar control
shadowing the value of the primary key.
Next add another unbound text box to our form as shown below, this can be placed in any part of the form
and will be hidden once we have completed the design work.
Setting Conditional Formatting
Access allows 3 conditional formatting statements per control, we want to use one of these to
conditionally change the colour of txtRecordBar when the value in the unbound control txtCurrentRecord matches
the current record. Set the conditional formatting to when the field value is equal to [txtCurrentRecord].
The value of txtCurrentRecord needs to be set to the current record value for CustomerId,
we do this as shown below adding three lines of code to the forms current event.
This gives the following result.
The remaining work is in improving the presentation and user interaction with the bar.
Formatting the results
The first thing to change is to hide the values for the rows other than the current row (for example the
values shown above as ALFKI, ANTON) by setting the text font to the same colour as the background.
We can see below how the values are now hidden.
We need to also change the conditional formatting to hide the value being displayed in the green selection bar. We do this by setting the font to match
the background in the conditional formatting properties of txtRecordBar.
This then gives the following presentation.
Now before we move the selection bar into position we must set the background for all the other controls on
the line to be transparent. This means that when we place the green bar behind the other controls we can see the
appropriate colour of txtRecordBar.
Next we move the selector bar into position and move it to be behind the other controls using send to back.
This then gives the following presentation.
There are two more issues which we need to look at.
Making the entire row the same colour
You will notice that the field you have clicked into, such as the CustomerId is on a white background,
if you don’t like this then you need to ensure that in the On Got Focus event for EVERY
control you call a very simple function which sets the background colour.
To keep things simple and not hard-code numbers we have set the background colour of txtCurrentRecord
to match our desired dark green background.
This gives the effect shown below and the entire row has a solid colour.
Preventing the bar getting focus
If a user clicks between controls, or in a space around a check box then the bar gets focus and we appear to have a blank line.
To solve this we make the Got Focus event on the record selector bar shift focus to another control. Add the code shown below.
The last step would be to hide the txtCurrentRecord control.
Code Download
The sample code described in this article can be downloaded using the following link.
(click here)
Top of Page
Top of Page