Conditional display of records based on user's multiple selections

[cna...@gmail.com]

Greetings
I have a simple MS-Access database with 2 tables in a many-to-many
relationship. One is a table of diseases and one is a table of
symptoms.
I need to create a form where all the symptoms show on the left side
with a checkbox. When one checkbox is clicked, the diseases associated
with the symptom show on the left side.
If another disease is selected, only the diseases associated with
both symptoms are displayed, and so on.
What would be the best way to create this form?
I'd be forever grateful to whoever can give me an idea of how to
solve this problem.

C

[jimfo...@compumarc.com]

Try This:

tblSymptoms
SID
Symptom
SCheckBox

tblDiseases
DID
Disease
DCheckBox

tblSymptomDiseases
SDID
DID
SID

SubformDiseases.RowSource = SELECT Disease, DCheckBox FROM tblDiseases
ORDER BY DID;

SubformSymptoms.RowSource = SELECT Symptom, SCheckBox FROM tblSymptoms
ORDER BY SID;

cbxDiseases.RowSource = SELECT Disease FROM tblDiseases WHERE DID IN
(SELECT DID FROM tblSymptomDiseases WHERE SID IN (SELECT SID FROM
tblSymptoms WHERE SCheckBox = -1) GROUP BY DID HAVING COUNT(DID) =
(SELECT COUNT(*) FROM tblSymptoms WHERE SCheckBox = -1));

cbxSymptoms.RowSource = SELECT Symptom FROM tblSymptoms WHERE SID IN
(SELECT SID FROM tblSymptomDiseases WHERE DID IN (SELECT DID FROM
tblDiseases WHERE DCheckBox = -1) GROUP BY SID HAVING COUNT(SID) =
(SELECT COUNT(*) FROM tblDiseases WHERE DCheckBox = -1));

Note: the counts force the inclusion only of SID's that show up for
every DID checked and vice versa.

Code behind frmSubDiseases:
Private Sub DCheckbox_AfterUpdate()
Dim strBookmark As String

strBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = strBookmark
Me.Parent!cbxSymptoms.Requery
End Sub

Code behind frmSubSymptoms:
Private Sub SCheckbox_AfterUpdate()
Dim strBookmark As String

strBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = strBookmark
Me.Parent!cbxDiseases.Requery
End Sub

Set the name of the first subform to SubformDiseases with Source Object
frmSubDiseases. Put cbxSymptoms below it.

Set the name of the second subform to SubformSymptoms with Source
Object frmSubSymptoms. Put cbxDiseases below it.

If everything is set up properly, clicking the checkboxes in
SubformDiseases will requery cbxSymptoms to show only the symptoms
common to all the diseases checked. Clicking the checkboxes in
SubformSymptoms will requery cbxDiseases to show only the diseases
common to all the symptoms checked.

Here is the data I used:

tblSymptoms
SID Symptom SCheckbox
1 FirstSymptom unchecked
2 SecondSymptom unchecked
3 ThirdSymptom checked

tblDiseases
DID Disease DCheckbox
1 FirstDisease unchecked
2 SecondDisease unchecked
3 ThirdDisease unchecked

tblSymptomDiseases
SDID DID SID
1 1 1
2 1 2
3 1 3
4 2 1
5 3 1
6 3 3

This resulted in the dropdown for cbxDiseases showing FirstDisease and
ThirdDisease. After clicking DCheckbox for SecondDisease, the
cbxSymptoms dropdown became FirstSymptom.

The comboboxes should probably be changed into listboxes. Testing this
out was a lot of fun but it could use a little more testing.

James A. Fortune