9/05/2012

Bind combo box to primary key but display a description field in Access

Question: In Access 2003/XP/2000/97, 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.


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

copyright www.TechOnTheNet.com
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.

copyright www.TechOnTheNet.com
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.
Finally, set the "Bound Column" property to 1. This will bind the combo box to the primary key value and not the description field.

1 comment:

  1. 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.

    Reliable Permit Solutions, LLC

    ReplyDelete