multi-select list box

[vickiwells]

I have a report based on a table, not a query.I need a dialog box to filter it from. I found an example in the Developer's handbook, but can't get it to work for more than the first selection. Could someone check this to see what's wrong? I'm using 2000.

Private Sub cmdPreview_Click()
Dim vntItem As Variant, strFilter As String
Dim rptMyReport As New Report_Ledger

For Each vntItem In Me!List0.ItemsSelected
' Concatenate single quotes around the Job #
' because the Job # field is Text.
strFilter = strFilter & "[Job #] = '" & _
Me![List0].ItemData(vntItem) & "' OR "
Next
' Remove the OR string from the end of the filter
' condition if a filter exists.
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
' Preview the report, and pass the filter string
DoCmd.OpenReport rptMyReport.Name, acPreview, , strFilter
End Sub

I'm not sure what the part at the bottom is for-where it's got the -4. Do I need it? My filtered field is a text field, but I don't know what the code is doing there. Also, it won't let me put brackets around "Report_Ledger" in the dimReport MyReport line.

 

[DALeffler]

Hi,

I'm assuming the report you want to see based on the filter is "Report_Ledger"?

And I'm assuming you're dialog box is a form with a list box on it & the Name property of that list box is "List0"?

And you want to select items in that list box and have the report, "Report_Ledger" previewed based on the values selected from the "List0" listbox on the form?

If you answer yes to all the above, one more question: What's the Bound Column property of "List0" set to?

I'm using '97 and the reason I'm asking all these questions is because I tried the code listing in your post with a quick'n'dirty form, table and report and got it to work with a couple minor code modifications, and the first error message I got was on:

Dim rptMyReport As New Report_Ledger

So's I left it out.

But with a little trial & error, I did get the following Sub to work OK:

Private Sub cmdPreview_Click()

Dim vntItem As Variant, strFilter As String

For Each vntItem In Me!List0.ItemsSelected
' Single quotes needed only when the bound column data is text -
'if the bound column data is a number, no quotes needed...
strFilter = strFilter & "mytblColName =" & _
Me![List0].ItemData(vntItem) & " OR "
Next
' Remove the OR string from the end of the filter
' condition if a filter exists - ya have to have this...
If strFilter <> "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If
' Preview the report, and pass the filter string
DoCmd.OpenReport "Report_Ledger", acPreview, , strFilter

End Sub

Hope this helps!

Doug.

PS: I'm a beginner here - try my code on a backup copy!

Thanks for the question - I learned a few things! Please forgive if help = Null...

 

[vickiwells]

Thanks for the reply! While I was waiting, I made some design changes to my report, and when I tried the filter this morning, it worked! I don't know what fixed it, must have been something in my report. At least you learned something out of it, though, so it wasn't a total waste of time.