composing filter using checkboxes

[notaloser]

heres what im trying to do

i have a main form that displays medical patients information from my table GENERAL, and then from REFMD, DIAGNOSIS, ENCOUNTERS, and MEDICATIONS as subforms.

i have a smaller form named multiple that has checkboxes with the values State, Zipcode, Diagnosis, Medication, and RefMD.

when say state and zipcode are selected on this form and submitted, i need to prompt the user for those 2 values and then filter the main report with the relative query.

i can do it with just state and zipcode, but i cant figure out how to include the subforms in the query, like diagnosis or medication

can anyone help?

 

[notaloser]

can anyone help me out? here is what i have as the event on my mini form submit button right now.. i havent finished, but the first 2 are checkboxes are part of the GENERAL table and i have done 1 from the DRUGS1 table, checkbox named Medication.

acForm = "PatientForm"
acFilter = "SELECT GENERAL.State, GENERAL.Zipcode, [PT-DXS].DIAGNOSIS, DRUGS1.DRUGNAME, REFMD.RefMdLast FROM [PT-DXS] INNER JOIN (([GENERAL] INNER JOIN DRUGS1 ON GENERAL.HistNum = DRUGS1.HISTNUM) INNER JOIN REFMD ON DRUGS1.HISTNUM = REFMD.HistNum) ON [PT-DXS].HISTNUM = REFMD.HistNum WHERE ("
acEnding = ""

If Me.State = True Then
acEnding = "((GENERAL.State)=[Enter State])"
acFilter = acFilter & acEnding
End If

If Me.Zipcode = True Then
If acEnding = "" Then
acEnding = "((GENERAL.Zipcode)=[Enter Zipcode])"
Else
acEnding = "AND ((GENERAL.Zipcode)=[Enter Zipcode])"
End If
acFilter = acFilter & acEnding
End If

'this is the part that does not work

If Me.Medication = True Then
If acEnding = "" Then
acEnding = "((DRUGS1.DRUGNAME)=[Enter Drug Name])"
Else
acEnding = "AND ((DRUGS1.DRUGNAME)=[Enter Drug Name])"
End If
acFilter = acFilter & acEnding

End If

'anyone know?

acFilter = acFilter & ")"
DoCmd.OpenForm acForm, acNormal, acFilter

 

[Banana]

Instead of using filter, create a embedded SQL statement and whenever a checkbox is checked, have your form recordsource change to the updated SQL statement.

Something like this:

If Me.chkState = -1 Then
    Me.Recordsource = "SELECT * FROM GENERAL WHERE STATE=" & Me.Inputbox
End If

Me.Requery

HTH.

 

[notaloser]

everything i have so far works fine except for the checkboxes that are representing a subform on the main patient form.

my table layout is:

GENERAL (main form source)
displays all the general information on PatientForm

DIAGNOSIS (subform)
displays all the information on the patient's diagnosis in a subform on PatientForm and should be filtered in my system using the field DIAGNOSIS, which is the name of the diagnosis's in the database

DRUGS1 (subform)
displays all the drug information for the patient in a subform on PatientForm, and should be filtered by the field DRUGNAME

REFMD (subform)
displays all the reffering doctor information for the patient in a subform on PatientForm and should be filtered using the doctors last name, field RefMDLast

hope that helps

 

[Banana]

In that case, you create a separate SQL statement for each subform if they want to filter the forms using what I suggested above.