Mutual control between 2 combo boxes

Jeff H
3StarLounger
Posts: 316
Joined: 31 Oct 2017, 20:07

Mutual control between 2 combo boxes

Post by Jeff H »

I’m trying to make two comboboxes dependent on each other. My web search turned up lots of examples, but always where one combo controls another; not where either of two combos controls the other. (I’m thinking that from a null state for both boxes, whichever combo is set first remains the controller until the ‘Clear Filter’ button is clicked to restore a null state.)

When the user selects a patient in one combobox, the other one should only display a unique list of volunteers who are matched with that patient in the Service Events table. Conversely, when the user selects a volunteer, the patient combobox should only show patients matched with that volunteer in Service Events. (Service Events is the record of all patient/volunteer contacts and it is the object of the filter whose parameters are selected with these combos.)

The objective is, for filtering purposes, if the user selects either a patient or a volunteer and leaves the other combo blank, they’ll see all the visits that person received/provided. But if they want to see the visits between a particular patient and volunteer, after making a selection in one combo, the other one will only show the relevant options.

I made a test query using three tables: tblPatients; tblVolunteers; and tblSvcEvents. I set the Unique Values to true and manually plugged in a PID (patient ID*). That produced exactly the volunteer results I want with the SQL statement:

Code: Select all

SELECT DISTINCT tblPatients.PatientID, tblPatients.PID, tblPatients.FirstName,
tblPatients.LastName, tblVolunteers.VolunteerID, tblVolunteers.VID,
tblVolunteers.FirstName, tblVolunteers.LastName, tblSvcEvents.PID, tblSvcEvents.VID
FROM tblVolunteers INNER JOIN (tblPatients INNER JOIN tblSvcEvents 
ON tblPatients.PatientID = tblSvcEvents.PatientID) 
ON tblVolunteers.VolunteerID = tblSvcEvents.VolunteerID
WHERE (((tblSvcEvents.PID)="GT01"));
But I can’t work out how to apply this to the two comboboxes. Is this doable? Is there a better way to get the result I’m looking for?

*[Regarding PatientID and VolunteerID in the SQL statement, those are the auto-number record ids. PID and VID are unique identifications assigned to each person.]

User avatar
HansV
Administrator
Posts: 69458
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mutual control between 2 combo boxes

Post by HansV »

I think it'd be conceptually easier to use two sets of two combo boxes each:
1) Patients > Volunteers associated with the selected Patient.
2) Volunteers > Patients associated with the selected Volunteer.
Otherwise, you might create an endless cascade of updates.
When the user selects a Patient in the first set, reset the combo boxes in the second set, and when the user selects a Volunteer in the second set, reset the combo boxes in the first set.
Regards,
Hans

Jeff H
3StarLounger
Posts: 316
Joined: 31 Oct 2017, 20:07

Re: Mutual control between 2 combo boxes

Post by Jeff H »

Hmm. That makes sense. I'll see if I can apply it.

Jeff H
3StarLounger
Posts: 316
Joined: 31 Oct 2017, 20:07

Re: Mutual control between 2 combo boxes

Post by Jeff H »

Update: it's taken me all day, but I was able to implement your suggestion and it seems to be working quite well. Thanks!

User avatar
HansV
Administrator
Posts: 69458
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Mutual control between 2 combo boxes

Post by HansV »

Good to hear that! :thumbup:
Regards,
Hans