Merge two columns for display in a ComboBox

I've known this trick for so long that I can't give proper credit any more. It might have been in the very first “Northwind Traders” database I studied, shipped with Access 2.0. Anyway, the idea is to display one thing in the edit portion of a combo box, and another in the drop-down section. Like this:
The selected supplier is displayed as “Exotic Liquids, London”, but the list shows the same in two columns. This makes the list much more readable, and reveals the fact that the sort order is in fact by city and not by supplier name. This is incidentally a second trick, allowing the user to search the list visually by city, while still being able to select a supplier by name using “auto-expand”. Typing “sp” will select the “Specialty Biscuits” even if the list isn't sorted.

A demo database is probably superfluous, but here is one anyway. ComboMergeCols.mdb

How it's done

The combo box contains in fact four columns. The first is the key column with the supplier number, which will be stored as the value of the combo and perhaps in an underlying field; it is hidden entirely. The second contains the combined supplier name and city, and is almost hidden, in that it doesn't show in the drop-down list. The last two are the same fields in separate columns.

To almost hide a column, the width is set to the minimal accepted value, which should be below one pixel. Internally, all form metrics are calculated in twips, measuring 1/1440 inches, which is thus the minimum. In recent versions of Access, one can type directly “1 twip”; in older versions the measure is given in the system units of measure: 0.0007" or 0.0018 cm.

The property sheet contains:

Row Source:         SELECT
                                            SupplierID,
                                            CompanyName & ', '+City,
                                            CompanyName,
                                            City
                                          FROM Suppliers
                                          ORDER BY City;
                      Bound Column:       1
                      Column Count:       4
                      Column Widths:      0";0.0007";;1"
                      List Rows:          16
                      List Width:         Auto
                      Auto Expand:        Yes
                      Allow AutoCorrect:  No

Select all Open in new window

Notes: One column width can be left empty, it will take the remaining space. Auto expand is still useful because the displayed column and the first really visible column start with the same words. AutoCorrect should always be off in combos.

Alternatives

When it doesn't make sense to concatenate the columns, the value from any column can be displayed next to the combo box in a text box.
The query has been reduced to three columns, and the property sheet has been modified like this:

Column Count:       3
                      Column Widths:      0";2"
                      List Rows:          4
                      List Width:         3"

Select all Open in new window

The text box has the control source: =cboSupplier.Column(2)

In this case, the result is clearly inferior for my taste. However, there are many other situations where this would be best. Also, the same trick can be used to display columns that are present in the row source but not even displayed as column in the drop-down section. Such columns can serve as a good substitute for numerous calls to DLookup() to obtain the same information.

Conclusion

The trick is simple yet very useful. Discovering it also gave me a better understanding of what “the first visible column” really means: almost hidden is still visible. The idea to sort on the second column rather than on the first one came later; it doesn't often make sense but when it does it's a real time saver.


Markus G Fischer
(°v°)

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.