In this issue, we’ll tackle the non-trivial exercise of
adding an Add New option to the top of the drop
down list of combo box. For our example we'll use a
list populated from a table (as
opposed to a combo box that is populated with fixed
values like male/female or cash/check/credit); see
the September
05 issue for an introduction to combo boxes.
We'll start by adding the Add New option to a combo
box populated from a plain lookup table. In future
editions, we'll dig into more complex situations where
we want the Add New option to appear.
Consider a lookup list of cities and towns in
Massachusetts. As our starting point, we'll use the
following query as the source for our combo box:
When we open our Combo Box, we see the following
list:
No surprises here. What if we want to give our users
the ability to add a new city just by
selecting an Add New option from the top of the list?
How do we get Add New to show up? Well one way is
to simply add a new record to our city table and call
it "Add New". If we do this then it will surely show up
in the list. But that approach is asking for trouble,
dollars to donuts (when I was a kid that really meant
something) the Add New item will simply be an option
that could be selected from the list, so that some
records will end up with a city of Add New.
We really don't want to solve one problem by
creating another one; rather we want a true solution
where the Add New option acts in a unique way to
actually allow the user to add a new value. The
secret to the Add New feature is a union query, a
very powerful query that you can read more about in
the July
2005 issue.
Open up a regular query window in design view (from
the Query tab choose New | Design View). This will
take you to the standard design window. Access will
invite you to select tables for your query; choose
Close to dismiss that dialog box. On the menu at the
top of the screen choose View | SQL View. This is
where we'll build our Union Query.
So the first step is to construct a union query that
includes the Add New field. As our first attempt at
adding Add New to the list, we'll use this code:
Select "ADD NEW" as City from tlkpCities
union
SELECT tlkpCities.CITY
FROM tlkpCities
ORDER BY CITY;
When we run this we get:
Now this is very close, but due to alphabetical
ordering Add New is not at the top of the list as we’d
intended. The only thing we need to do is to move
the Add New to the top of the list and then we'll
have what we're looking for. The easiest way to do
this is to simply use the power of regular sorting and
force Add New to the top of the list by preceding it
with either a space or perhaps a parenthesis.
For instance, if we change our SQL statement to:
Select "(ADD NEW)" as City from tlkpCities
union
SELECT tlkpCities.CITY
FROM tlkpCities
ORDER BY CITY;
And then run it we get:
If we take that SQL statement and put it as the row
source of our combo box, we’ll have what we’re
looking for. Because a parenthesis sorts before the
letter A, the (Add New) becomes the first item on
our list. As an added bonus, the parenthesis makes it
stand out a bit more. Now if the user selects the
(Add New) element we can redirect them via code
(to be covered in a later issue) to a form that will
allow them to make a new entry in our city list.
