We help IT Professionals succeed at work.

SQL for Cascading Combo Box

yoducati
yoducati asked
on
I am posting this question as a follow up to the question at this link:

https://www.experts-exchange.com/questions/29057032/ComboBox-on-Access-Form-with-Blank-Selection-Option-to-Clear-Filter.html#a42300096

I have a form with two unbound combo boxes, one for the course and one for the preferred session of that course.  Each are located in the header of a continuous form which displays each request submission that has not been assigned to a session.  The user is able to filter the list based upon both the class and the preferred session.  

I need to add the cascading functionality so that if the user filters the list by course only the preferred sessions for the requests for that particular course show up in the preferred sessions filter combo.

Here is the SQL I have for each combo box:

Course

SELECT qryReqPendingAssignment.Course, tblCourses.CourseName FROM tblCourses INNER JOIN qryReqPendingAssignment ON tblCourses.CourseID = qryReqPendingAssignment.Course UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblCourses.CourseName;

Preferred Session

SELECT qryReqPendingAssignment.PreferredSession, tblEvents.EventDesc FROM tblEvents INNER JOIN qryReqPendingAssignment ON tblEvents.EventID = qryReqPendingAssignment.PreferredSession UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblEvents.EventDesc;
Comment
Watch Question

Are ypu comfortable with VBS code to generate query on the fly? You  also might be able to reference the forms combo box in the query itself, however since your using a continous form the control source will change all the rows shown
Sorry I meant VBA
Kyle AbrahamsSenior .Net Developer

Commented:
SELECT qryReqPendingAssignment.PreferredSession, tblEvents.EventDesc FROM tblEvents INNER JOIN qryReqPendingAssignment ON tblEvents.EventID = qryReqPendingAssignment.PreferredSession
where qryReqPendingAssignment.PreferredSession = ...
UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment

Open in new window


Just plug in the value from the first combo box.

Author

Commented:
Hi Kyle, I changed my SQL as you suggested but now I only get the "View All" option in the preferred session combo box.

Author

Commented:
Hi Gerald,

I am much more comfortable with VBA than SQL, but I already have everything else working so Id prefer to just add the correct SQL statement, which Im pretty sure is a simple where clause like Kyle suggested but I just can't figure out exactly what it needs to be.

Commented:
I did this recently...
You want to change one combo box and filter the results of the other?

When you do your sql you have some sort of interface program  (.net, C#, etc) where your combo box resides.
1. Change the combo for Course (call your on change event) From your onchange event call a different SQl statement to populate your other combo box.

You can get fancy and set both combo boxes to filter each other if you put in parameters based on the combobox values.  
When the part number box is changed

Mine something like this...

      @StopReason as Nvarchar(100)=null,
      @PartNumber as Nvarchar(255)=null

      select partnumber, ordernumber from TStoppedOrders
WHERE  (@StopReason is null or (StopReason=@StopReason)) and (@PartNumber is null or (PartNumber=@PartNumber))

Hopefully this is what you're looking for :-)
Kyle AbrahamsSenior .Net Developer

Commented:
Ensure you're passing the right value?

Essentially you just need the where clause to filter out the correct sessions for the given course.

I would recommend you first write out the SQL for one course.

The other thing I forgot to mention was you have to rerun the session sql every time the course changes.  (EG: on selectedindex changed).