<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Merge two columns for display in a ComboBox

Published on
14,777 Points
3,777 Views
5 Endorsements
Last Modified:
Awarded
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:
two column combo with concatenated columnsThe 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

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.
combo with external display of a columnThe 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"

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.
5
Author:harfang
1 Comment

Expert Comment

by:developingprogrammer
Nice trick Markus! Thanks for sharing! = )
0

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month