The Access Wizard
$Account.OrganizationName
The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
March 2006

"Add New" -- The Frosting on a ComboBox

How many of you like cake? And what's the best part of the cake? The frosting! The cake itself is really good, but it's what's on top that really adds to the yumminess (is that a word?)

My wife Diane is a cake fan and sometimes I catch her eating just the frosting and leaving the cake behind, something that I'll bet a lot of kids do too.

Combo boxes, like cake, are also really enjoyable things (although I'm a big fan of combo boxes, I don't think they're really yummy – however, based on past columns you know that I use them a lot and think that they're really neat. It’s often possible to elevate a good combo box to greatness by adding some frosting---namely an “Add New” option at the top of the drop down list so that users can easily add a new item to the list. This month we'll learn how to add an Add New option to the top of a combo box.

In this issue
  • Tip of the Month - Self Updating Combo Boxes
  • The Basics
  • Conclusion and a Look Ahead
  • Trap of the Month – Avoid Clutter in your database

  • The Basics

    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.


    Conclusion and a Look Ahead

    This month we saw how to insert an Add New element at the top of a list in a combo box. We ran around the problem of sorting by placing a character (the parenthesis) before the Add New to force our item to sort to the top of the list.

    Next month we'll take this one step further and get the Add New element to the top without resorting to using a leading character. We'll explore different ways to sort lists, including some obscure but powerful techniques to bring selected elements to the top of a list.


    Trap of the Month – Avoid Clutter in your database

    Don't leave temporary tables, queries, and reports lying around. I've seen many databases with hundreds of tables, queries, and forms that the user is afraid to delete. S/he realizes that any table or query may potentially be used someplace else in the database and its elimination will result in a problem down the road.

    There's a really easy way around this issue. If you're creating a temporary table, query, or report to answer an immediate question, precede the name of the object with an underscore character "_". By using an underscore, you'll get two immediate benefits. First, if ever you come across a table, query, or any other object that starts with an underscore, you'll know immediately that it's a temporary object that you can safely delete (aren't naming conventions great!)

    Secondly, when you sort your list of tables, queries, or reports by name, your temporary objects will float to the top of the list, all nicely grouped together waiting for you to delete them.


    Tip of the Month - Self Updating Combo Boxes

    If you use a lot of combo boxes and have a frequent need to add elements to lists, take a look at the August 2004 issue – Self Updating Combo Boxes. That issue contains an explanation of how you can automatically detect that the user has entered a unique value into a combo box and then automatically add it to the underlying table. Using this method, each new value will show up immediately in the list ready for use in the next record.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
    Powered by

    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886