Combo list based on current record

[James_Morant]

I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way.

I have three tables; Contacts, Companies, Company_Addresses

Contacts:

ContactID (P)
CompanyID
OfficeName​

Companies:

CompanyID (P)​

Company_Addresses:

CompanyID
OfficeName (P)​

What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field.

I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated.

Sorry if this is in the wrong forum.

Thanks. James

 

[Zigzag]

Hi James,

What i find easiest is to build a query based on Company_Addresses: Table.
Within the query set the CompanyID Like the combo box you have on your form.
Use this query as the record source for your Officename.

There are other ways to do it but this is an easy way to start.

Have a go and if you get stuck just shout.

 

[James_Morant]

Thanks Zigzag. Sadly it is still not working. Is it because you cant reference to a field that has just been editted on that record? Here is my current query:

SELECT Company_Addresses.CompanyAddressID, [AddressName] & " (" & [Town/City] & ")"
FROM Company_Addresses
WHERE Company_Addresses.CompanyID=Contact_Details.CompanyID;

This is run on the Contact_Details table and instead of gathering the CompanyID from the current record's CompanyID field, it asks me for a manual input.

Any help grately appreciated. Thanks. James

 

[Rabbie]

Set up relationships between your tables and then use Query builder to build your query. That's the easiest way to set up the Inner Joins. Then use that query to build your form. The Wizards are the best way to learn the basics with Access.

 

[Rabbie]

Also check the spelling in your query. If it asks you for a manual input its because it can't find that field. The name it is asking for will tell you where to look.