Narrowing search using two combo boxes

D

dackedyman

New member
Local time
Today, 00:35
Joined
Aug 3, 2019
Messages
9
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.
 
Last edited by a moderator:
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.
 
Last edited:
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.
 
Last edited by a moderator:
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
 
Perhaps you read my previous post before my edits. Review it again.
 
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.
 
Last edited by a moderator:
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?
 
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]);"
 
Last edited by a moderator:
You must log in or register to reply here.

Similar threads

H
Solved Search across multiple fields
Replies
8
Views
822
Harry Paraskeva
H
B
Combo Box Search Issue
2
Replies
34
Views
1,384
MajP
MajP
A
Search box to find parts to add to job instead of a combo box
2 3
Replies
41
Views
703
Gasman
Gasman
Uncle Gizmo
🛠️ UberChatMaster: Take Control with Your Own AI Interface
Replies
1
Views
450
Thales750
T
A
Solved Combo box query
Replies
3
Views
353
AlliCarr
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom