Getting a Syntax error in INSERT INTO statement

Question

I need help figuring out why I'm getting this syntax error when trying to run this code in Microsoft Access. I am working on this database for work and have some experience with it, but not a whole lot.

I created a database before the one I'm currently working on and it works fine. It is very similar to the database that I'm working on now, so I just copied it and then renamed the fields and text boxes and everything to match the information that this database will be handling.

Essentially I have a table with the data and then I want a form that has a text box for each field in the table with a subtable of the main table incorporated into the form. The user fills in the text boxes with the information and then either adds it to the table or they can click on a record and edit it or delete it with the corresponding buttons on the form.

Now I'm getting a runtime error '3134': syntax error in INSERT INTO statement when trying to click the add button on one of my forms. This only happens on the add button and consequently the update button if it is set to update, but all of the other buttons work fine.

Here is the code for the new database:

Option Compare Database

Private Sub cmdAdd_Click()
    'when we click on button Add there are two options
    '1. for insert
    '2. for update
    If Me.txtICN.Tag & "" = "" Then
        'this is for insert new
        'add data to table
        CurrentDb.Execute "INSERT INTO tblInventory(ICN, manu, model, serial, desc, dateRec, dateRem, dispo, project, AMCA, UL, comments) " & _
        " VALUES(" & Me.txtICN & ", '" & Me.txtManu & "', '" & Me.txtModel & "', '" & Me.txtSerial & "', '" & Me.txtDesc & "', '" & Me.txtDateRec & "', '" & Me.txtDateRem & "', '" & Me.txtDispo & "', '" & Me.txtProject & "', '" & Me.txtAMCA & "', '" & Me.txtUL & "', '" & Me.txtComments & "')"
    Else
        'otherwise (Tag of txtICN store the ICN of item to be modified)
        CurrentDb.Execute "UPDATE tblInventory " & _
        " SET ICN = " & Me.txtICN & _
        ", manu = '" & Me.txtManu & "'" & _
        ", model = '" & Me.txtModel & "'" & _
        ", serial = '" & Me.txtSerial & "'" & _
        ", desc = '" & Me.txtDesc & "'" & _
        ", dateRec = '" & Me.txtDateRec & "'" & _
        ", dateRem = '" & Me.txtDateRem & "'" & _
        ", dispo = '" & Me.txtDispo & "'" & _
        ", project = '" & Me.txtProject & "'" & _
        ", AMCA = '" & Me.txtAMCA & "'" & _
        ", UL = '" & Me.txtUL & "'" & _
        ", comments = '" & Me.txtComments & "'" & _
        " WHERE ICN = " & Me.txtICN.Tag
    End If

    'clear form
    cmdClear_Click
    'refresh data in list on form
    frmInventorySub.Form.Requery
End Sub

Private Sub cmdClear_Click()
    Me.txtICN = ""
    Me.txtManu = ""
    Me.txtModel = ""
    Me.txtSerial = ""
    Me.txtDesc = ""
    Me.txtDateRec = ""
    Me.txtDateRem = ""
    Me.txtDispo = ""
    Me.txtProject = ""
    Me.txtAMCA = ""
    Me.txtUL = ""
    Me.txtComments = ""

    'focus on ID text box
    Me.txtICN.SetFocus
    'set button edit to enable
    Me.cmdEdit.Enabled = True
    'change caption of button add to Add
    Me.cmdAdd.Caption = "Add"
    'clear tag on txtICN for reset new
    Me.txtICN.Tag = ""
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub cmdDelete_Click()
    'delete record
    'check existing selected record
    If Not (Me.frmInventorySub.Form.Recordset.EOF And Me.frmInventorySub.Form.Recordset.BOF) Then
        'confirm delete
        If MsgBox("Are you sure you want to delete this item?", vbYesNo) = vbYes Then
            'delete now
            CurrentDb.Execute "DELETE FROM tblInventory " & _
                "WHERE ICN =" & Me.frmInventorySub.Form.Recordset.Fields("ICN")
            'refresh data in list
            Me.frmInventorySub.Form.Requery
        End If
    End If
End Sub

Private Sub cmdEdit_Click()
    'check whether there exists data in list
    If Not (Me.frmInventorySub.Form.Recordset.EOF And Me.frmInventorySub.Form.Recordset.BOF) Then
        'get data to text box control
        With Me.frmInventorySub.Form.Recordset
            Me.txtICN = .Fields("ICN")
            Me.txtManu = .Fields("manu")
            Me.txtModel = .Fields("model")
            Me.txtSerial = .Fields("serial")
            Me.txtDesc = .Fields("desc")
            Me.txtDateRec = .Fields("dateRec")
            Me.txtDateRem = .Fields("dateRem")
            Me.txtDispo = .Fields("dispo")
            Me.txtProject = .Fields("project")
            Me.txtAMCA = .Fields("AMCA")
            Me.txtUL = .Fields("UL")
            Me.txtComments = .Fields("comments")
            'store id of item in Tag of txtICN in case ICN is modified
            Me.txtICN.Tag = .Fields("ICN")
            'change caption of button add to Update
            Me.cmdAdd.Caption = "Update"
            'disable button edit
            Me.cmdEdit.Enabled = False
        End With
    End If
End Sub

And here is the code for the old database:

Option Compare Database

Private Sub cmdAdd_Click()
    'when we click on button Add there are two options
    '1. for insert
    '2. for update
    If Me.txtID.Tag & "" = "" Then
        'this is for insert new
        'add data to table
        CurrentDb.Execute "INSERT INTO tblEquipmentList(equipID, equipDesc, equipManu, equipModelNum, equipSerNum, lastCalDate, calDue) " & _
        " VALUES(" & Me.txtID & ", '" & Me.txtDesc & "', '" & Me.txtManu & "', '" & Me.txtModelNum & "', '" & Me.txtSerNum & "', '" & Me.txtLastCalDate & "', '" & Me.txtCalDueDate & "')"
    Else
        'otherwise (Tag of txtID store the id of equipment to be modified)
        CurrentDb.Execute "UPDATE tblEquipmentList " & _
        " SET equipID = " & Me.txtID & _
        ", equipDesc = '" & Me.txtDesc & "'" & _
        ", equipManu = '" & Me.txtManu & "'" & _
        ", equipModelNum = '" & Me.txtModelNum & "'" & _
        ", equipSerNum = '" & Me.txtSerNum & "'" & _
        ", lastCalDate = '" & Me.txtLastCalDate & "'" & _
        ", calDue = '" & Me.txtCalDueDate & "'" & _
        " WHERE equipID = " & Me.txtID.Tag
    End If

    'clear form
    cmdClear_Click
    'refresh data in list on form
    frmEquipmentListSub.Form.Requery
End Sub

Private Sub cmdClear_Click()
    Me.txtID = ""
    Me.txtDesc = ""
    Me.txtManu = ""
    Me.txtModelNum = ""
    Me.txtSerNum = ""
    Me.txtLastCalDate = ""
    Me.txtCalDueDate = ""

    'focus on ID text box
    Me.txtID.SetFocus
    'set button edit to enable
    Me.cmdEdit.Enabled = True
    'change caption of button add to Add
    Me.cmdAdd.Caption = "Add"
    'clear tag on txtID for reset new
    Me.txtID.Tag = ""
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub cmdDelete_Click()
    'delete record
    'check existing selected record
    If Not (Me.frmEquipmentListSub.Form.Recordset.EOF And Me.frmEquipmentListSub.Form.Recordset.BOF) Then
        'confirm delete
        If MsgBox("Are you sure you want to delete this piece of equipment?", vbYesNo) = vbYes Then
            'delete now
            CurrentDb.Execute "DELETE FROM tblEquipmentList " & _
                "WHERE equipID =" & Me.frmEquipmentListSub.Form.Recordset.Fields("equipID")
            'refresh data in list
            Me.frmEquipmentListSub.Form.Requery
        End If
    End If
End Sub

Private Sub cmdEdit_Click()
    'check whether there exists data in list
    If Not (Me.frmEquipmentListSub.Form.Recordset.EOF And Me.frmEquipmentListSub.Form.Recordset.BOF) Then
        'get data to text box control
        With Me.frmEquipmentListSub.Form.Recordset
            Me.txtID = .Fields("equipID")
            Me.txtDesc = .Fields("equipDesc")
            Me.txtManu = .Fields("equipManu")
            Me.txtModelNum = .Fields("equipModelNum")
            Me.txtSerNum = .Fields("equipSerNum")
            Me.txtLastCalDate = .Fields("lastCalDate")
            Me.txtCalDueDate = .Fields("calDue")
            'store id of equipment in Tag of txtID in case id is modified
            Me.txtID.Tag = .Fields("equipID")
            'change caption of button add to Update
            Me.cmdAdd.Caption = "Update"
            'disable button edit
            Me.cmdEdit.Enabled = False
        End With
    End If
End Sub

As you can see, I'm pretty confident that they're pretty much identical except for the field names.

I'm also linking an album of screenshots of the database here: http://imgur.com/a/xLV3Q

Thanks for any help you guys can provide.

Answer 1

The problem may be: Your new table tblInventory has a column DESC which is a SQL reserved keyword. You have two options:

1. Drop the column DESC and create a new column with different name OR;
2. Add brackets to your script like this: INSERT INTO tblInventory([ICN], [manu], [model], [serial], [desc], [dateRec], [dateRem], [dispo], [project], [AMCA], [UL], [comments]).

Please check a full list of SQL reserved keywords: Reserved Keywords-Transact-SQL