IN operator - error '3075'

[knownote]

Hello all,

Am getting an error using code:

Run-time error '3075':
Syntax error (missing operator) in query expression '[CustomerID] IN ({selected items})

I have extended selection turned on; I can select listbox results with shift, control, but
when press spacebar to open selected records, error occurs.


for this form I downloaded:

http://www.mvps.org/access/forms/frm0045.htm

... ad-hoc search ... which generates dynamic SQL and assigns it to a listbox on the
form to show the results. The form "frmSearch"

Limitations:

The form only allows searches on non-system tables and non-action, non-parameter queries.

A maximum of five fields (duplicates inclusive) can be used in one SQL statement. However, by selecting the "Edit SQL"

checkbox, additional criteria can be typed directly in the SQL textbox.

Multiple criteria can only be grouped together (using parenthesis) by directly editing the SQL textbox.



---- code

Private Sub lstResult_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode

Case 32 ' vbKeySpace key
KeyCode = 32 ' this is the "revaluation"
' that kills the escape key action
'Open frmCustomer based on the ID from lstResult listbox

Dim stDocName As String
stDocName = "frmCustomer"
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String

strList = ""
Set frm = Forms!frmSearch
Set ctl = frm!lstResult
For Each varItm In ctl.ItemsSelected
strList = strList & ctl.ItemData(varItm) & ", "
'Debug.Print ctl.ItemData(varItm)
Next varItm
strList = Left(strList, Len(strList) - 2)
DoCmd.OpenForm stDocName, acNormal, , "[CustomerID] IN (" & strList & ")"

Case Else ' just in case you want to
' take a look at the key code generated
' by other keystrokes
'MsgBox KeyCode ' comment this out when it annoys you
End Select

End Sub

 

[JPMontreal]

The apostrophes are missing for the string.
...
strList = Left(strList, Len(strList) - 2)
DoCmd.OpenForm stDocName, acNormal, , "[CustomerID] IN (‘" & strList & "’)"

...


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[Golom]

Or possibly from the individual values within the string

strList = strList & [COLOR=red]"'" & [/color]ctl.ItemData(varItm) & "[COLOR=red]'[/color], "

 

[knownote]

Golom

Your suggestion got closer to working. Another error is
occurring that involves the Where in opening the results form.

run-time error '2501':
The OpenForm action was canceled.

You used a method of the DoCmd object to carry out an action in Visual Basic, but
then clicked Cancel in a dialog box.

For example, you used the Close method to close a changed form, then clicked Cancel
in the dialog box that asks if you want to save the changes you made to the form.