Autopopulate a field based on contents of another

[TheHub]

I am in the process of revamping an old database, and I would like a little help. I have a main table that currently has two fields "Name" and "Job" but will have more. I have another table with the same fields. I want to use as a lookup to populate the main table with a user form from the second table. I can populate the "Name" field using a combo box, but my problem is how to autopopulate the "Job" field. eg If the user selects Mr Smith from the "Name" drop down list, the corresponding "Job" populates the second field. If that makes sense .

 

[pr2-eugin]

You are talking about lookup at Table level, this is a very bad idea.. You do not need to store this information in the table at all. You need to have set up relationships.

 

[TheHub]

Thanks for the quick reply, but unfortunately, I have to work with what I have got

 

[Helystra]

So you would suggest just making a "number" field to hold the data and using the form field to do the looking up and just creating the relationship of the primary/foreign key fields separately or how does that actually work?

 

[Cronk]

Hub

Why do you have data duplicated across tables? Use an update query to set the Job field in the main table for all records and remove the second table.