Highlight Current Record

Logic

Make it easier to see the current record on your Microsoft Access forms! Highlight the current record with a yellow background color (or whatever formatting),

Use a control to keep track of the primary key value (CurrentID), a control to change color (txtHighlight), Conditional Formatting and a little VBA.

While designing, increase the height of the detail section so it's easy to move things around. Create a textbox control called txtHighlight below (for now) and behind everything else. Set properties. Create a textbox control that VBA will change the value of for tracking current value.

Once the controls for showing highlight and tracking PK are created, write VBA code for Current event (at least) and set Conditional Formatting for the unbound txtHighlight.

After testing, in Form Design, move Top of txtHighlight to 0 (zero), close the height of the detail section, and then set the Height of txtHighlight to whatever the section turns out to be.

Your primary key will be the name of your primary key control and someting such as ProductID, OrderID, ContactID, etc. The expression in a Conditional Formatting rule may refer to a control or field name.

If you are using a datasheet form instead of a continuous form, you can't have a control under all the others, so set the conditional formatting rule on every control. You can then select all the controls (remember what you picked! so you can edit as a group next time too) and set the rule(s).

To specify code for an event

select the object you want such as the form or a control
go to the Event tab of the property sheet
choose [Event Procedure] or pick it from the list -- or just type "[" to pick [Event Procedure]
Click the builder button [...], or press Ctrl+F2, to go to the Visual Basic Editor
Access will automatically create the Sub declaration and End Sub Statements.
Type what you want to happen between the procedure declaration and End statements.

Steps

Go to the Design View of the form

expand detail section to be taller to give yourself some working space

Grab bottom of detail section border and drag down when the mouse pointer becomes a horizontal line with vertical double-headed arrow.
OR go to the Property Sheet for the detail section and make the Height 1 to 2 or so inches taller

Set BackColor to transparent for controls you want the highlight to show through

Select all controls in the Detail section that you want the highlight to appear behind. Perhaps drag the pointer in the vertical ruler on the left, let go, and then
shift-click out controls you don't want to set, and that don't have Back properties.
For selection, set:
1. Back Color = whatever color you want to show when control has the focus. If using black for text, perhaps a pale yellow: #FFFFC8 is RGB(255,255,200). Then:
2. Back Style = Transparent
Create an unbound textbox control in Detail section, below other controls (for now), to show highlight for the active record:
- Name = txtHighlight
- Left = 0
- Enabled = No
- Locked = Yes
- TabStop = No
- Width = width of form
- BackColor = #FFFFFF (white or whatever)
- BorderStyle = Transparent
send txtHighlight control to the back
- right-click on it, choose: Position > Send to Back
I also like to right-align the text and set the font color to orange (only shows in Design View for developers)

once this much is done, test and make sure behavior is good while you can still easily select what you want to check and change. Developers like to optionally show a bit more.
set Top of txtHighlight, close space, and set Height
- Top = 0 -- and then close space in Detail section
- Height = height of section
Create an unbound textbox control in the form footer to keep track of the primary key.
- Name = CurrentID
On the form Current event, which happens when a record changes, set CurrentID to the primary key (PK). If the PK doesn't yet have a value (new record), set it to be 0 (zero).
```
        Private Sub Form_Current()
            With Me
               .CurrentID = Nz(.MyPrimaryKey, 0)
            End With
         End Sub         
```
if PK is not numeric, change optional argument for NZ to "" for text.
on the GotFocus event for txtHighlight, make the active control go somewhere else so it doesn't come to the front and cover everything
```
        Private Sub txtHighlight_GotFocus()
            Me.otherControlName.SetFocus
        End Sub      
```
Compile the code. Save.
set Conditional Formatting for txtHighlight so the background color changes. Select txtHighlight and choose Conditional Formatting from Format tab on the ribbon.
- click New Rule
- choose: "Expression is"
- enter rule:
  Nz( [MyPrimaryKey], 0) = [CurrentID]
- set format such as yellow for background color
- OK, then OK again to dismiss conditional formatting manager
Save form and test!

VBA

code behind form

Private Sub Form_Current() 'PK is CID '181012 strive4peace, 251130, 31 'for highlighting behind other controls Me.CurrentID = Nz(Me.CID,0) 'Conditional Formatting on txtHighlight ' Expression: if ' Nz([CurrentID],0) =Nz([CID],0) ' then background is yellow End Sub Private Sub txtHighlight_Click() 'optional but nice '181012 s4p 'if highlight to indicate current record gets focus ' then move focus to another control Me.MainName.SetFocus End Sub

Color Code add-in

Highlight Current Record with Conditional Formatting and VBA in Microsoft Access

Quick Jump

Download

Video

Logic

To specify code for an event

Steps

VBA

code behind form

Conditional Formatting

Back Story

Do you like this? Give, it comes back around ...