Multi-Select List Box and report printing

[belovedcej]

I want to be able to print a report by limiting on particular field value via a multi-select list box.

I used thread703-565398 as a source for this, but there's obviously something I'm missing.

If I do not select anything in the list box, the report prints fine.

If I select something, however, I get the message "Object variable or with block variable not set."

Could someone tell me what I'm doing wrong? Thanks!

Dim stDocName As String
    Dim varItem As Variant
    Dim strList As ListBox

    stDocName = "rpt_IN_Services"
    
    If Me.List0.ItemsSelected.Count = 0 Then
    DoCmd.OpenReport stDocName, acPreview
        
    Else
    
    For Each varItem In Me.List0.ItemsSelected
        strList = strList & ",'" & vaItem & "'"
        Next varItem
        strList = Mid$(strList, 2)
            
    Report_rpt_IN_Services.RecordSource = "SELECT * FROM qry_IN_Services WHERE [Service] In (" & strList & ")"
    DoCmd.OpenReport stDocName, acPreview
    
    End If

 

[lameid]

The code for getting the selection itself seems odd to me but then again I haven't had to use multi-select listboxes. Since the other guy got a star, I'm rolling with it... Try this...

Dim stDocName As String
Dim varItem As Variant
Dim strList As string
Dim StlinkCriteria as string

stDocName = "rpt_IN_Services"

If Me.List0.ItemsSelected.Count = 0 Then
DoCmd.OpenReport stDocName, acPreview

Else

For Each varItem In Me.List0.ItemsSelected
strList = strList & ",'" & varItem & "'"
Next varItem

stLinkCriteria = "[Service] In (" & Mid(strList,2) & ")"
DoCmd.OpenReport stDocName, acPreview,,stLinkCriteria

End If

 

[onetalentedactor]

dim(service0 In (" Mid(strList,2) 8 )

 

[belovedcej]

Thank you for the tips -

My code now executes without a problem, but there is an error when I open the report. It just puts #Error in my first data field and then everything else in blank.

My report does have another criterion - I am entering the year. Would that affect it?

BTW - here's the code, if you are interested.

 Dim stDocName As String
    Dim varItem As Variant
    Dim strList As String
    Dim stLinkCriteria As String
    

    stDocName = "rpt_IN_Services"
    
    If Me.List0.ItemsSelected.Count = 0 Then
    DoCmd.OpenReport stDocName, acPreview
        
    Else
    
    For Each varItem In Me.List0.ItemsSelected
        strList = strList & ",'" & vaItem & "'"
        Next varItem
        strList = Mid$(strList, 2)
            
    stLinkCriteria = "[Service] IN (" & strList & ")"
        
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
    
    
    End If

 

[lameid]

It could be a number of things... I am assuming your date citeria is in your query. If you are suspicious of the date criteria, try taking it out.

I would not expect #Error if the following were true but worth considering. The code we used assumes the field Service is a text datatype. If it isn't you need to change a line of code.

strList = strList & ",'" & vaItem & "'"
would become
strList = strList & ", " & vaItem

I guess my question would be, does it run if you do not select anything in the listbox? If it does you might add a line to display your filter in the immediate window to check out if it looks weird...

debug.print stLinkCriteria

Put that line right after the line

stLinkCriteria = "[Service] IN (" & strList & ")"

Run the code and push ctl+g to see the result in the immediate window (bottom pane).

Beyond those couple of ideas, you might post the SQL of the query behind the report, that might help more.

 

[belovedcej]

Thanks for the debug tip - that's a great tool to know!

ok - I checked your suggestions.

Yes, the report runs when I select nothing.
Yes, the Service is text type.

So when I ran the debug option. . . .

turns out my strList variable isn't actual getting anything. It's just a string of one space.

Looking closely, there's a typo -
strList = strList & ",'" & vaItem & "'"

should be strList = strList & ",'" & varItem & "'"

I fixed it - ran the debug again, and now varItem is picking basically numbering my selections and calling them "1", "2", "3," etc.

So no data matches because my field is looking for a string.

Any ideas? I've never worked with variant types before, so I don't fully understand what I'm doing.

 

[lameid]

What exactly does the debug return?

debug.print stLinkCriteria


Are the values in the list values in the table where service is?