The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
Sept 2005

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.

In this issue
  • Tip of the Month – Easy Backups of Existing Objects
  • Just what is a Combo Box?
  • The Simple Way to Combo Boxes
  • Advantages & Coming Attractions
  • Trap of the Month – When to Avoid Combo Boxes

  • 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:

    1. By double-clicking on the desired item with the mouse
    2. 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.
    3. 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:

    1. Look at alternative ways to present data
    2. Change the population routine of your combo boxes so they don't load automatically
    3. Revise your form to make it "lighter"
    4. Bind sections of your form only when they need to display data
    5. 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.

    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 | - | Westford | MA | 01886