Highlight the Current Record on an Access form with a Yellow background color
Posted on
9,575 Points
9:11
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
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 to do it.
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, create VBA code and set Conditional Formatting.
In the steps below, 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 multiple bound controls, and then 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.
To select the form object, click in the upper left where the rulers intersect, or choose "Form" from the dropdown at the top of the property sheet.
Thanks for watching, and using Microsoft Access!
have an awesome day,
crystal
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, create VBA code and set Conditional Formatting.
In the steps below, 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 multiple bound controls, and then 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.
To select the form object, click in the upper left where the rulers intersect, or choose "Form" from the dropdown at the top of the property sheet.
Thanks for watching, and using Microsoft Access!
have an awesome day,
crystal
Video Steps
1. Go to the Design View of the form
2. Select all controls in the Detail section that you want to highlight, and set:
Back Style = Transparent
3. Expand the detail section so it is taller to give yourself some working space
Grab bottom of detail section border and drag down when mouse shape changes to a resizing arrow. You can also set Height on the property sheet.
4. Create an unbound textbox control in Detail section to highlight the active record:
Name = txtHighlight
Left = 0
Enabled = No
Locked = Yes
TabStop = No
Width = width of form
--------------------------
Top = 0
Height = height of section
5. send txtHighlight control to the back
right-click, choose: Position > Send to Back
6. set Top of txtHighlight, close space, and set Height
Top = 0
close space in Detail section by dragging bottom border up
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, such as for a new record, set CurrentID to be 0 (zero).
Private Sub Form_Current()
With Me
.CurrentID = Nz(.MyPrimaryKey, 0)
End With
End Sub
If PK is text, not a number, then use "" (zero-length string or ZLS) for the second argument of NZ
9. on the GotFocus event for txtHighlight, make the active control go somewhere else so the big control with color doesn't come to the front and cover everything
Private Sub txtHighlight_GotFocus()
Me.otherControlName.SetFoc
End Sub
If setting conditional formatting for a Datasheet form (as opposed to Continuous or Multiple Items form), you will set the same rule on multiple controls because there cannot be a txtHighlight control. In that case, this is not necessary.
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!
13. Get VBA Code:
Download example database from:
http://www.msAccessGurus.com/VBA/Database/Highlight.htm
or get this text file containing the code behind the example form:
Form_f_Addresses_180930_EE__CLS.zip
0 Comments
Suggested Videos
Azure OpenAI service, a cloud-based service offered by Microsoft, has recently been made generally available to the public, providing developers with access to large advanced AI models.
- Microsoft
- Exchange
- Microsoft 365
- Cloud Services
- Active Directory
- Azure, Robotics, *Azure AD
In May 2023, I attended the Microsoft 365 Conference as part of the Experts Exchange Reconnect program. This article is the first in a series where I will share my key takeaways and insights from this exciting event.
