reports by multiple criteria from multiple list boxes

[Atoga]

Hi All,
I am building a database where I would run reports by multiple criteria from multiple list boxes. I set up the list boxes and wrote a code to run a report when clicking on the `Emp`button so the user see a List of Employees by State(s), Department(s), Skill(s) and Course(s)/Qualification(s). Please see below. This works fine if the user selects at least one from each list box.
I would like to modify the code so that when the user doesn`t select any from a listbox, Access assumes they selected all. Also, I would like to add a pop-up message saying `please clear all Employee selections` when they select any from the `Employee` listbox (because it is contradictory to select any as the user wants to see a List of Employees by certain criteria when clicking on the `Emp` button).
How would you add these to the code?
Cheers

Private Sub cmdEmpl_Click()


Dim x As Long
 
 ' clear out old list selections
 
 DoCmd.RunSQL ("Delete * from t_Employee")
 DoCmd.RunSQL ("Delete * from t_Department")
 DoCmd.RunSQL ("Delete * from t_State")
 DoCmd.RunSQL ("Delete * from t_CourseQualification")
 DoCmd.RunSQL ("Delete * from t_Skill")
 
 'Add a for loop here for each list box


 For x = 0 To Me.lstDepartment.ListCount - 1
        If Me.lstDepartment.Selected(x) = True Then
          DoCmd.RunSQL ("Insert into t_Department (Department) values ('" & _
          Me.lstDepartment.ItemData(x) & "')")
        End If
    Next x
       
 For x = 0 To Me.lstState.ListCount - 1
        If Me.lstState.Selected(x) = True Then
          DoCmd.RunSQL ("Insert into t_State (State) values ('" & _
          Me.lstState.ItemData(x) & "')")
        End If
    Next x
    
For x = 0 To Me.lstCourseQualification.ListCount - 1
        If Me.lstCourseQualification.Selected(x) = True Then
          DoCmd.RunSQL ("Insert into t_CourseQualification (CourseQualification) values ('" & _
          Me.lstCourseQualification.ItemData(x) & "')")
        End If
    Next x
    
 For x = 0 To Me.lstSkill.ListCount - 1
        If Me.lstSkill.Selected(x) = True Then
          DoCmd.RunSQL ("Insert into t_Skill (Skill) values ('" & _
          Me.lstSkill.ItemData(x) & "')")
        End If
    Next x
    
DoCmd.OpenReport "rptMain", acViewPreview, "", "", acNormal


cmdPrint_Click_Exit:
    Exit Sub


cmdPrint_Click_Err:
    MsgBox Error$
    Resume cmdPrint_Click_Exit


End Sub

[June7]

Have a conditional If Then Else statement. If nothing selected then use wildcard criteria else use the looping structure. Maybe don't need the RunSQL and insert to temp tables unless the selections are very numerous. Review http://www.allenbrowne.com/ser-50.html

[Atoga]

Hi June7,
I modified my code, added some IF statements after the loops, but they don`t work (pls see below). It says `compile error: wrong number of arguments or invalid property assignment.'
What I would like to reach: when the user doesn`t select any from a listbox, they would get a pop-up message ("Please select at least one.....!). Also, I would like to add a pop-up message saying `please clear all Employee selections` when they select any from the `Employee` listbox (because it is contradictory to select any as the user wants to see a List of Employees by certain criteria when clicking on the `Emp` button).

Private Sub cmdEmpl_Click()


Dim x As Long
 
 ' clear out old list selections
 
 DoCmd.RunSQL ("Delete * from t_Employee")
 DoCmd.RunSQL ("Delete * from t_Department")
 DoCmd.RunSQL ("Delete * from t_State")
 DoCmd.RunSQL ("Delete * from t_CourseQualification")
 DoCmd.RunSQL ("Delete * from t_Skill")
 
 'Add a for loop here for each list box


 For x = 0 To Me.lstDepartment.ListCount - 1
        If Me.lstDepartment.Selected(x) = True Then
          DoCmd.RunSQL ("Insert into t_Department (Department) values ('" & _
          Me.lstDepartment.ItemData(x) & "')")
        End If
    Next x
       
 For x = 0 To Me.lstState.ListCount - 1
        If Me.lstState.Selected(x) = True Then
          DoCmd.RunSQL ("Insert into t_State (State) values ('" & _
          Me.lstState.ItemData(x) & "')")
        End If
    Next x
    
For x = 0 To Me.lstCourseQualification.ListCount - 1
        If Me.lstCourseQualification.Selected(x) = True Then
          DoCmd.RunSQL ("Insert into t_CourseQualification (CourseQualification) values ('" & _
          Me.lstCourseQualification.ItemData(x) & "')")
        End If
    Next x
    
 For x = 0 To Me.lstSkill.ListCount - 1
        If Me.lstSkill.Selected(x) = True Then
          DoCmd.RunSQL ("Insert into t_Skill (Skill) values ('" & _
          Me.lstSkill.ItemData(x) & "')")
        End If
    Next x
    
If (Count(t_Employee!Employee) <> 0) Then
        Beep
        MsgBox "Please clear all EMPLOYEE selections!", vbOKOnly, ""
End If
If (Count(t_Department!Department) = 0) Then
        Beep
        MsgBox "Please select at least one DEPARTMENT!", vbOKOnly, ""
End If
If (Count(t_CourseQualification!CourseQualification) = 0) Then
        Beep
        MsgBox "Please select at least one COURSE/QUALIFICATION!", vbOKOnly, ""
End If
If (Count(t_State!State) = 0) Then
        Beep
        MsgBox "Please select at least one STATE!", vbOKOnly, ""
End If
If (Count(t_Skill!Skill) = 0) Then
        Beep
       MsgBox "Please select at least one SKILL!", vbOKOnly, ""
End If
  
DoCmd.OpenReport "rptMain", acViewPreview, "", "", acNormal


cmdPrint_Click_Exit:
    Exit Sub


cmdPrint_Click_Err:
    MsgBox Error$
    Resume cmdPrint_Click_Exit


End Sub

[June7]

If you want to force user to make selection then must exit the procedure before the OpenReport line, otherwise it will open the report regardless.

Can't use Count() function in VBA like that - it is an SQL function used in queries. What you need to do is check if the listbox has selected rows.

If Me.lstCourseQualification.ItemsSelected.Count = 0 Then

Why make the user clear the Employee selections - just clear them automatically if other items selected - that's the 'user-friendly' approach.

[Atoga]

Thanks. I have modified the code following your guidelines and it works. Thanks a lot.
I have just one last issue:
I am not sure how to put all messages into one messagebox, for example `Please select at least one State! Please select at least one Skill!` instead of having several messages popping up in case the user does not make selections.
Cheers,

[June7]

You could have a variable for the message and build it up with concatenation by each If Then. Then have the message box pop up at the end only if the variable is not empty string. Would also exit the procedure to prevent the report opening.