3 List Boxes + Several Check Boxes on a Parameter Form

[KMEscherich]

Hi there, am wondering if someone can tell me how to get 3 list boxes to work in conjunction with a bunch of check-boxes on an independent form and have the results filter the report accordingly.

I have temp tables for each set of code that would pertain to EMPLOYEE / DEPARTMENT / LOCATION. When I run the code when everything is not commented out, the selections that I make go into the temp tables perfectly, but for some reason the temp table results do not get passed onto the report as it should. Therefore, when I press run, the report will display more results than what I specify on the paramter form.

I sure hope someone can help me out with this.

 
Private Sub BTN_RUN_REPORT_Click()
On Error GoTo BTN_RUN_REPORT_Click_Err
 
 
    Dim rs As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    Dim strSQL As String
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE TEMP_TABLE.* FROM TEMP_TABLE;"
    DoCmd.SetWarnings True
 
    Set cnn = CurrentProject.Connection
    strSQL = "SELECT * From TEMP_TABLE"
 
    rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
 
      'Ensure that primary key is the bound value of the listbox
      Dim VarItem As Variant
      Dim VarData As Variant
        For Each VarItem In Me.LIST_EMPLOYEE.ItemsSelected
            VarData = Me.LIST_EMPLOYEE.ItemData(VarItem)
 
            rs.AddNew
            rs.Fields("NUID") = VarData
            rs.Update
        Next VarItem
 
' -------------------------------------------------------------------------------
 
'    Dim rs_2 As New ADODB.Recordset
'    Dim cnn_2 As New ADODB.Connection
'    Dim strSQL_2 As String
'
'    DoCmd.SetWarnings False
'    DoCmd.RunSQL "DELETE TEMP_TABLE_DEPT.* FROM TEMP_TABLE_DEPT;"
'    DoCmd.SetWarnings True
'
'    Set cnn_2 = CurrentProject.Connection
'    strSQL_2 = "SELECT * From TEMP_TABLE_DEPT"
'
'    rs_2.Open strSQL_2, cnn, adOpenDynamic, adLockOptimistic
'
'      'Ensure that primary key is the bound value of the listbox
'      Dim VarItem_2 As Variant
'      Dim VarData_2 As Variant
'        For Each VarItem_2 In Me.LIST_DEPARTMENTS.ItemsSelected
'            VarData_2 = Me.LIST_DEPARTMENTS.ItemData(VarItem_2)
'
'            rs_2.AddNew
'            rs_2.Fields("DEPT_ABBR") = VarData_2
'            rs_2.Update
'        Next VarItem_2
 
' -------------------------------------------------------------------------------
 
'Dim rs_3 As New ADODB.Recordset
'    Dim cnn_3 As New ADODB.Connection
'    Dim strSQL_3 As String
'
'    DoCmd.SetWarnings False
'    DoCmd.RunSQL "DELETE TEMP_TABLE_LOCATION.* FROM TEMP_TABLE_LOCATION;"
'    DoCmd.SetWarnings True
'
'    Set cnn_3 = CurrentProject.Connection
'    strSQL_3 = "SELECT * From TEMP_TABLE_LOCATION"
'
'    rs_3.Open strSQL_3, cnn_3, adOpenDynamic, adLockOptimistic
'
'      'Ensure that primary key is the bound value of the listbox
'      Dim VarItem_3 As Variant
'      Dim VarData_3 As Variant
'        For Each VarItem_3 In Me.LIST_MOB.ItemsSelected
'            VarData_3 = Me.LIST_MOB.ItemData(VarItem_3)
'
'            rs_3.AddNew
'            rs_3.Fields("MOB") = VarData_3
'            rs_3.Update
'        Next VarItem_3
 
 
' -------------------------------------------------------------------------------
 
If (CB_ACUITY_DEPT) Then
  ' Acuity - By Department
  DoCmd.OpenReport "R_ACUITIES_DEPT", acViewPreview, "", "", acWindowNormal
  End If
If (CB_ACUITY_EMPLOYEE) Then
  ' Acuity - By Employee
  DoCmd.OpenReport "R_ACUITIES_EMPLOYEE", acViewPreview, "", "", acWindowNormal
  End If
If (CB_ACUITY_AGE_GROUP) Then
  ' Acuity - By Age Group
  DoCmd.OpenReport "R_ACUITIES_AGE_GROUP", acViewPreview, "", "", acWindowNormal
  End If
If (CB_INTERVENTION_SUMMARY) Then
  ' Intervention Summary - Count
  DoCmd.OpenReport "R_COUNT_INTERVENTION_2", acViewPreview, "", "", acWindowNormal
  End If
If (CB_PROBLEM_SUMMARY) Then
  DoCmd.OpenReport "R_COUNT_PROBLEM_2", acViewPreview, "", "", acWindowNormal
  End If
If (CB_CHILD_ABUSE) Then
  DoCmd.OpenReport "R_CHILD_ABUSE", acViewPreview, "", "", acWindowNormal
  End If
If (CB_ELDER_ABUSE) Then
  DoCmd.OpenReport "R_ELDER_ABUSE", acViewPreview, "", "", acWindowNormal
  End If
If (CB_HOMELESS) Then
  DoCmd.OpenReport "R_HOMELESS", acViewPreview, "", "", acWindowNormal
  End If
If (CB_DOMESTIC_VIOLENCE) Then
  DoCmd.OpenReport "R_DOMESTIC_VIOLENCE", acViewPreview, "", "", acWindowNormal
  End If
If (CB_PSYCH) Then
  DoCmd.OpenReport "R_PSYCH", acViewPreview, "", "", acWindowNormal
  End If
If (CB_ENCTRS_TYPE_LOC) Then
  DoCmd.OpenReport "R_ENCOUNTERS_MOB", acViewPreview, "", "", acWindowNormal
  End If
If (CB_LOC_SUMM_DEPT_EMPL) Then
  DoCmd.OpenReport "R_LOCATION_EMPL1", acViewPreview, "", "", acWindowNormal
  End If
If (CB_LOC_SUMM_DEPT_ENCTR) Then
  DoCmd.OpenReport "R_LOCATION_BY_DEPT", acViewPreview, "", "", acWindowNormal
  End If
If (CB_LOC_SUMM_INTERVENTION) Then
  DoCmd.OpenReport "R_INTERVENTION_2", acViewPreview, "", "", acWindowNormal
  End If
If (CB_LOC_SUMM_PROBLEM) Then
  DoCmd.OpenReport "R_PROBLEM_TYPE_SUMMARY", acViewPreview, "", "", acWindowNormal
  End If
If (CB_PATIENT_AGE_GROUP) Then
  DoCmd.OpenReport "R_PATIENT", acViewPreview, "", "", acWindowNormal
  End If
If (CB_PATIENT_ENCTRS) Then
  DoCmd.OpenReport "R_PATIENT_ENCOUNTERS", acViewPreview, "", "", acWindowNormal
  End If
If (CB_EMPL_PRODUCTIVITY) Then
  DoCmd.OpenReport "R_HOURS_EMPLOYEE", acViewPreview, "", "", acWindowNormal
  End If
If (CB_ENCTR_TYPE_EMPLOYEE) Then
 DoCmd.OpenReport "R_ENCOUNTERS_EMPLOYEE", acViewPreview, "", "", acWindowNormal
 End If
 
BTN_RUN_REPORT_Click_Exit:
Exit Sub
 
BTN_RUN_REPORT_Click_Err:
MsgBox Error$
Resume BTN_RUN_REPORT_Click_Exit
 
End Sub
 
 

Thank you VERY, VERY MUCH for your time and assistance.