Narrowing search using two combo boxes (1 Viewer)

dackedyman · Aug 13, 2019

I have seen a few threads on this issue online, but they are confusing to me.

I work for a medical company with Therapist, and I have been able to display who specializes in a certain area based of a combo box selection. However, I want to further filter/narrow the search results based off the model that the therapist uses. It is not so important to understand what Model means, but I want to use two combo boxes to narrow the search results to show the user which Therapist have a certain combination of Specialty and Model.

I have created a UNION query to combine the Specialties and Models that the Therapist use. However, I do not know how to implement this SQL in VBA so that the user can first select by Specialty and get certain results and then to further narrow those results based off the model that the user selects.

This is the VBA that I have so far for the Specialty that the user can use to find out who Specializes in a certain area:

Code:

List6.RowSource = "SELECT tblStaff.StaffID, tblStaff.FirstName AS [First Name], tblStaff.LastName AS [Last Name], tblStaff.Position, tblSpecialities.Type " & _
    "FROM (tblStaff INNER JOIN StaffSpecialitiesJunctionTbl ON tblStaff.StaffID = StaffSpecialitiesJunctionTbl.StaffID) " & _
    "INNER JOIN tblSpecialities ON StaffSpecialitiesJunctionTbl.SpecialitiesID = tblSpecialities.SpecialitiesID " & _
    "WHERE tblSpecialities.Type = Forms![SpecialitySearch]![Combo10]" & " " & _
    "ORDER BY tblStaff.LastName, tblStaff.FirstName;"

Any help would be greatly appreciated. Thank you.

June7 · Aug 13, 2019

Do specialties and models have a relationship? Is there a table that defines these associations?

Therapist/Specialty and Therapist/Model associations are in separate tables and you UNIONed those? Would be nice to see that query but not sure it is even needed.

And List6 with staff name info probably not needed.

To restrict model combobox/listbox based on selected specialty:

SELECT * FROM <query joining StaffModelJunctionTbl with tblStaff> WHERE TherapistID IN (SELECT TherapistID FROM StaffSpecialitiesJunctionTbl WHERE Specialty=[cboSpecialty]);

If you want to provide db for analysis, follow instructions at bottom of my post.

dackedyman · Aug 13, 2019

Thank you for your quick response. I appreciate it.

Here is the UNION query I have:

Code:

SELECT tblStaff.StaffID, tblStaff.FirstName, tblStaff.LastName, tblSpecialities.Type
FROM (tblStaff INNER JOIN StaffSpecialitiesJunctionTbl ON tblStaff.StaffID = StaffSpecialitiesJunctionTbl.StaffID) INNER JOIN tblSpecialities ON StaffSpecialitiesJunctionTbl.SpecialitiesID = tblSpecialities.SpecialitiesID

UNION 

SELECT tblStaff.StaffID, tblStaff.FirstName, tblStaff.LastName, tblTherapeuticModel.Type
FROM tblTherapeuticModel INNER JOIN (tblStaff INNER JOIN StaffModelJunctionTbl ON tblStaff.StaffID = StaffModelJunctionTbl.StaffID) ON tblTherapeuticModel.ModelID = StaffModelJunctionTbl.ModelID;

They have a relationship based off the StaffID. I am not sure how to incorporate the union query in vba to get my desired results. The two combo boxes are SpecialtyCombo and ModelCombo.

isladogs · Aug 13, 2019

You shouldn't be using a union query for this.
Unions display the results of both queries in the same output so that will add more records to the results.
In your case, you want to filter the results of the first query to reduce the number of records shown.
What you want is often referred to as cascading combos.

June has already provided a method for doing this using one query with subquery.
Alternatively you can use stacked queries where the second query filters results from the first

June7 · Aug 13, 2019

Perhaps you read my previous post before my edits. Review it again.

dackedyman · Aug 14, 2019

Thank you for your help June7 and isladogs. I implemented the changes June7 suggested to the best of my ability, but the list box goes blank when I choose an option from the drop down list. Below is the code I have used.

Code:

Private Sub SpecialtyCombo_AfterUpdate()

    List6.RowSource = "SELECT * FROM tblTherapeuticModel INNER JOIN (tblStaff INNER JOIN StaffModelJunctionTbl ON tblStaff.StaffID = StaffModelJunctionTbl.StaffID) ON " & _
    "tblTherapeuticModel.ModelID = StaffModelJunctionTbl.ModelID " & _
    "WHERE tblStaff.StaffID IN (SELECT tblStaff.StaffID FROM StaffSpecialitiesJunctionTbl WHERE tblSpecialities.Type=[SpecialtyCombo];"

Thank you for your help.

isladogs · Aug 14, 2019

I may be wrong but that row source code looks more complicated than it needs to be.
Suggest you test by pasting that code into a new query.
Now add specific valid values for the WHERE clause. Does that give a result?

June7 · Aug 14, 2019

Yes, looks more complicated than needed.

Code:

List6.RowSource = "SELECT * FROM (SELECT tblStaff.StaffID, LastName, FirstName FROM tblStaff  INNER JOIN StaffModelJunctionTbl ON tblStaff.StaffID = StaffModelJunctionTbl.StaffID) AS Q1 " & _
"WHERE StaffID IN (SELECT StaffID FROM StaffSpecialitiesJunctionTbl WHERE [Type]=[SpecialtyCombo]);"

Narrowing search using two combo boxes (1 Viewer)

dackedyman

New member

June7

AWF VIP

dackedyman

New member

isladogs

MVP / VIP

June7

AWF VIP

dackedyman

New member

isladogs

MVP / VIP

June7

AWF VIP

Similar threads

Users who are viewing this thread