Using MultiSelect List Box to pull records of selected IDs from a report

[Jay]

Hey guys,
I am beginner in this world of VB. So please bare with me if I ask
stupid questions. What I'm trying to do is: I have a query that feeds
the list box (Multi-Select) on a form. Clicking of the button triggers
the code. I want my code to take the items selected (in my case unique
identifier is ProjNum) from the list, and preview records (from the
report) that has ProjNum (project numbers) selected on the list. Below
is the code I'm running:

Option Compare Database
Option Explicit

Private Sub cmdPrint_Click()
Dim varSelected As Variant
Dim strSQL As String

For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[ProjNum] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"

DoCmd.OpenReport "rpt-ByProject", acViewPreview, , strSQL
End If
End Sub

When I run this code, ti gives Run-time error '3464' Data type mismatch
in criteria expression.

But when I used this DoCmd inside the if statement,
MsgBox (strSQL)
DoCmd.OpenReport "rpt-ByProject", acViewPreview
DoCmd.RunCommand acCmdFitToWindow

I get a pop-up box that says [ProjNum] IN (563) with a OK button below
it. When I click the 'OK' button it pulls the entire report with all
the project numbers.
I think my code is working backwards because it is supposed to look for
(563) in ProjNum column and pull a record from the report that
corresponds to that particular ProjNum (project number).

I have Bound column = 1, column 1 is my ProjNum column, which uniquely
identifies each of the projects in my database.

Any suggestions from you guys will be greatly appreciated!!!

 

[Douglas J. Steele]

What's the data type of ProjNum? If it's a text field, you need to use:

strSQL = strSQL & "'" & Me!lstSelect.ItemData(varSelected) & "',"

Repeated for clarity, that's

strSQL = strSQL & " ' " & Me!lstSelect.ItemData(varSelected) & " ' ,"

 

[Jay]

Doug,
Thanks for your response. Data type of ProjNum is text, but when I
added those single quotes it gave me a "compile error: Syntax error",
with 'Private Sub cmdPrint_Click()' highlighted.

Jay

 

[Douglas J. Steele]

If that line is being highlighted, there must be some other issue. Adding
the quotes definitely wouldn't have caused the declaration line to raise a
syntax error: that would occur elsewhere if there was going to be a problem.

Did you perhaps add some text around that line as well?

 

[Jay]

Doug
Thanks for your response again. I used a different set of code to
make it work. I appreciate your help!!!