Filtering Lookup Drop List using Drop List on same form

[KLewisBPM]

How can I use a drop down list to filter what I see in the second drop down list of the same form?



Kind Regards

Kelley Lewis

 

[SiJP]

Have a look at this thread...

http://www.tek-tips.com/viewthread.cfm?qid=1076509&page=1

------------------------
Hit any User to continue

 

[KLewisBPM]

Hi thanks for the link I read it but I can't seem to make any sense of it in my situation?? I ended up with the customer contact list being blank whatever the customer was set to.

here goes from the beginning

I have a form with two combo boxes

Customer and Customer_Contact

Customer combo box has this code in the rowsource

SELECT tblCustomerInfo.Auto_ID, tblCustomerInfo.Company FROM tblCustomerInfo ORDER BY tblCustomerInfo.Company;

Customer_Contact combo box has this code in the rowsource

SELECT [tblCustomerInfo Query].Auto_ID, [tblCustomerInfo Query].fullname FROM [tblCustomerInfo Query];

Auto_ID is a random number.

There could be multiple customers and multiple contacts

When selecting the customer from the first box I would like the second box to only show the Customer_contacts for that customer.

I hope this is clear enough!!!



Kind Regards

Kelley Lewis

 

[PHV]

How are [tblCustomerInfo Query] and tblCustomerInfo related ?
If by chance tblCustomerInfo Query].Auto_ID is a FK referencing tblCustomerInfo.Auto_ID, then try the following.
In the AfterUpdate event procedure of the Customer combo box :
Me!Customer_Contact.RowSource = "SELECT Auto_ID, fullname FROM [tblCustomerInfo Query] WHERE Auto_ID=" & Me!Customer.Auto_ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KLewisBPM]

tblCustomerInfo is the main table within it, it has customer contact firstname and customer contact surname.

I had to create a query from this original table to join the firstname and surname together in one field called fullname.

the Auto_ID is a nonsense field its just the record number and as there are multiple customers and customer_contacts I cannot use that as the filter.

To make it simpler I have made both row sources select from the tblCustomerInfo Query.

Do I need to include in the SELECT more fields??

Kind Regards

Kelley Lewis

 

[KLewisBPM]

I have tried entering the following, in AfterUpdate but seem to be hitting problems still when proceeding to click the customer_contact combo box.

Me!Customer_Contact.RowSource = "SELECT fullname FROM [tblCustomerInfo Query] WHERE Customer=" & Me!Customer

Any ideas?

Kind Regards

Kelley Lewis

 

[KLewisBPM]

When I check the property's of the customer_contacts combo box its shows the correct company that has been picked however it is in square brackets.

e.g. [MyCompany] but it should be "MyCompany"

Thanks again for your assistance

Kind Regards

Kelley Lewis

 

[KLewisBPM]

bump

Kind Regards

Kelley Lewis