How to get "Lookup" functionality in Access when linking to a SQL table?

Question

I am building a SQL database which will have an Access 2010 front-end.

I would like some of the fields to be lookups in Access (ie the user clicks on the field in Access and a drop down populates). It is fairly straightforward to make a field a lookup for another table in Access but I can't seem to know how to do it in SQL and then propagate the changes.

My SQL knowledge is very basic. Here's an example of how I am creating my SQL tables:

CREATE TABLE RequestTypes (
    RequestType varchar(50) PRIMARY KEY
);
INSERT INTO RequestTypes (RequestType) VALUES ('Val 1');
INSERT INTO RequestTypes (RequestType) VALUES ('Val 2');
INSERT INTO RequestTypes (RequestType) VALUES ('Val 3');

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

I then connect to the database through the ODBC connection in Access.

How can I create my tables in SQL so that the RequestStatus field of my Projects table to have the same functionality a lookup table does? For example, being able to click on the RequestStatus attribute of a Project and select "Val 1" or "Val 2" or "Val 3" from a list. The above does require the tables to match but does not provide the "dropdown" lookup functionality.

Answer 1

Create the table in SQL Server, link to it, then use that table as the row source property for the desired combo box / drop down.

This is the very basic syntax to create a table in SQL Server:

CREATE TABLE LookupTest 
(
    ID INT NOT NULL,
    LookupValue varchar(255)
);