Can't get Multiselect listbox code to work for report

I am having trouble getting the procedure to use a Multiselect listbox in a
report to work. I am using Access 2000.

Here's the code. The error I get is QueryDef "User-defined type not
defined".

Private Sub CmdReport_Click()
On Error GoTo Err_CmdReport_Click

Dim stDocName, stSQL, stWhat, stCriteria As String
Dim varItem As Variant
Dim qdfTemp As QueryDef

stWhat = ""
stCriteria = ","

For Each varItem In Me!List1.ItemsSelected
stWhat = stWhat & "'" & Me!List1.ItemData(varItem) & "'"
stWhat = stWhat & stCriteria
Next varItem

Me!HiddenTextBox = CStr(Left$(stWhat, Len(stWhat) -
Len(stCriteria)))
Set qdfTemp = CurrentDb.QueryDefs("qryInventory")
stSQL = "SELECT Position, Line, Station, Status "
stSQL = stSQL & "FROM tblInventory WHERE Position"
stSQL = stSQL & " IN (" & Me!HiddenTextBox & ")"
qdfTemp.SQL = stSQL
qdfTemp.Close

stDocName = "tblInventory"

DoCmd.OpenReport stDocName, acPreview, qryInventory
Exit_CmdReport_Click:
Exit Sub

Err_CmdReport_Click:
MsgBox Err.Description
Resume Exit_CmdReport_Click

End Sub

Please advise and thanks, much!!

Bill Walsh

[Salad]

wa********@sbcglobal.net wrote:

I am having trouble getting the procedure to use a Multiselect listbox in a
report to work. I am using Access 2000.

Here's the code. The error I get is QueryDef "User-defined type not
defined".

Private Sub CmdReport_Click()
On Error GoTo Err_CmdReport_Click

Dim stDocName, stSQL, stWhat, stCriteria As String
Dim varItem As Variant
Dim qdfTemp As QueryDef

stWhat = ""
stCriteria = ","

For Each varItem In Me!List1.ItemsSelected
stWhat = stWhat & "'" & Me!List1.ItemData(varItem) & "'"
stWhat = stWhat & stCriteria
Next varItem

Me!HiddenTextBox = CStr(Left$(stWhat, Len(stWhat) -
Len(stCriteria)))
Set qdfTemp = CurrentDb.QueryDefs("qryInventory")
stSQL = "SELECT Position, Line, Station, Status "
stSQL = stSQL & "FROM tblInventory WHERE Position"
stSQL = stSQL & " IN (" & Me!HiddenTextBox & ")"
qdfTemp.SQL = stSQL
qdfTemp.Close

stDocName = "tblInventory"

DoCmd.OpenReport stDocName, acPreview, qryInventory
Exit_CmdReport_Click:
Exit Sub

Err_CmdReport_Click:
MsgBox Err.Description
Resume Exit_CmdReport_Click

End Sub

Please advise and thanks, much!!

Bill Walsh

It might help if you put in a comma between each element.
Example
stWhat = stWhat & stCriteria & ", "
and at the end of adding elements, remove the comma and space.

Hint: When you have created a SQL string that fails, enter
Debug.Print yourSQLStringVariable
Then run, then go into the Debug/Immediate window and copy the SQL
string. Open a new query in design mode, don't add any tables, and go
into SQL view. Paste the SQL into the window and try to run. Your
error will most likely be very evident.