Multiple criteria from list box

[ztm]

Hello,
I've read through this forum, and it appears many people have a similar problem. I've read the solutions offered, but can't seem to make any of them work. I need to run multiple reports (i.e. 1 report for each item selected in a listbox on a form) where the item/items selected becomes the criteria for the query. I can make the listbox work, but it only returns the last item selected, not all items selected and I can't get the listbox selection into the query.

I've tried to do something like..

stDocName = "Report"

Dim I As Long
With List1
For I = 0 To List1.ListCount
If .Selected(I) = True Then

' Here's where the trouble starts...

criteria = list1.column(0,I)
DoCmd.OpenReport stDocName, acPreview
End If
Next I

End With

 

[Nancy2]

Hope this is what you need, sorry if it isn't!

Your code above is running the report after the criteria variable is filled just once. - I assume you should print the report after the loop - and you need to build the criteria by concatenating each value returned in the loop


Here's what I use to build the criteria for everthing item selected in lstBrand. Not sure what you mean by a report for each -- assume you want to run jut one report for those item selected.


Dim ctlList As Control
Dim varItem As Variant
Dim strCriteria As String
Dim ctl As Control
Dim Itm As Variant



strCriteria = ""
Set ctlList = lstBrand

For Each varItem In ctlList.ItemsSelected

If strCriteria = "" Then
strCriteria = ctlList.ItemData(varItem)
Else
strCriteria = strCriteria & "," & ctlList.ItemData(varItem)
End If

Next varItem


Then, I build a sql string for the record source of the report and print the report

 

[Sawatzky]

Dear ztm

With List1
For I = 0 To List1.ListCount
If .Selected(I) = True Then

'Everytime you come in here you overwrite your criteria by the actual value
when you use this:
criteria = list1.column(0,I)


DoCmd.OpenReport stDocName, acPreview, ,[criteriafield]= "list1.column(0,I)
'it could be that your code is tooquick to view the report changing as Access does not open several reports but changes the one open.
msgbox("report for " & list1.column(0,I)

End If
Next I

HTH

regards Astrid

 

[ztm]

Thanks for the quick response! I've got it working... I've added an invisible textbox to the form and referenced my query criteria to the textbox. Then I wrote the selected item to the textbox, ran the report in acNormal instead of acPreview.



Dim I As Long
With List1
For I = 1 To List1.ListCount
If List1.Selected(I) = True Then
txtCriteria.Value = List1.Column(0, I)
DoCmd.OpenReport stDocName, acNormal

End If

Next

End With