How to use a combobox in Access form to update record with foreign key but display a lookup of that key?

Question

I have what I thought would be simple to do in Access.

Imagine I have the following tables (generated in SQL):

CREATE TABLE Projects (
    ID int IDENTITY(1,1) PRIMARY KEY,
    RequestStatus int FOREIGN KEY REFERENCES RequestStatus(ID),
    mName varchar(50)
);

CREATE TABLE RequestStatus (
    ID int IDENTITY(1,1) PRIMARY KEY,
    RequestStatus varchar(50) unique
);

I want to create a form in Access on the Projects table which has a dropdown allowing users to change RequestStatus - but displaying the varchar(50) values.

For example, if RequestStatus.RequestStatus values are:

• (1,"one")
• (2,"two")

I want the drop down to show the options "one" and "two" but actually, when they are selected, update my Projects.RequestStatus to be 1 or 2, respectively.

I can very easily push the "one" and "two" values into the dropdown by simply making my Combobox:

• RowSource = SELECT RequestStatus.RequestStatus FROM RequestStatus;
• ControlSource = RequestStatus.RequestStatus

But then, when I change the combo box value, it tries to insert a new value into the RequestStatus table - this is obviously a problem.

If I change the ControlSource however to be Projects.RequestStatus, it doesn't let me select using the "one" or "two" indicators as the value which is displayed is 1/2 etc.

How can I achieve this?

Answer 1

Try this for your combo box:

Control Source: Projects.RequestStatus
Row Source: SELECT ID, RequestStatus FROM RequestStatus
Bound Column: 1
Column Count: 2
Column Widths: 0";1"

That way your combo box has two columns (ID and RequestStatus), but the first column is hidden because its width is zero.