Microsoft Access: How do I create a drop down list that stores the index value, but displays text value?

Question

For example:

I have my primary table called Projects with headers as follows:

Projects ID, Project Name, Project Leader ID

And I have a second table called Project Leaders which stores Project
Leader ID as the primary key with headers as follows:

Project Leader ID, Project Leader Name

I am trying to create a Project Entry Form that displays and has a
drop down of the Project Leader Names, but stores the Project Leader
ID in the Projects Table.

Thanks for your help!

Answer 1

You do this by creating a combo box (a drop-down) on the form.

Right-click the box and go to Properties.

In the Data tab:
- Set the control source to Project Leader ID.
- Make sure Row Source Type is set to Table/Query (should be set that way by default.)
- In the Row Source, select the Project Leaders table, then click the little "..." button that shows up to the right of the properties box. This will open the query design builder.
- Double-click (in the table, or however you prefer to add fields in queries) on the Project Leader ID field, and the Project Leader Name field (in that order - make sure that Project Leader ID is first. You probably also want to sort by Project Leader Name. When you're done, close the query. When it asks if you want to save, say Yes.
- Make sure that Bound Column is set to 1 (this will bind the combo box using the first field in the query, which is Project Leader ID).

In the Format tab:
- Set the Column Count to 2.
- Set the Column Widths to 0";1". (this will hide the first column (Project Leader ID) and show the second column (Project Leader Name)).

That's it. In a very short time, you will find that it's become second nature. :)