VBA Programming

[Alex Motilal]

Button Click Event of a form with ListBox contains the following code:


Private Sub Command6_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("QryListLots")
For Each varItem In Me!ListLots.ItemsSelected
strCriteria = strCriteria & ",'" & Me!ListLots.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM [tblLots] " & _
"WHERE [tblLots].[Lot#] IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenReport "PressWt-ShiftingWT", acViewPreview
Set db = Nothing
Set qdf = Nothing
End Sub

The Lot# is a string value. Now I want to replace it with LotID which is Long value. Please help to rewrite the code.
Thanks
Alex

[davegri]

Does this code run OK now?
Just to start, we need to know what's in strCriteria. Add debug.print strCtriteria just after the line

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

Run the code and show us a few lines from the Immediate Window.
If strCriteria is not a string of numbers like ('1','3','44') then you will need to build it that way.

[pbaldy]

Note the two different strWhere lines:

http://www.baldyweb.com/multiselect.htm

[Alex Motilal]

Thanks pbaldy,
It worked:
strWhere = strWhere & "" & ctl.ItemData(varItem) & ","
Thanks again.

[pbaldy]

Happy to help! FYI, the bit in red doesn't do anything:

strWhere = strWhere & "" & ctl.ItemData(varItem) & ","