Search The Web

Today's Headlines

Sunday, October 31, 2010

Microsoft Access Tips & Tricks: Highlight Form Controls

When you have long forms with lots of controls to make entries in, it is easy for the user to lose track of where the cursor is, and what control they are supposed to be making an entry in. A well-designed form in which the cursor moves in an orderly fashion can ease this problem (see the post on setting tab indexes), but even well-designed forms can have some cursor movement that can be confusing. For instance, if your address is split into a street address, city and zip controls, you may want to move the cursor directly from the street address to the zip control so that you can use a lookup table to fill out the city based on the zip rather than forcing the user to make an entry in that control.

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 Database
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
Obviously, 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.

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:

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
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.

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!

1 comment:

Access Training said...

Very interesting, I have just written some Access Training tips about the new features in Microsoft Access 2010, I thought you might find this interesting and maybe be able to add a few of your own?

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web