autolookup

[welsh girl]

I have a db with 3 tables - candidates, clients and jobs. I have created a relationship between clients (one) and jobs (many) by the client ID field. In the jobs form I want to be able to type in a client id and have all the other fields filled in for me
Anyone help?

 

[Jeff Boyce]

Why? As in "why do you want to copy data from one table to another?" This
isn't necessary in Access, wastes disk space, and risks the data getting out
of sync.

Your table structure may benefit from further normalization if you are
presently copying "all the other fields" into your tblJobs. And if your
tblCandidates is an exact match for your tblClients, that's not necessary
either.

Can you give a bit more detail about what the tables contain? The 'group
readers may be able to offer alternative approaches...

 

[John Vinson]

thank you for replying. Basically I have a table called clients which records all the information pertaining to a client e.g name, address and all other contact details etc and then I have a table called jobs which has a lot of different information but also some of the client information - mainly the contact details.

This table is then incorrectly designed. It is neither necessary nor
desirable to store the contact details in two different tables.

When I look at a particular job it will tell me not only information about the job but also about the company (client) who has submitted the job so rather than enter all the contact information for the client all over again I just want to type in the client id (unique ref) and let the system do the rest for me.

When you "look at" it, you should be looking at a Query based on both
tables, or you should have unbound textboxes showing the contact
information, or you should be using a Form based on the contact table
and a Subform based on the job table. It is NOT necessary to store the
information all in one table in order to be able to "look at it".