Trying to limit input on a field

Question

I have a linked table in ms-access that pulls data from a mysql database. In it is a field titled 'job' that should have three possible values, 'Chairperson', 'Vice-Chair' and NULL. I have a local table I have titled jobs that has two columns, title and value.

title|value
Chairperson|Chairperson
Vice-Chair|Vice-Chair
Member|NULL

It looks like setting a lookup box display control in the design view of the linked table to should provide a solution but I cannot seem to be able to set the rows in the title field as what shows up in the dropdown and the corresponding value in the values table to be what the field is set to. Am I missing something? a translate command or something?

Answer 1

On the lookup tab do the following:

• Set the rowsource as a query containing the two columns value, title (in that order)
• Set the bound column to 1
• Set the column count to 2
• Set the column widths to 0;2" (change the 2" to whatever you need for the width of the display column)
• Set "limit To list to" yes

Answer 2

It is not recommended to use look-up fields in tables:

The Evils of Lookup Fields in Tables

Use a query to display information and a form for data entry.

Answer 3

In addition to JohnFx's implementation of showing the data, you want to fix the underlying table structure. Set up your table to look like this:

id (autonumber)    title
-------------------------
1                  Chairperson
2                  Vice-chair

Store the ID of the title in the linked table as opposed to the text of the Title field, and make it a Foreign Key. Also, don't add a record for NULL, as the principle of a Foreign Key field states that the value must exist in the table where it is a Primary Key, or it must be null.