Updating Records in Access Database

You confused me so I'll tell you how I create a "Look-up" field (or combo box).

First I create a query and test it until it delivers EXACTLY what I want. Then I save it and copy its SQL View (SELECT code) into the clipboard ([Ctrl]+C).

I thenopen the Form where my look-upcontrol will be and create and name a combo box set to disallow user entry (or require the user select from my list) that is attached to the field I want to populate. I then open the Combo box property sheet and paste the clipboard contents ([Ctrl]+V) into the Row Source property.

Walla - the User MUST select from EXACTLY what is needed only.

The best way to solve this is to use Update queries. Basically open a query, select the field you want to update and enter the existing data as your criteria, then in the update to box, enter the lookup field data.

For example:

Title
Currently - A Tree Grows in Brooklyn

In the Look up - 23 A Tree Grows in Brooklyn

Set your Update query to update the field title to 23 and your criteria will be "A Tree Grows in Brooklyn". This is a little tedious as you have to do it for each item in the Lookup table, but that should be far less than doing it for each record in your main table.