Multi Column Lookup

Question

I want to do a lookup in a Access table. I want the lookup to select from the left most column in the table it's getting the data from but to also display the next column over (i.e. if I'm selecting a company code, display the company name when the lookup drop down is activated. Any way to do this?

Answer 1

As dbGuy said, lookups should be done using list controls on a form, NOT at the table level (see Evils of Lookup fields).

The combobox wizard, by default, hides the key column from the Rowsource and displays the next column. So given the following Rowsource:

SELECT CompanyCode, CompanyName FROM tblCompany ORDER BY CompanyName;

The other relevant properties would be:

BoundColumn: 1

ColumnCount: 2

ColumnWidths: 0";2"

The Column widths property is the key here. Access will display the first non-zero width field. So the combo list will display just the company name not the code.

If you want to display other attributes of a company on your form see my blog on Displaying Data from Related Tables for techniques to do so.

Answer 2

Hi. I hope you're talking about doing this on a form. When you click on the dropdown, all visible columns should show up. But once you make a selection, only the first visible column will be displayed in the box. To show the other columns, you'll need a separate textbox for each one or "concatenate" the values you want displayed into one column. To use additional boxes, they must be unbound and refer to the Column() property of the combobox. For example:

=ComboboxName.Column(x)

x = column index starting with zero (0)

Hope it helps...