Build Your Skills: Create connection-sensitive combo boxes in Access

You may be a whiz at writing single-user Access database applications, but you’ll probably find that an application written for use on a local system falls down once you move it to a client/server environment. For example, a simple combo box that displays a reasonably sized list performs well on a local system with one user. Transfer that same application to a network with a hundred or so users, or use the control to start retrieving thousands of rows of data across the network, and the application may grind to a near halt. That’s because the users that access the form force large and repetitive downloads across the network every time they use the control.

The issue isn’t just users, but also the amount of data the control retrieves. As users add data to your tables, things begin to slow down—if you’re not prepared for it. We’ll show you a simple procedure that will keep your combo box controls responding almost instantaneously, whether you have 10 or 50,000 items in the list. We’ll use Access 2002 in our examples, but the technique will also work in Access 2000 or SQL Server 2000.

The single-user control
First, let’s create a typical combo box control in a single user application. We’ll use the Customers form in the Northwind sample database that comes with Access. Specifically, we’ll add a filtering combo box to the form’s header. To do so:

1. ·        Open the Customers form in Design view and save it as frmCustomersSingle (that way you’re not modifying the actual object).
2. ·        Delete the Customers label in the form’s header.
3. ·        Replace the label with a combo box. Refer to Table A for the new control’s properties.
4. ·        Add the After Update event procedure in Listing A to the form’s module.

Table A

Control properties

Your form should resemble the one in Figure A. The combo box displays all the customers in the Customers table. When you choose a customer from the list, the control’s After Update event procedure updates the contents of the form to display only those records that match the customer you select.

Figure A

Add a combo box to find specific customers.

There’s nothing wrong with this method, as long as you’re working on a local system. The control should perform well displaying thousands of items—even though you probably wouldn’t use a list control to display that many items, without filtering the records that make it to the list. A list box with thousands of items isn’t very useful, even if it does load quickly. So, you might want to use the next technique, even on a local system.

By default, the combo box will select the first item in the list that matches the user input, but it will continue to display all the other items as well. Our technique displays only matching items.

A scalable control
The Northwind Customers table has only 91 records to retrieve and will probably perform well. But your users won’t appreciate the time it takes to load the same combo box once it’s retrieving thousands of records. In addition, the above control could actually degrade performance throughout the entire network.

You’re going to need a strategy that significantly reduces the amount of retrieved data. To that end, we’ll create a control that doesn’t retrieve any data at all until the user enters three characters. Then, the control retrieves only those records that match the user’s input. The control continues to filter the retrieved items as the user enters more characters.

Other than the amount of data it retrieves as the user enters characters, there’s no difference between the previous control and the one we’re about to build. This second control will retrieve data from a table named Customers, but this time that table will contain nearly 40,000 records. You can base your control on any table you like. Just be sure to update your field and table references in the SQL statement components. To build the filtering control:

1. ·        Open a blank form and add a combo box. Name the control cboFinder.
2. ·        Set the Column Count property to 2.
3. ·        Set the Column Widths property to 0”.
4. ·        Click Code on the Form Design toolbar to open the form’s module and enter the event procedure shown in Listing B.

After declaring a few variables, the procedure checks the current entry’s length. When the entry contains more than two characters—meaning the user has entered at least three characters—the event retrieves the CustomerID and Name values where the appropriate number of characters in the Name entry matches the current entry. In other words, the revised control contains no list at all until the user enters at least three characters. Then, the control displays all company names that begin with those three letters (or more, depending on the number of characters in the entry). This filtering process dramatically reduces the number of records retrieved from the data source—a critical issue if that data source is on a server and not local.

Using the scalable control
Use the control as you would any other combo box. Start entering characters and watch the list update its contents accordingly. For instance, using our example data source, entering “Fen” narrows the almost 40,000 possible list items to the list shown in Figure B. Only 19 entries begin with the letters “Fen” (not all are shown). Pulling over 19 records is certainly more efficient than pulling over almost 40,000. Entering a second “n” (Fenn) further reduces the number of retrieved records to three.

Figure B

Nineteen items begin with the characters “Fen.”

Regardless of the size of your data source, there’s no reason to retrieve more records than you need. Even if you’re working with just one user, you’d hardly consider filling a list with thousands of records. Typically, a three-character filter reduces the retrieved data by 99 percent. In addition, the combo box is lightning-quick whether you have one or many users and regardless of the number of records in the data source.