Changing the Background Color in a continuous form is an often
asked question in the Access Newsgroups. The following document shows how this can be done,
and the methodology behind it.
This procedure will highlight the current row in a continuous form.
Please see the notes at the end to learn what this procedure DOES NOT do.
This sample is based on the "Products" table from the
Northwinds Database.Changes will be noted so you may use this code in your own form.
- Create a new form based on the "Products" table.
- Add all fields from the products table to the form.
- Create the following Controls to the form
Control Source: =IIf([SelTop]=[ctlCurrentLine],"лллллллллллл",Null)
"л" is character 0219, the easiest way to enter this is
to copy and paste from here. Format the font of this control as Terminal.
Place this control on your form so that it is sized to cover the
entire area where you would like the background to be. Experiment with the number of
"л" characters as well as the font height to get complete coverage. Set the
background to transparent. Set the foreground to whatever color you want your highlight
color to be. Make sure the section background color is different from the highlight color.
Next, for all the controls that will have the background highlight,
select them all, change the background color to the highlight color, then change the
background color to transparent. (Yes, this step is necessary).
The following two controls can be placed anywhere, and be hidden.
You may want to leave them visible to help in seeing how this works, then hide them when
Add the following code behind the form:
Dim RS As Recordset
Dim F As Form
Dim KeyName As String
Set F = Form
KeyName = "productid"
KeyValue = [ProductID]
On Error GoTo Err_GetLineNumber
Set RS = F.RecordsetClone
Select Case RS.Fields(KeyName).Type
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
RS.FindFirst "[" & KeyName & "] = " & KeyValue
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
MsgBox "ERROR: Invalid key field data type!"
Do Until RS.BOF
CountLines = CountLines + 1
GetLineNumber = CountLines
CountLines = 0
Private Sub Form_Click()
Me!ctlCurrentRecord = Me.SelTop
Private Sub Form_Current()
Me!ctlCurrentRecord = Me.SelTop
How the code works:
When you open the form, the GetLineNumber function gets the record
number for each record and assigns its value to "ctlCurrentLine". As you move
from record to record, the code "Me!ctlCurrentRecord = Me.SelTop" changes the
value of "ctlcurrentRecord" to the current record number (See Access help for
more on "SelTop"). The code in "ctlBack" compares "SelTop"
to the"ctlCurrentLine" value and only changes the highlight if the two are
equal.These values will only be equal for the current record. The highlighting works by
formatting the Terminal Font character 219 (a box, you can use any font box character, but
Terminal is more likely to be on any machine) to the highlight color. Because all the
controls on the form are transparent, this formatting shows through as a background to the
Although at first glance it may look like the control
"ctlCurrentRecord" is not needed, it actually is to force the record numbers to
update as you move from record to record.The code as written will detect movement to a row
from the following: a mouse or keyboard click, clicking the record selector (through
Form_Click), and using the Record Navigation bar. If you move to rows through code, you
may need to make modifications to highlight the row.
Performance is affected by the speed of the machine. On slower
machines you will note that the individual cell highlight shows first, then the whole row
The are many things that can be done with a continuous form, such as
allowing deletions, edits, additions, etc. This code generically handles navigation
through the form only. To account for how your form is actually set up, you would need to
modify your form to detect each of the above events, and refresh accordingly. Anytime you
change the underlying records of the form, you will need to call "GetLineNumber"
(one way to do this is through Form.Refresh). Since there is a performance hit in doing
this, only use this for those operations that are allowed on your form.