Incorrect syntax for dynamic Update query



I use a listbox to select either all some only some existing record (field:
"txtOptionGroup"). The selected record(s) in my listbox (cboIssue) are
then updated via command button (which executes an update query).

The SQL listed below contains some incorrect syntax. Pls keep in mind the
the TrackNoIDpk is of datatype = AutoNumber.

The error indicates: Run-time error 3075. Syntax error (missing operator)
in query expression 'TrackNoIDpk IN (& strCriteria & )'.

What's wrong w/ the syntax? When clicking on Debug, the line "qdf.SQL =
strSQL" is highlighted.


Private Sub UpdateTrackNo_Click()

'Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

'Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("00_qryUpdateTrackNoID")

'Loop through the selected items in the list box and build a text string
If Me!cboIssue.ItemsSelected.Count > 0 Then
For Each varItem In Me!cboIssue.ItemsSelected
strCriteria = strCriteria & "" & Chr(34) &
Me!cboIssue.ItemData(varItem) & Chr(34) & ","
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 1)
strCriteria = "tblIssues.TrackNoIDpk Like *"
End If

'Now update txtOptionGroup
strSQL = "UPDATE tblIssues SET tblIssues.txtOptionGroup = '1' WHERE
TrackNoIDpk IN ( & strCriteria & )"

'Apply the new SQL statement to the query
qdf.SQL = strSQL

'Execute strSQL
CurrentDb().Execute strSQL, dbFailOnError

'Empty the memory
Set db = Nothing
Set qdf = Nothing

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question