Seeking Advice! Populating Fields based on other entries in table

[blairsly]

Last week some very altruistic folks here helped me solve a many-to-many relationships quandry, and I am hoping that there may some others out there who can help with another question.

I have a feeling this is simple, but I can't quite get my head around it, and have not been able to find it in the past forum threads, but....

I want to create a tblJobs that has information about our clients' Companies, as well as Contact. As there can be many Contacts for each Company, my current setup looks like this:

tblContacts
ContactID (Pkey)
Name
Lookup_to_qryCompanies_to_Contact(shows Contact's company)

tblComanies
CompanyID (Pkey)
Company Name
(Client info is included as embedded table based on qryCompanies_to_Contact)

qryCompanies_to_Contact
Company (from tblCompanies)
Contact (from tblContacts) (one-to-many)

In my new tblJobs, I would like to have a combo box in which I can input the company name, and then the next field will have another combo containing ONLY names of the contacts affiliated with the chosen company. (I will also eventually like to make a form that allows the same function, but am assuming that the process will be the same for updating the table trhough a form as it would be updating it directly).

Thank you for any advice!!

Blair Sly

 

[neileg]

Mmm…

Tables are for holding data, queries for linking it, forms are for displaying it. Although Access provides some features in table view, you’ll struggle to do anything at all complex unless you use a form.

Not clear if you have a one to one relationship between jobs and contacts, but I’ll assume you have.

Your Job form needs a combo based on a query that pulls out all your company names, as you suggest and stores the foreign key CompanyID in your job table. The contact combo needs to be based on a query that returns all the contacts relevant to the company selected in the first combo and stores the foreign key ContactID in your job table. This is known as a cascading combo and is a regular topic in here, so use the search facility, probably in the Forms Forum

 

[Milothicus]

here's a simpler way, assuming each contact can only be associated with one company.

tblContacts
ContactID (Pkey)
Name
CompanyID (Foreign Key)

tblCompanies
CompanyID (Pkey)
Company Name

Then if you know the contact name, you can fill in all the details for both contact and company on a form with one click.

 

[neileg]

Milothicus, I don't think your suggestion answers the question!

You are correctly pulling the company Id across from the contact, but I really think the requirement is the other way round. The company is the key association with the job, not the contact. Logically, contacts within companys may change so the list of available contacts should be based on the company.