Query using combo box

[Supraracer]

I have a table named Quote_Info. Within is a field named Customer_ID (which looks up the Customer_Name from the Customer_Info table). There is als a Contact_ID field which would look up the Contact_ID from the Customer_Contact table. Each customer can have several contacts.

This is what I am trying to do.

In the Quote_Info Datasheet I click a combo box to select the Customer Name (stores the ID not the name but this works fine) and based on this the Contact Name combo box should only display the contact names of that customer when the down arrow is clicked. I either get all the customers contacts or none in my attempts.

I choose Smith Contracting then go to Contact name and click down arrow. Only the Contacts from Smith contracting should show up here but I will get all or none.

SELECT Customer_Contact.Contact_ID, Customer_Contact.Last_Name, Customer_Contact.First_Name, FROM Customer_Contact WHERE Customer_Contact.Customer_ID = [Quote_Info].[Customer_ID] ORDER BY Customer_Contact.Last_Name;

Is the current SQL but it will prompt me for the info rather than pull it from the form / datasheet I am using. I am new to all of this but do have some basic understanding of programming. I don't know if this will have to be done in VBA because the querrie runs when the sheet is opened and not when changed?

Thank you for your time.

John

 

[MSAccessRookie]

I have a table named Quote_Info. Within is a field named Customer_ID (which looks up the Customer_Name from the Customer_Info table). There is als a Contact_ID field which would look up the Contact_ID from the Customer_Contact table. Each customer can have several contacts.

This is what I am trying to do.

In the Quote_Info Datasheet I click a combo box to select the Customer Name (stores the ID not the name but this works fine) and based on this the Contact Name combo box should only display the contact names of that customer when the down arrow is clicked. I either get all the customers contacts or none in my attempts.

I choose Smith Contracting then go to Contact name and click down arrow. Only the Contacts from Smith contracting should show up here but I will get all or none.

SELECT Customer_Contact.Contact_ID, Customer_Contact.Last_Name, Customer_Contact.First_Name, FROM Customer_Contact WHERE Customer_Contact.Customer_ID = [Quote_Info].[Customer_ID] ORDER BY Customer_Contact.Last_Name;

Is the current SQL but it will prompt me for the info rather than pull it from the form / datasheet I am using. I am new to all of this but do have some basic understanding of programming. I don't know if this will have to be done in VBA because the querrie runs when the sheet is opened and not when changed?

Thank you for your time.

John

SELECT Customer_Contact.Contact_ID, 
    Customer_Contact.Last_Name, 
    Customer_Contact.First_Name[B][COLOR=red], [/COLOR][/B]
FROM Customer_Contact 
WHERE Customer_Contact.Customer_ID = [COLOR=darkred][B][Quote_Info].[Customer_ID][/B][/COLOR]
ORDER BY Customer_Contact.Last_Name;

The example above has two issues to be concerned with:

1. There is an extra "," (Marked in RED)
2. The Comparison Field name (Marked in MAROON) is formatted as if it were a Field in another Table, when I believe you want to get the information from a Control Field from a Form. You do not need VB for this, but you do need to refer to the Control Field similar to below:

WHERE Customer_Contact.Customer_ID = Forms!Quote_Info!Customer_ID

​

 

[Supraracer]

You are the man! That worked great. Thank you so much.

John