Multi Select Combo box for print function

[jparker1954]

I have a combo box that selects records for a report. I can get the report to print for individual selection or for all the selections, but I want to be able to make 2 or 3 selections (multi-select) and have all those records print on the report. Any help with the code is appreciated.

[pbaldy]

I assume you mean a list box:

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

[jparker1954]

Actually, I've got it set up with drop-down combo boxes right now.

[pbaldy]

I'm unaware of a multiselect combo box in 2003. Do you mean you have more than one combo? If so, this can work (in the query underlying the report):

http://access.mvps.org/access/queries/qry0001.htm

[jparker1954]

I have a seasonal report that I want to print. When you select the print preview or print options, a dialog box pops up that has a Combo Box for the Season and a Combo Box for the Location. If you leave the combo boxes blank it gives you all records in the report. If you choose a Season from the dropdown list and/or a Location from the dropdown list, the report is filtered by those selections. That's all working fine. HOWEVER, if it's possible, I need the added ability for someone to Select more than one season in the Season combo box. If I have to, I can probably change it to a List Box, but I'll still need it to allow for ALL if no option is selected. Would this be possible with the first option you gave me?

[jparker1954]

I just discovered the Multi-Select feature on the List Box and have set that to Simple. However I can't seem to figure out how to get those selections to filter the report. How do I set that up with the query that is used with the report.

[pbaldy]

I'd use the technique in my first link.

[jparker1954]

I'm working on my report and your technique from your first link in this series works well for multi-selections of the seasons I want, but I need to multi-select for two items, Location and Season. For example: We have several different farms, so I want to pick, say 1, 2, or 3 farms and print info on them for 1, 2 or 3 seasons. Is there a way, that I can do both in one report using the multiselect options for both criteria - say two list boxes connected to the same open report button? Or perhaps two selection options in the same list box (i.e. FarmA/Season 1, Farm A/Season 2, Farm B/Season 1, Farm B/Season 2, etc.)

Thanks!

[pbaldy]

Sure, it's just a little more tedious to write. Basically create 2 strings, 1 for each listbox, and then:

DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ") AND OtherField IN(" & strOtherString & ")"

[jparker1954]

Thanks, I came up with the following, but not working. I'm sure I've named some of the code incorrectly.

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.lstLocation.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Location"
Exit Sub
End If
'add selected values to string
Set ctl = Me.lstLocation
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items


Dim strOtherWhere As String
Dim Otherctl As Control
Dim OthervarItem As Variant
'make sure a selection has been made
If Me.lstSeason.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Season"
Exit Sub
End If
'add selected values to string
Set Otherctl = Me.lstSeason
For Each OthervarItem In Otherctl.ItemsSelected
strOtherWhere = strOtherWhere & "'" & Otherctl.ItemData(varItem) & "',"
Next OthervarItem
'trim trailing comma
strOtherWhere = Left(strOtherWhere, Len(strOtherWhere) - 1)

DoCmd.OpenReport "SeasonalInfoReport", acPreview, , "Location IN(" & strWhere & ") AND Season IN(" & strOtherWhere & ")"
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

[jparker1954]

Got it!

I just changed this line
Otherctl.ItemData(varItem) & "',"
to
Otherctl.ItemData(OthervarItem) & "',"

and it works great!

Thanks!

[pbaldy]

Great! Glad you got it sorted out.