Access Cookbook

Make a Simple “Searching” List Box

Problem

You’d like to create a text box/list box combination like the one in Windows Help. As you type in the text box portion of the control, you want the list box to scroll to match whatever’s been typed so far. You know you could use a combo box for this, but the combo box keeps closing up. You want something that’s permanently open.

Solution

Entering a portion of the value they’re looking for and seeing the matches displayed as users type is an excellent way to find specific values in a list. You get the best of both worlds: the functionality of a combo box and the “permanently open” look of a list box.

The key to implementing this functionality is the text box’s Change event. Every time the text in the text box changes, the code you’ll use will automatically find the matching value in the associated list box. You’ll be able to call a function that will handle all the work for you. In addition, because searching through indexed tables is so much faster than walking through dynasets (the results of running a query or a SQL expression), this solution offers two solutions to this problem: one for list boxes that are bound to tables and another for list boxes that are bound to queries or SQL expressions. Figure 2-15 shows frmSearchFind in action.

The methods you’ll find in this solution apply only to bound list boxes.

Figure 2-15. Using Incremental ...