make Life Easier - Self Updating Combo Boxes
For many of us summer represents vacation time. Wouldn't life be
grand if we think something and magically it happens? If we could do
that, it would be vacation-time year round.
Well, unfortunately technology doesn't help us that much yet, but
there are many aspects of Access that can make our lives a whole lot
easier. Self Updating combo boxes are one of those things that bring
us closer to getting work done with little effort.
|The Magic of Self Updating Combo
Combo boxes (some people call them dropdowns) make
life easier for both Access users and Application Developers.
These clever controls allow you to select from a predefined
list. They help with data consistency since they can be
configured to prevent entry of items that are not on the list.
With combo boxes you typically take one of two paths. The
first, and most common, is that the item must be on the list
to be valid. This technique we'll explore in a future
This month we'll consider the second path: a list that's
not necessarily known in the beginning, may constantly change,
and a list that users must be able to add to. It's common for
combo boxes to go to a lookup table of valid values. In this
self-updating technique we'll look at the actual data table to
determine valid values, and we'll make the combo box always
return the contents of the current list.
You'll want to use this technique only when you store the
value that the user sees and selects - a true lookup list. If
you're storing a key (typically hidden in column one of the
combo box) then this is not the approach you'll want to take.
Let's use customers and their cities for our example. At
the start of a project, let's say that there is a defined list
of cities. As new customers come along we need to expand the
list to include new cities. If we use a standard lookup table
then someone (or some code) must add to that list as needed.
The technique I'll explain here is how we can have the list
constantly update itself. Instead of using a lookup list and
separate lookup table, we'll go to the customer table itself
and select from there.
To follow along I've included a sample file that you can
pick up at the link below. It's an Access 2000 zipped file.
Download, unzip, and then open the file.
Updating Combo Box Demo »
|Behind the Scenes|
In the database there's one table (tblCustomer) and
one form (frmDemoComboBox). If you open the form and click on
the city combo box, you'll see that it lists only those cities
that are in the city field of the Customer table. If you open
the form in design view, you'll see that the combo box's row
source points to the city field of tblCustomer. Only those
cities which are in the customer table show up. To prevent
duplicates I've set the unique values parameter (that's
distinct to the SQL folks out there) of the query to true. To
do this, start in the design view of the form, go to the
properties of the city combo box (right-click on the combo -
choose properties). Click in the row source, click on the
ellipsis (...) to the right. This will bring you to the query
behind the combo box. Right- click in the grey area of the
query design window, click on properties and then select "Yes"
next to unique values.
In the form when you open the dropdown, it lists only those
cities that are in the list. As the user types in that box, it
will auto complete and the user needs to just hit return to
select the city. If the user types something brand new it asks
if the new value is intentional, accepts it as requested, and
adds the record. The combo box then shows that new city as a
valid city on the list. To show the new city immediately it
uses only a single line of code in the form's On Current event
(this fires every time you change records or when the form is
refreshed or requeried.)
This line ensures that the combo box always presents the
latest list of cities to the user. You want to add it in the
design view of the form. Click on the grey button in the top
left corner, choose View|Properties from the menu above, click
on the On Current Event, click on the ellipsis choose code
builder, then place that line in the code.
If we stopped here we'd have a good technique; however it
would have the drawback of not entirely avoiding the perils of
misspellings. That can be handled with some code that fires on
the Before Update event of the combo box.
|The Code and How it Works|
To add the code open the form in design view, click on the
combo box, go to properties, click on the events tab, click on
the ellipsis to the right of the before update event, and
choose code builder.
Here's the code, note that in this
newsletter it doesn't wrap or indent correctly, so I've
numbered the lines so you can get the idea:
1. Dim strCity As String
2. strCity = Me.cboCity
3. If DCount("City", "tblCustomer", "city = '" &
strCity & "'") = 0 Then
4. If MsgBox(strCity & "
is not currently in the City list, would you like to add it?",
vbYesNo, "Add City?") = vbNo Then
5. Cancel = True
7. End If
8. End If
Line 2 puts the value selected or typed into the variable
strCity. On line 3 it determines whether or not the city is in
the existing list of cities, if it's not, the code asks the
user whether they want to use this city. If they see they've
made a mistake they can back out (line 5) and choose from the
You'll want to change the code of course to reflect your
form and combo box. The critical lines to change are 2 and 6
to reflect the name of your combo box, and line 3 to include
your table and field name.
This self-updating technique has the advantages of keeping
a current list of acceptable values, while allowing easy
additions to the list with a minimum of code. This technique
means that there's no further work to do once it gets rolling;
so you can relax on the beach and enjoy the rest of your
summer, maybe even take an extended vacation!
|Trap of the Month|
| Access by default will correct your spelling mistakes.
However sometimes what looks like a mistake is actually
intentional. Say you have a person with the name "Harry Thier"
and you notice that Access changes the name to "Harry Their"
To fix this you have to turn off autocorrect for that control.
To change the autocorrect open the form, go to the control, go
to properties. On the Other tab, you'll see an Allow
Autocorrect property, set this to No, and you'll be all set.
|Tip of the Month|
|If you find that your combo boxes are not sorting
properly the fix is easy. Open the form in design view. Right
click on the combo box, choose properties, click on the data
tab, choose row source, click on the elipsis. This will bring
up the query by example (QBE) grid. In this QBE set the sort
order on the field you'd like to sort by. Close and save the
grid. When you start the form again you'll see that it's now
properly sorted. This is something you'll want to do almost
all the time since it helps you and your users easily identify
the correct item. |