Let's say we have an application to keep track of summer campers. We'll use the same data from a summer camp that we've used in the past. Let’s walk through an unfriendly process to search the form for specific campers, starting with a typical form for this application:

We have the basic detail here, and we can get to a record we want a couple of different ways; we could click on the little triangles at the bottom of the form to advance through the 9 records in the database. Or we could click on binoculars at the top of the form (they don't show here, but in Access when the form is open as above, they're typically in the menu bar at the top of the screen.) When you click on the binoculars icon, you get the following dialog box:

The user must then type in the character string (e.g. camper last name) they’re searching for. It must match exactly, so you’ll have to spell it correctly (was that McKenzie or MacKenzie?). Moreover, additional complexity is caused by the row labeled ‘Match’ in this dialog box. This drop down selection requires the user to declare whether they want to match the entire field (whole field), the beginning of a field, or part of a field (and what do all those terms mean to the novice user, anyway?). I hope you’re getting the idea that using this method to search in the database is confusing and sets the user up for a frustrating experience.

Rather than searching using the cumbersome method described above, wouldn't it be friendlier to present your user with the following interface to search for campers:

In this scenario the user may either select the camper by clicking on a name from the drop down menu or simply start typing and the cursor will jump to the name of the camper that matches the letters typed. When the user hits return or enter, the form jumps to the right record. The user doesn't need to remember if it's McKenzie or MacKenzie; he can conveniently browse and select.

I won't go into detail about how to create the combo box or how to populate the list of selections, because those steps were covered in detail in the October 05 Wizard. However there is one additional step beyond what was covered in the October Wizard that you must implement to allow the user to find a record on the form. When you first place the combo box in the form you'll see:

Select the option "Find a record on my form based on the value I selected in my combo box”. Then continue to follow the prompts as outlined in the September and October Wizards. When you've completed this process, the combo box wizard will create code that will jump to the proper record once the user makes his selection.

If you don't see the option "Find a Record..." see this month's trap below for a work-around.

What could be slicker than a friendly combo box created with very little effort? Employing this technique of searching for records with a combo box makes life much simpler for you and your user. Rather than having to guess correct spellings and wrestle with multiple confusing drop-down menus, the user points and clicks and immediately goes to the record he wants.

This combo box that finds records is very friendly and powerful. But what's going on behind the scenes? How does it work? Next month we'll take a look at the code generated during this process that makes this magic.

Sometimes when you place a combo box on a form with the intent of using it to find a record, you get the dialog box below, which is missing the option to find a record selection.

This happens when your form is based on SQL statement within the form rather than a query or a table.

I’m a big fan of SQL statements rather than queries since a SQL statement with a form is much less likely to be changed by accident, so I run into this problem a lot.

There are two ways to deal with this problem.

First you could temporarily save the SQL statement as a query then use the query as the source for the form. When you place the combo box on the form, you’ll then see that third option "Find a record . . . " is available. If you take this road I urge you to put the SQL statement back as the record source for the form to avoid inadvertent changes in the future.

The second solution, the one that I use, is to find similar code someplace else in your application (do a search on the word clone and you'll likely find it), copy that code and adjust it so that it references the unique field on your form. Of course if you have no other combo boxes in your application that find records, this isn't very helpful; but stay tuned because next month we'll take a look at the code in question and once you've seen how easy it is to generate the code manually this problem will be one you can solve with ease.