I came across this code in Access Hacks by Ken Bluttman (O’Reilly, 2005, pg 83) and decided to experiment with it. The concept is quite simple: two short subroutines to define what to do to a field when it is entered and exited using With Me.ActiveControl to set numerous parameters, followed by an _Enter and _Exit sub for every control that you want so manipulated. Also note that if the Special Effects of the text box is other than Flat, it might not work correctly.
You’ll notice the IF in the _Enter and _Exit subs. I prefix the names of non-text fields with ck for checkboxes and cbo for combo boxes. In the case of the checkbox, they don’t support the FontItalic and FontWeight options, so I skip them. There may be other controls that I don’t use that have similar limitations.
Sub Active_Control_Enter() With Me.ActiveControl .Properties("BorderWidth") = 2 .Properties("BorderColor") = vbRed If Left(Me.ActiveControl.Name, 2) <> "ck" Then .Properties("FontItalic") = 1 .Properties("FontWeight") = 700 'bold End If End With End Sub
Sub Active_Control_Exit() With Me.ActiveControl .Properties("BorderWidth") = 0 .Properties("BorderColor") = vbBlack If Left(Me.ActiveControl.Name, 2) <> "ck" Then .Properties("FontItalic") = 0 .Properties("FontWeight") = 400 'normal End If End With End Sub
Private Sub APHGrade_Enter() Active_Control_Enter End Sub
Private Sub APHGrade_Exit(cancel As Integer) Active_Control_Exit End Sub
Assuming that you’re dealing with linked tables, you can use the T-SQL code that I wrote a few months ago called GetTableFieldNames to get a list of all of your table’s field names, it would be pretty simple to modify the code to produce all of the _Enter and _Exit subs.