Efficiency isn't always about performance -- sometimes, it's a about making tasks easier for end users. See how the effective design and implementation of list box and combo box controls in your Access apps can reduce effort for users and help them work more productively.
This article is also available as a PDF download.
With today's powerful systems, performance isn't the issue it once was. But just because controls respond quickly, that's no a reason to get lazy. Efficient controls aren't necessarily the fastest controls. Rather, efficient controls are easy to use and most responsive to users' needs. Apply the following tips to list box and combo box controls (except where noted) to improve productivity.
#1: Less is more
The most important thing to remember when populating controls is to retrieve only the data needed to get the job done. The more data you retrieve, the longer the process takes. In addition, the more data users have to view, the harder it is to find what they need. A busy control is as annoying as a slow one. When populating a list or combo box, minimize the number of columns and use criteria to display only the appropriate records.
#2: To hide or not to hide
The selected item isn't necessarily the data the control uses. More often than not, list items identify a record, but the control's actual value is probably the selected item's corresponding primary key value. The user never even sees the primary key values, but the control uses it just the same.
Hiding a primary key value in a list control is a good practice. While displaying it doesn't really hurt anything, unknown values confuse users. You might as well hide the value, when the value means nothing to the user (which is usually the case). To hide a column, set the column's Column Width property to 0.
There's one small drawback to hiding a column: It slows a control's performance. However, with today's powerful systems, a user isn't likely to notice the hit. So don't use performance as a reason not to hide a column. Having said that, keep hidden columns to a minimum. If you hide more than a primary key column, rethink your strategy.
#3: Turn off Auto Expand
Combo box controls automatically select the first list item that matches the characters you enter, as you enter them. (List box controls don't exhibit this behavior.) Most users find the behavior useful because just a few keystrokes return the item they need. However, every character you enter forces Access to perform a quick search to match the new keystroke, and that takes time. If performance is an issue and your users don't require this behavior, set the Auto Expand property to No.
Performance isn't likely to be a major issue, but similar items or oddly spelled items often are. A simple typo can cause Access to choose the wrong item, forcing the user to back-step a bit. In such a situation, do your users a favor and disable this property.
#4: Indexing for speed
Some list controls display more than one column. When this is the case, consider indexing the field that populates the control's first field, or rather, the control's bound column (which is usually the first column). If the bound column is a primary key, you can skip this step because a primary key has an index by default. Also, consider indexing any field that the control uses as criteria, although, in the case of list controls, that sort of setup is rare.
#5: Set a default value
Default values help reduce data entry keystrokes. Set a list control's Default Value property to the list item that users will choose the most often. Simply enter the appropriate literal value from the bound column. That way, users can skip the control when the new record's value is the same as the control's default value.
If no item is more popular than another, try setting the Default Value property to display the first item in the list using a simple expression in the form:
where listcontrol is the name of the list or combo box control. Oddly enough, a list control with a Default Value property generally performs a bit faster than one without because the control doesn't search for a Null value in the list when the control gets the focus. Users probably won't notice the performance hit though unless the list is long.
#6: Use SQL Row Source
In regard to performance, it doesn't matter whether a control's Row Source property specifies a fixed query or a SQL statement. There are other issues to consider, though, when deciding which source type to use in a list control. The most important consideration is upsizing. If upsizing your application to SQL Server is a possibility, don't use a SQL statement, because the upsizing wizard can't convert these controls.
On the other hand, fixed queries are prone to clean-up mistakes. Here's what happens: You think a query is obsolete, so you delete it. Days or even weeks later, a seldom-used control stops working because its data source (the query you deleted) is gone. Rebuilding the query is a huge waste of time. Using SQL statements eliminates this possibility.
#7: Value lists aren't bad, just limited
A value list (Row Source Type) control uses a literal list to populate its list. Value lists aren't dynamic, which means you must manually update them as list items change. That isn't a terrible problem, but it can be a nuisance, especially if you're using code to build the list.
The bigger problem is that a value list limits the number of characters the list can support. When choosing this type of list source, versions before Access 2002 can display up to 2,048 characters. Access 2002 and later are more robust, allowing 32,768 characters.
#8: Disable AutoCorrect
The AutoCorrect feature corrects typos as users make them. For instance, if a user enters abbout, Access recognizes the mistake and enters about instead. Not every entry that Access fixes is incorrect, which means the feature can quickly become a nuisance. For instance, if TEH is a legitimate entry -- perhaps it represents an item or department code -- the user must undo the fix after Access enters THE instead of TEH. (To undo an AutoCorrect change, press Ctrl+Z.)
You can disable the AutoCorrect feature for the entire application, but chances are you shouldn't. Instead, disable it for the combo box by setting its Allow AutoCorrect property to No. This will enable users to keep working without the interruption of undoing an unwanted correction. (List box controls don't support AutoCorrect.)
#9: Sorting a value list
Most lists are easy to sort. If you base the list on a query or table, add the appropriate sort details to the query or add an index to the table. When using a value list, simply enter the items in order as you create the list.
If you create a value list on the fly, sorting can be a problem, but not impossible:
- Create a one-field table with an index.
- Instead of creating a literal list of values, use SQL's INSERT INTO to insert each list item into the one-field table, as you retrieve it.
- Set the control's Row Source property to the one-field table.
You can accomplish all of this with the appropriate VBA code. Simply add it to the code that's creating the list. To avoid errors, be sure to delete the contents of the one-field table before inserting new items each time Access re-populates the list.
#10: Combine data to create meaningful list items
Most list controls display columns of data, but the column format doesn't always display the most meaningful items. For instance, a list of names becomes two or maybe even three columns of single name entries. The format isn't horrible, but you can display those names in a more familiar format using SQL. For instance, the following SQL statements would display names in last name, first name format with a comma character in a single column:
SELECT pkfield, LastName & ", " & FirstName FROM datasource
To sort the names by last name, add an ORDER BY clause as follows:
SELECT pkfield, LastName & ", " & FirstName FROM datasource ORDER BY LastName
Alternatively, you might display the names in first name, last name format, but sorted by their last name components:
SELECT pkfield, First Name & " " & LastName FROM datasource ORDER BY LastName
Concatenating data isn't limited to names. You can use this technique with any data that looks more familiar or meaningful in a single column.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at firstname.lastname@example.org.