Listbox Selections to appear on Reports

[Atoga]

Hello All,
I created reports in my database that are run by multiple selections from multiple listboxes. How can I make the selections appear on the reports in the Title or anywhere else on the report so that the user can see what criteria that actual report is based on?
Thanks!

[pbaldy]

How are you doing it? With this method:

http://www.baldyweb.com/multiselect.htm

the string could be passed in OpenArgs and displayed on the report.

[Atoga]

Thanks. I am using 5 multi select lisboxes to create the reports (and there are five type of reports). Is there an easier way? It would be quite complicated to write this code because of the number of listboxes and reports....

[June7]

Just exactly how are you using multi-select listboxes to create reports?

[Atoga]

For example I use this code to generate an Employee list based on multiple selections from 5 listboxes.

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")

If Me.lstEmployee.ItemsSelected.Count > 0 Then
Me.lstEmployee.Value = Null
End If

Dim lngRow As Long
If Me.lstDepartment.ItemsSelected.Count = 0 Then
For lngRow = 0 To lstDepartment.ListCount - 1
lstDepartment.Selected(lngRow) = True
Next
SelectAll = True
End If


If Me.lstCourseQualification.ItemsSelected.Count = 0 Then
For lngRow = 0 To lstCourseQualification.ListCount - 1
lstCourseQualification.Selected(lngRow) = True
Next
SelectAll = True
End If

If Me.lstState.ItemsSelected.Count = 0 Then
For lngRow = 0 To lstState.ListCount - 1
lstState.Selected(lngRow) = True
Next
SelectAll = True
End If

If Me.lstSkill.ItemsSelected.Count = 0 Then
For lngRow = 0 To lstSkill.ListCount - 1
lstSkill.Selected(lngRow) = True
Next
SelectAll = True
End If

'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 (CourseQualificationID) 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 (SkillID) values ('" & _
Me.lstSkill.ItemData(x) & "')")
End If
Next x

DoCmd.OpenReport "rptbyEmpl", acViewPreview, "", "", acNormal

cmdPrint_Click_Exit:
Exit Sub


cmdPrint_Click_Err:
MsgBox Error$
Resume cmdPrint_Click_Exit

End Sub

[pbaldy]

Wow, you're populating temp tables with the data? Selecting all items in a listbox if none were selected? That seems much more complicated than what I would do, but how would you present the selections when selecting all? It would seem to be very cumbersome to list all states, etc. In any case, you could populate public variables within the same loops that do the inserts, and use those variables on your report to show the selections (via functions that return the values of the variables).

[Atoga]

Yes, I was not able to find another way to create the reports based on multiple selections from multiple listboxes than with temp tables....maybe there is a simpler way...
I assume users want to see all when they don`t select any as they surely don`t want to see none (then there is no data to show at all). Instead of pop-up asking them to select at least one, I just select all instead of them (assuming they don`t want to filter on that variable).
As for presenting the selections on the reports, maybe I just leave it out...too cumbersome and yes, u are right, descriptions would be too long if for example many courses are selected...
thanks anyway!

[Atoga]

How can I make a text box on the report display `Selected` in case not all items are selected from a listbox in the form? I decided that I would like to show on the report if there have been filtering applied to that variable (i.e. not all items were selected from the listbox), but the actual filter values I don`t want to display (might be too long). On the form I have Select All buttons next to each list...so maybe instruct the textbox to display `Selected` in case the SelectAll is not True?

[June7]

Several ways to pass value to report.

1. textbox on report references textbox on form
2. form/report OpenArgs
3. TempVars