SELECT queries and Combo_Boxes 1

[laina222]

I have a problem with a query I'm trying to build. I have a two tables. One is a Call Log that records every complaint a customer has when they call customer service. The Customer service rep will choose a complaint type from a combo box and then according to which complaint type they selected, I want the next combo box to display a list of complaints that correspond to that complaint type. My problem is this, I have created a new table listing a unique key called Complaint_Code, a Complaint_Type which corresponds/is related to the Complaint_Type which is the first combo box the user selects for the Call Log table, and the Complaint_Descriptions. There are three complaint types and 18 Complaint Descriptions. I can get the Complaint_Description combo box to display all of the Complaint_Description values but I can't get it to display only those that correspond to the Complaint_Type. I have an SQL statement in the Row Source field for the Complaint Description that states
SELECT tblComplaint.Complaint_Description
FROM tblComplaint
WHERE tblCallLog.Complaint_Type = tblComplaint.Complaint_Type
Every time I go to enter information, it asks what the Parameter values are for tblCallLog.Complaint_Type which is exactly the previous Combo_box I selected before.
Anyone?

 

[jebry]

Hi!

The SQL you need for the second combo box is:

SELECT tblComplaint.Complaint_Description
FROM tblComplaint
WHERE tblComplaint.Complaint_Type = Forms!YourFormName!YourTypeComboBox

Of course you will need to substitute the actual name of your form and combo box

hth
Jeff Bridgham

 

[laina222]

What if I don't have any forms?
How do I create a form so this statement will be applicable?
And what is YourTypeComboBox? Are there various kinds of combo boxes to select from?

 

[laina222]

Okay-
That was somewhat helpful, but I still have a problem. I entered the following into the second Combo box:
SELECT tblComplaint.Complaint_Description
FROM tblComplaint
WHERE tblComplaint.Complaint_Type = Forms!frmCallLog!tblCallLog.Complaint_Type
This seemed to work until I went to a second record and selected another option for the first combo box. The second combo box then displayed the values that corresponded to my selection for the previous combo box.
Any new ideas?

 

[jebry]

Hi!

In the AfterUpdate event procedure of your first combo box add the following code:

Me![Insert the name of your second combo box].Requery

This will rerun the SQL statement of your second combo box.

hth
Jeff Bridgham

 

[laina222]

How do I create an AfterUpdate event?

 

[jebry]

Hi1

In the form's design view, right click on the combo box and choose properties. Choose the event tab and click in the After Update box. Click on the box with the three dots to the side and then choose code builder. Access will take you to the code window and put the first and last lines of the procedure in for you. All you will need to do is add the code I gave you.

hth
Jeff Bridgham

 

[laina222]

Thanks so much!
That was extremely helpful. Now I can get on with my life!