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.
Thank you VERY, VERY MUCH for your time and assistance.
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.
Code:
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