Combo Boxes 101 – a Powerful Tool in a Small Package
A while back a friend of mine showed me his new toy.
It was a knife with the standard things like a big
blade, a small blade, and a screwdriver. But this was
no ordinary knife; it had features like a hack saw
blade, a blade to cut down small trees, and even a
pair of pliers. This modern day update to the Swiss
Army knife is called a Leatherman. Although a
Leatherman knife is small, there's a lot of utility
packed into a small frame.
This month we'll begin a series on at a tool in Access
that also carries a big punch within a small package –
the Combo Box.
Just what is a Combo Box? |
 |
A Combo boxes is a tool that allows an Access
Developer to present a predefined list of choices to a
user. The user selects one, and only one, choice
from the list. Unless you decide to let him/her type in
an alternate choice, you can require that the user
select only from the list.
You've probably seen many combo boxes on
electronic forms; they look like this:
The combo box is usually labeled with instructions
like "Make your Selection," as seen above. The
typical combo box includes an area or “drop down
box” where the user can choose an item from a list.
If the user clicks on the downward facing black
triangle (arrow), the combo box will expand to display
a list of possible choices. Note that the list of items
may expand beyond the area of the box, so it may
be necessary to scroll down to see the complete list.
As you can see above, this combo box displays the
first eight items on the list. The light blue box
between the arrows to the right of the list indicates
that we are at the beginning of the list. The user
must move down the list (by clicking on the
downward pointing arrow or dragging the blue box
down between the arrows) to view the remaining
items on the list. The user may select an item from
the list in a number of ways:
- By double-clicking on the desired item with
the mouse
- By typing the first letter of an item. For
example, if the user wants to move down the list to
items that start with the letter “s”, she could type
an s and then select from the values in the window
by double-clicking on the desired item with the
mouse or hitting return or enter when the value is
selected.
- By typing a series of letters (for example,
typing “ag” would make the city “Agawam” pop into
the box) and hitting return or enter to complete the
selection.
Once an item is selected to populate the box, the
combo box collapses back to a single record with the
highlighted record selected.
When the user starts typing, the combo box will
begin a process of auto-completing the selection. For
instance, if your combo box had an alphabetized list
of all the cities and towns in Massachusetts, and you
typed ”w”, it would immediately guess Wakefield (the
first city starting with a “w”).If you added an “i” to
the “w”, it would guess Wilbraham, the first city
starting with “wi”. If you were trying to get to the
town of Winchendon (a very pretty town in Central
Massachusetts where I taught High School), you
would have to type just one more character, an “n”.
This is very powerful stuff. There is something on the
order of 250-300 cities and towns in Massachusetts.
If you include all the sub towns, villages, heights,
springs and the like, there are almost 500. And here
we got to the one we wanted with just three
keystrokes! Imagine how painful it would be if you
had to scroll though that long list to get to the town
you wanted!
|
The Simple Way to Combo Boxes |
 |
The easiest way to get a combo box onto a form is
to use a Wizard. Start with a blank form in design
view, and then find the toolbox. It looks like the
picture below. It may be at the top or side of your
screen or in a free-floating toolbar. If you don't see
it at all, you may have to choose View | Toolbox
from the menu at the top of the screen.
Once you have the toolbox in front of you, hover
over the icon highlighted above and you'll see the
word Combo Box as in the illustration. Drag and drop
this onto your form. On your form you'll notice the
cursor changes to a crosshair. Left click and draw a
rectangle the size you'd like your combo box to be.
Don't worry too much about precision at this point
since you'll be able to resize and move the box as
you need to.
When you release the mouse button you'll get the
dialog box below:
This dialog box gives you two different options to
populate the combo box. The first, "Look up values in
a table or query," allows you to select a table (or
query) as the source for your combo box. This is a
good choice if you have more than a handful of items
that you'd like your user to choose from, or if you
would like the selections to be dynamic (that is to
have the list change as values in the source table
change). If you choose this option, you'll be given a
list of tables and queries to choose from. Simply
select the one you want, then you'll be asked for the
fields to show in the combo box (more fields mean
that you can give your users more information to
help them make their selection). Then you'll be asked
to select a field (or fields) to sort by. The next step
will ask you to hide key column (this is typically your
primary key. If your primary key is an auto-number
(and it should be-see June
04), and then accept the default to hide the
key column. Select Next and choose a label for your
combo box. Finally, click Finish and you'll have the
combo box on your form ready for use.
The second option, "Type in Values," allows you to
enter a set of static values. This option makes sense
when you have only a few standard items to choose
from that are not necessarily stored in a table.
Gender (male or female) is a good example of this. If
you choose this option, you'll be asked how many
columns to show. Typically, you would want to show
one column. Then the wizard allows you to fill in the
values you would like to include and asks you to label
your combo box. Fill in the box, click Finish, and your
combo box is ready to use.
The methods described above are the easiest ways
to build a bare bones combo box. All the choices
you've made along the way can be revised at this
point, and we'll see how to make those changes next
month when we'll dive further into the various
options and features of combo boxes.
|
Advantages & Coming Attractions |
 |
The big advantage of a combo box is that it can
restrict users to selecting values from a predefined
list. This can help big-time if you have the potential
for different spellings for items on your list. It brings
more consistency to your data, which in turn helps
with analysis and reporting when it comes time to
get information out of your database application.
We've barely scratched the surface of Combo Boxes.
In the next few months we'll dive deeply into their
guts (do combo boxes have guts?)
and learn to tap their power to make life a whole lot
easier for you and your users.
|
Trap of the Month – When to Avoid Combo Boxes |
 |
When you first start using combo boxes, you may be
tempted to use them for every situation where you
want to restrict your users to a list of predefined
values. In general this is a good idea; however this
approach may backfire. Incorporating too many
combo boxes in any given form may noticeably slow
down performance. Each combo box on a form may
have
to go after a table in the database or run a query to
get data. When a form opens it will run the
necessary routines to populate each combo box on
your form. If you find that you have a form with
many combo boxes and it opens slowly, start
eliminating combo boxes from the form (make sure
you back it up first -see this month's Tip for an easy
way to back up a form) and rerun your form. If you
find that things speed up without the combo boxes,
then you have many alternatives, which we’ll revisit
in future issues of The Access Wizard:
These include:
- Look at alternative ways to present data
- Change the population routine of your combo
boxes so they don't load automatically
- Revise your form to make it "lighter"
- Bind sections of your form only when they need
to display data
- Unbind your form
None of these suggestions are trivial and some of
them involve rather complex solutions to what can be
a very tough problem. Again, we’ll address these
techniques in future months.
|
|
Tip of the Month – Easy Backups of Existing Objects |
|
I'm sure all of you make regular backups of your files,
both data files and Access databases. If you haven't
backed up recently, give yourself 20 lashes (for the
masochists out there, deny yourself 20 lashes) and
backup today, for all hard drives will fail at some
point.
When you're testing something in Access and want
the ability to revert back to the original object even
after changes are made, you don't really need a full
backup of the database. In this case, you may want
make a backup of a single object, (e.g. table, form,
report, or query), which is easy to do.
Let's say you have a form called MySlowForm that
you want to experiment with -- see this month's trap
as to why you might want to do this. Before you
begin to change things, select the form (left click
once from the database window), choose, Edit |
Copy from the menu at the top of the screen, then
choose Edit | Paste. It will invite you to give the
copy of the form a new name (I usually use the
original name and append the word old to the
filename – that way it will be easy to find and
identify if I need it and I know it's OK to delete it
later on). Select OK and rest comfortably knowing
that a copy of your original form (or object) has been
preserved.
At this point you can go ahead and start
experimenting. If your testing does not work out, you
can just delete the form and rename your backup to
the original name. If you're satisfied with the testing
you may move ahead with your work. Just remember
to eventually delete your back up copy of the object
so you can reduce your database bloat.
|
|