Company, contacts and job question

[andymartin3186]

Hi
Ive created a database which is a job system
I have a customer table and each customer can have many contacts which are stored in a separate table. The customer form has a subform and this works showing all associated contacts
The issue I have is when it comes to creating a job. The job table pulls through customer name as a dropdown and also contact name as a dropdown along with other job specific fields.
The customer dropdown works fine but the contact dropdown then needs to show only associated contacts for the chosen customer, however it shows the entire contacts table. I have no idea how to do this and despite searching can’t find the name of what this function is. I assume its a query of some sort but need some guidance.
Hope this makes sense

Thanks

 

[The_Doc_Man]

Look up "cascading combo boxes" which is a technique that allows combo box B to use combo box A's answer as a filter to its .RowSource argument, thus reducing returned options.

By the way, the forum software says that was your first post. Hello, Andy, and welcome to the forums.

 

[GaP42]

The record source for the contact dropdown must include the customerID (?). The CustomerID is used to filter the record source. To do this ensure that in the query supporting the record source that the CustomerID criteria is set to something like Forms!frmCustomer!cboCustomer

 

[andymartin3186]

Look up "cascading combo boxes" which is a technique that allows combo box B to use combo box A's answer as a filter to its .RowSource argument, thus reducing returned options.

By the way, the forum software says that was your first post. Hello, Andy, and welcome to the forums.

Hello
Many thanks for the welcome.
I last did access in 2002 at college so slowly picking it up.
I will check this out, appreciated.

 

[Gasman]

It has been mentioned many times here, so you could search here as well.
Here is one thread from a search here https://www.access-programmers.co.uk/forums/threads/dropdown-query.329053/#post-1892726

When in the thread Similar threads at the bottom will also show more.

 

[GPGeorge]

Spoiler:

Hi
Ive created a database which is a job system
I have a customer table and each customer can have many contacts which are stored in a separate table. The customer form has a subform and this works showing all associated contacts
The issue I have is when it comes to creating a job.

The job table pulls through customer name as a dropdown and also contact name as a dropdown

Spoiler:

along with other job specific fields.
The customer dropdown works fine but the contact dropdown then needs to show only associated contacts for the chosen customer, however it shows the entire contacts table. I have no idea how to do this and despite searching can’t find the name of what this function is. I assume its a query of some sort but need some guidance.
Hope this makes sense

Thanks

Unfortunately, that sounds like you use Lookup Fields for the CustomerID and ContactID in the job table. If so, you probably want to revisit that decision before moving on to try to implement cascading combo boxes.

The irony of Lookup fields in tables is that most of the time, experienced developers who would be able to handle them properly avoid them because of the complications the introduce. Less experienced newcomers see them and think they're handy, but all too often end up misusing them and getting into trouble because they don't understand what is going on.

I strongly suggest, therefore, that you replace any such Lookup Fields with plain old boring fields for the Foreign Keys required. Here, that would be CustomerID and ContactID.

 

[Pat Hartman]

Lookup fields on tables are a crutch for people who can't create a query with a join. Once you can create a query with a join and code some basic VBA, you have progressed beyond the point where lookup fields are useful. Instead they simply obfuscate the actual table data and cause confusion. Combos belong on forms, NOT on tables.

Cascading combos are implemented with a single line of code for each combo. Each combo has a RowSource query that references its "parent" combo. So - combo1, combo2, combo3.
query for combo1:
Select .... From ... Order By ...
query for combo2
Select ... From ... Where fld1 = Forms!yourform!combo1 Order By ...
query for combo3
Select ... From ... Where fld2 = Forms!yourform!combo2 Order By ...

The Code:
AfterUpdate event of combo1
Me.combo2.Requery
AfterUpdate event of combo2
Me.Combo3.Requery