Access VBA - Identifying text

问题:

Im trying to create a button to delete certain records in a subform. However im getting "syntax error (missing operator) in query expression 'KEY_ID="1'.

I know what the problem is: The attribute is text therefore the value needs to be surrounded by single quotes. I just don't know how to write the VBA to accomplish this.

Private Sub cmdDelete_Click()
If Not (Me.subKey.Form.Recordset.EOF And Me.subKey.Form.Recordset.BOF) Then
    If MsgBox("Confirm Deletion?", vbYesNo) = vbYes Then
        Dim strSql As String
        strSql = "DELETE FROM KEYS" & _
            " WHERE KEY_ID='" & Me.subKey.Form.Recordset.Fields("KEY_ID")
        Debug.Print strSql ' <- prints to Immediate window
        CurrentDb.Execute strSql, dbFailOnError

    End If
End If

回答1:

strSql = "DELETE FROM KEYS" & _
    " WHERE KEY_ID='" & Me.subKey.Form.Recordset.Fields("KEY_ID") & "'"

... though, mind you, using the quote as an ASCII character is safer, or at least more versatile or portable, when these kinds of things get more intensive.

strSql = "DELETE FROM KEYS" & _
    " WHERE KEY_ID=" & _
    Chr(32) & Me.subKey.Form.Recordset.Fields("KEY_ID") & Chr(32)

I'm not sure it's Chr(32) but I think that is correct.