totn Access

MS Access 2010: Bind Combo Box to Primary Key but display a Description field

This MSAccess tutorial explains how to bind a combo box to a primary key (ie: autonumber field) but display a description field in the combo box results in Access 2010 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2010, I've set up a table that contains an autonumber field as the primary key and a description field. I want to be able to create a combo box that displays the description, but stores the primary key. How do I set up the combo box?

Answer: Quite often the primary key value can mean nothing to the user. Instead, there is a description field in the table that relays the meaning of the record. In Access you can link a combo box to the primary key, but display the description in the combo box itself.

To do this, first create a combo box object. Right-click on the combo box and select Properties from the popup menu.

Microsoft Access

Then click on the button (with the 3 dots) to the right of the "Row Source" property to bring up the Query Builder window.

Microsoft Access

In the Query Builder window, build your SQL so that the first selected field is the primary key and the second is the description field. In this example, the primary key is CategoryID and the description is CategoryName. Close the query builder window by clicking on the X in the top right corner of the Query Builder window.

You will be prompted to save the changes. Click on the "Yes" button.

Microsoft Access

You will return to the Properties window for the combo box. Set the "Column Count" property to 2. The column count property indicates that the query will return two columns in your result set.

Then set the "Column Widths" property to 0;1. This will set the width of the first column in your result set to 0, in essence hiding the value from being seen by the user. The width of the second column will be one inch.

Microsoft Access

Finally, set the "Bound Column" property to 1. This will bind the combo box to the primary key value and not the description field.

Microsoft Access