Access Database Highlight the Current Record Continuous Form

Download

Download Access database (accdb) example with form that shows the active record highlighted in yellow.

Download Highlight-Current-Record_Conditional-Formatting.zip
(112K unzips to 830 kb ACCDB Access database)

Be sure to enable macros, and save the accdb file from the zip as its own file. You can't make any changes inside a zip file.

Alternately, you can download this text file with the code behind the form:
CodeBehindForm_Highlight__CLS.zip

Overview

Make it easier to see the current record on your Microsoft Access forms! To highlight the current record with a yellow background color, use Conditional Formatting, a control to keep track of the primary key value, a control to change color, and a little VBA. See how it works in this video:

While designing, I like to increase the height of the detail section, create a shorter txtHighlight control below everything else on the ruler, set all the properties, then move txt Highlight up, close the height of the detail section, and then set the Height of txtHighlight to whatever the section turns out to be.

Once the controls are created, write VBA code and set Conditional Formatting.

MyPrimaryKey is the name of your primary key control such as ProductID, OrderID, ContactID, etc. The expression in a Conditional Formatting rule can refer to control names or field names.

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 textboxes and set the rule.

To specify code for an event

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

Steps

1. Go to the Design View of the form
2. Select all controls in the Detail section that you want the highlight to appear behind, and set:
   • Back Style = Transparent
3. expand detail section to be taller to give yourself some working space
   • Grab bottom of detail section border and drag down
4. Create an unbound textbox control in Detail section, below other controls on the ruler (for now), to highlight the active record:
   • Name = txtHighlight
   • Left = 0
   • Enabled = No
   • Locked = Yes
   • TabStop = No
   • Width = width of form
     -------------- set these later -------------
   • Top = 0
   • Height = height of section
5. send txtHighlight control to the back
   • right-click on it, choose: Position > Send to Back
6. set Top of txtHighlight, close space, and set Height
   • Top = 0 -- and then close space in Detail section
   • Height = height of section
7. Create an unbound textbox control in the form footer to keep track of the primary key.
   • Name = CurrentID
8. 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.
9. 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              

10. Compile the code. Save.
11. 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
12. Save form and test!

Video

Click HERE to watch video on YouTube

Do you like this? Support developers who freely share ...

chestersionerve.blogspot.com

Source: http://www.msaccessgurus.com/VBA/Database/Highlight.htm

Share this post