In any case, it is helpful for the user to know at a glance where the cursor is positioned in a form without having to squint and stare at the form for a long time, or having to use the mouse to position the cursor where they think it should be. An easy way of doing this is to highlight the form field which has the focus at any given point of time. The highlight can take many different forms, but one simple way to make it stand out is to change the background color to something bright so that the field stands out to catch the user's attention.
If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, and pre-filling forms based on previous entries.
This post shows you how to change the properties of a form control based on the GotFocus() and LostFocus() events of the control. In this case, when the control gets the focus, all we will do is change the background of the control to a bright yellow so that it stands out among the controls (we will assume that the rest of the controls have the default grey or white background). When the control loses focus, it will go back to the previous background it had.
Option Compare DatabaseObviously, the control in this case is named "Control1". Hopefully, your controls have more meaningful names than that! Leaving that aside, let us look at the code carefully. In the GotFocus() and LostFocus() subroutines, we first create a variable of type Control.
Option Explicit
Private Sub Control1_GotFocus()
Dim myCtrl As Control
Set myCtrl = Me.Control1
Call HighlightControl(myCtrl)
End Sub
Private Sub Control1_LostFocus()
Dim myCtrl As Control
Set myCtrl = Me.Controls.Item("control1")
UnHighlightControl myCtrl
End Sub
Private Sub HighlightControl(ByRef ctrl As Control)
ctrl.Tag = ctrl.BackColor
ctrl.BackColor = 65535
End Sub
Private Sub UnHighlightControl(ByRef ctrl As Control)
ctrl.BackColor = ctrl.tag
End Sub
We then set the variable to Control1, and then call HighlightControl() in GotFocus(), and UnHighlightControl() in LostFocus(). Notice the two different ways to set the value of the myCtrl variable. You can either use the construct me.controlname, or the construct me.controls.item("controlname"). I have used both constructs in my code to show that there is a choice. Preferably, you should use one of the constructs in both places for consistency and ease of maintenance.
Seems simple enough. But notice that HighLightControl() and UnHighlightControl() take the control as an argument by reference. This is a very important point to note. Since we are changing the control's properties, and we want the changes to stick after we exit the subroutine, we need to pass the control by reference rather than by value (which is the default in VBA).
Why don't we just change the background color in the GotFocus() and LostFocus() subroutines themselves and save ourselves the trouble of writing separate subroutines to change the background color? That is a perfectly valid way of doing things. The only problem is that sometimes, we want to do more than just change the background color based on getting or losing focus. As the number of changes we want to make increases, it makes sense to isolate all these changes to separate subroutines rather than repeating the changes for every control in the form.
Remember that your form might have dozens of controls. Instead of a call to HighlightControl() and UnHighlightControl(), the GotFocus() and LostFocus() of each of these controls might expand to several lines each, making the code hard to read. The code also becomes harder to maintain because every time you want to make a change to the controls based on their getting or losing focus, you have to make the change individually in every GotFocus() and LostFocus() subroutine rather than in a single place (the HighlightControl() or UnHighlightControl() subroutine).
The other thing to note in the code above is what we do with the normal background color of the control when we highlight it. In this instance, I have put that previous background color in the tag property of the control. This is then used to restore that background color to the control when we unhighlight the control.
If you use the tag property for something else and don't want to overwrite it with the control's previous background color, you have to use a global variable to accomplish this. In fact, if you change more than one property, you really can not store all the previous values of these properties in the tag property, and retrieve them from there without a lot of fancy coding. So, global variables come in handy. Take a look at the following snippet of code:
You can declare global variables for as many properties of the control as you are going to be changing, then set their highlight properties in the form_open() event. Set the unhighlight properties in the highlight() subroutine to their current values, and then you can use those values to revert the control back to its original appearance when you unhighlight it.
Option Compare Database
Public highlightbackcolor As Long
Public unhighlightbackcolor As Long
Option Explicit
Private Sub Control1_GotFocus()
Dim myCtrl As Control
Set myCtrl = Me.Control1
Call HighlightControl(myCtrl)
End Sub
Private Sub Control1_LostFocus()
Dim myCtrl As Control
Set myCtrl = Me.Controls.Item("control1")
UnHighlightControl myCtrl
End Sub
Private Sub Form_Open(Cancel As Integer)
highlightbackcolor = 65535
End Sub
Private Sub HighlightControl(ByRef ctrl As Control)
unhighlightbackcolor = ctrl.BackColor
ctrl.BackColor = highlightbackcolor
End Sub
Private Sub UnHighlightControl(ByRef ctrl As Control)
ctrl.BackColor = unhighlightbackcolor
End Sub
The possibilities are endless. Experiment with it, and your users will thank you for creating forms that are easier on their eyes, and there will be fewer data entry errors, making everyone happier!
Hope this post has been helpful in solving any problems you might have had with highlighting form controls in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

I was walking through Terminal 1 of the Chicago O'Hare airport recently when a sign caught my attention. It was a giant sign that simply said "No Enter" as seen on the photograph to the left. The sign certainly made me do a double-take.
The accompanying graph showing the price of gold over the past 5 years illustrates the explosive growth in gold prices because of turmoil in economies throughout the world. During this time period, budget deficits and trade deficits have gone up causing countries to prop up their economies by trying to increase their money supplies, resulting in devaluation of currencies and a run-up in the prices of real assets such as gold.















