Run time error 2001

[jmd]

this code collects entries from a listbox and sends it to a query. I get a
run-time error 2001 You cancelled the previous operation. Debug references
line

DoCmd.OpenQuery "qryPayroll_MultiSelect"

Here's the entire code below

Private Sub cmdOK_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("qryPayroll_MultiSelect")
For Each varItem In Me!lboxPayPeriod.ItemsSelected
strCriteria = strCriteria & "tblPayPeriod.MPPID = " & Chr(34) _
& Me!lboxPayPeriod.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
strSQL = "SELECT * from tblPayPeriod " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryPayroll_MultiSelect"
Set db = Nothing
Set qdf = Nothing
End Sub

Any help would be greatly appreciated.

 

[Wayne Morgan]

How many columns are in the list box? The value you are inserting in the
WHERE clause will come from the Bound Column of the list box. If you want
the value from a different column, you'll need to specify the column. It
appears that the value you are intending to insert is a text value, is this
correct?

While the ORs should work, it may be easier to use an IN statement instead.

Example:
WHERE tblPayPeriod.MPPID IN (value1, value2, value3)
or, if text
WHERE tblPayPeriod.MPPID IN ("value1", "value2", "value3")

Yes, you can have a single item in the IN statement.

 

[jmd]

I re-checked the listbox and it's bound to the correct column, however it is
a number (long integer) value not text. How would I adjust the code to
account for number instead of text?

I also changed the strCriteria code to add commas between the collected
values and adjusted the strSQL code to "string" out the right SQL text for
the query, same error message, Run Time Error 2001 You canceled the previous
operation. I'm pasting the adjusted code below.

For Each varItem In Me!lboxPayPeriod.ItemsSelected
strCriteria = strCriteria & Chr(34) _
& Me!lboxPayPeriod.ItemData(varItem) & Chr(34) & ","
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM tblPayPeriod " & _
"WHERE tblPayPeriod.MPPID IN(" & strCriteria & ");"

 

[Douglas J. Steele]

Remove the Chr(34)

 

[Wayne Morgan]

As you can see from the two examples I gave for the IN statement, text has
quotes around it, numbers don't. The Chr(34) is putting quotes around the
values.

 

[jmd]

worked.. thank you so much

As you can see from the two examples I gave for the IN statement, text has
quotes around it, numbers don't. The Chr(34) is putting quotes around the
values.