Special Edition Using Microsoft Office Access 2003

In the Real World Access Combo and List Boxes

Access's bound combo and list boxes offer many advantages over corresponding native controls available to Visual Basic programmers. Automatic multi-field capability in both combo and list boxes, and semi-formatted columns in Access list boxes are just two of the advantages of the Access version. When you migrate to ADP, another advantage of Access combo and list boxes becomes evident. As the frmDrillDown and frmDrillDownAll forms of the sample VBACombo.adp file demonstrate, you can populate combo and list boxes from Transact-SQL statements sent to the server as an alternative to creating SQL Server views.

The downside of sending SQL statements to SQL Server is that performance suffers because SQL Server optimizes and compiles the query before execution. Sending long SQL statements to a remote server also contributes to list-box latency. Compare the time required to first populate the Line Items list box using a connection to MSDE's NorthwindCS database on your PC to that for Jet's Northwind; Jet is significantly faster. (The performance difference is accentuated on a slower PC or one having 256MB or less RAM.) After you run the query once, however, the performance difference is minimal because SQL Server stores the compiled version in memory. When you re-execute the query, SQL Server 2000 checks to see whether it's in memory; if so, it executes the copy without recompilation. If you alter WHERE clause criteria, SQL Server must recompile the query on each execution.

Views and stored procedures optimize combo and list box performance by eliminating the initial optimization and compilation step. The view-based examples of this chapter minimize recompilation time by specifying only WHERE criteria and the ORDER BY clause for each query. If your query is complex and especially if it requires multiple joins between large tables substitute a parameterized stored procedure to return the Recordset that populates list and combo boxes. Stored procedures return read-only Recordsets that have forward-only (Microsoft calls them firehose) cursors, which provide better performance than the default dynamic cursor.

Access multi-column list boxes still have a few warts that need attention in future versions. For instance, you can't specify the alignment of individual columns; numeric values (including currency) and dates should right-align. A long-standing complaint of Access 97 developers was the lack of the simple syntax for adding items to VBA combo boxes the AddItem method. The callback method of programmatically adding items to Access combo and list boxes was gruesome, and writing code to generate a value list was almost as bad. Access 2003 combo and list boxes have an AddItem method. Developers also complained about the 2KB limit on the length of the SQL statement used as the RowSource property of combo and list boxes, as well as the RecordSource property of forms in Access 2000 and earlier. The maximum length of Access 2003's RowSource and RecordSource property values is 64KB (32KB Unicode characters).

Regardless of their column format shortcomings, Access's native combo and list boxes are effective tools for both decision support and online transaction processing front ends. Consider replacing all read-only subforms with multicolumn list boxes, even if doing so requires some extra VBA code. Your customers the users of your application will appreciate their speedy response and space-saving format. You also gain the respect of DBAs when you substitute views and stored procedures for ad hoc queries against production databases.