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

Conditional Formatting

Conditional Formatting and Continuous Forms in Microsoft Access:

There are times whilst working in a Microsoft Access form when you would like to indicate, by the use of background colour, that the textbox that the user is in is the currently active textbox.

Now imagine the following scenario -

I have a form with a view setting of continuous (as opposed to Single form view).

What I want to do is to have the text box in that record change colour when the user clicks on that box. I put the following code in the Click event for the control named txtLength:

Private Sub txtLength_Click()
'When control is clicked, change
'back colour to red
    txtLength.BackColor = 255
End Sub

but as you probably know, it changes all the records to red, not just the record which was clicked, as shown below:

Formatting a field, using code, in a continuous form.
Formatting a field, using code, in a continuous form.

This changes all records as in a continuous Microsoft Access form all of the controls in a column have the same name. This doesn't appear to happen in a single view form since you see only one record at a time.

What additional code would I need in order for it to only change the colour of the currently selected record?

Well, if using Microsoft Access 2000, Microsoft Access 2002 (XP) or Microsoft Access 2003 you can use Conditional Formatting to achieve the desired results.

Select the control whose colour you want to change then choose:

Format » Condition1, choose "When Field has Focus", then format the font, backcolour and forecolour to what you want.

Now you can see from the image below, that the control in the continuous Microsoft Access 2000 form, contains only a change in the back colour of the memo field when the field has focus.

Using conditional formatting options in Microsoft Access 2000
Using conditional formatting options in Microsoft Access 2000

Why not check out the following for much more information on using VBA in Microsoft Access: