asked on

Access VBA Run-time error '3134': Syntax error in INSERT INTO statement

Hi all-

I'm modifying a database that somebody else created & am getting a syntax error when trying to run this code to either update existing records or add new ones. The WorkerID is the primary key/autonumber & is exclusive to each record- it does not allow duplicates. There is an unbound form that when double clicked, a sub pop-up form comes up with each field for the specific record selected on the main form. The user can then make changes to the specific user selected & click save. This code is for the save button & either adds the record, if it's new (that is if the WorkerID doesn't already exist), or it modifies the existing selected record, if it does exist.

So, in Module1, we have the following code:

Sub RunSQL(strSQL As String)
    'turn off the warnings
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL strSQL
    'DoCmd.Requery
    
    'turn warnings on
    DoCmd.SetWarnings True
End Sub

Open in new window

Then, the code we have in the form for the save button is:

Private Sub Save_Button_Click()
Dim lngWorkerID As Long
DoCmd.SetWarnings False

    If lngWorkerID <> 0 Then
        RunSQL "UPDATE tblCallTree SET " & _
        "FirstName = """ & Me.FirstName & """," & _
        "LastName = """ & Me.LastName & """," & _
        "HomePhone = """ & Me.HomePhone & """," & _
        "CellPhone = """ & Me.CellPhone & """," & _
        "Pager = """ & Me.Pager & """," & _
        "Extension = """ & Me.Extension & """," & _
        "Email = """ & Me.Email & """," & _
        "Fax = """ & Me.Fax & """," & _
        "ManagerID = " & Me.Manager & "," & _
        "IsManager = " & Me.IsManager & "," & _
        "Updated? = True, " & _
        "WHERE WorkerID = " & lngWorkerID
    Else
        RunSQL "INSERT INTO tblCallTree (FirstName, LastName, HomePhone, CellPhone, Pager, Extension, Email, Fax, ManagerID, IsManager, Updated?) VALUES ( " & _
        """" & Me.FirstName & """," & _
        """" & Me.LastName & """," & _
        """" & Me.HomePhone & """," & _
        """" & Me.CellPhone & """," & _
        """" & Me.Pager & """," & _
        """" & Me.Extension & """," & _
        """" & Me.Email & """," & _
        """" & Me.Fax & """," & _
        Me.Manager & "," & _
        Me.IsManager & "," & _
        True & ")"

    End If
    DoCmd.SetWarnings False

    DoCmd.Close acForm, "frmEditContact_LiveCallTree", acSaveYes
    [Forms]![frmLiveCallTree]![Contacts_List].Requery
    
DoCmd.SetWarnings True
End Sub

Open in new window

Can somebody please tell me where I'm going wrong to be giving me this run-time error when run? Thanks in advance for your help!

ASKER CERTIFIED SOLUTION

hielo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

AmitGK

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daisy80

ASKER

Woohoo!!! It was both- I needed to add brackets to both Updated? as well as remove that extra comma- can't believe i missed that! Thank you all so much for your help & quick responses!!!

Daisy80

ASKER

All- I like to briefly revisit this as it's a learning opportunity for me. I now need to modify this code & I'm trying to understand how exactly it's doing what it's doing. I understand the whole INSERT INTO is appending it, my question is under the values part where the actual fields are that are being appended to the table. Can someone explain to me the significance of the quotation marks are "" ? Because in my efforts to update it, it keeps bombing out & it's b/c I don't really have a full understanding of the correct syntax that's needed. I did some google searches, but no examples I found really include the "". Can someone help explain in laymen terms how to accurately write/update this code? Thanks as always for your help!!

RunSQL "INSERT INTO tblCallTree (FirstName, LastName, HomePhone, CellPhone, Pager, Extension, Email, Fax, ManagerID, IsManager, [Updated?]) VALUES ( " & _
        """" & Me.FirstName & """," & _
        """" & Me.LastName & """," & _
        """" & Me.HomePhone & """," & _
        """" & Me.CellPhone & """," & _
        """" & Me.Pager & """," & _
        """" & Me.Extension & """," & _
        """" & Me.Email & """," & _
        """" & Me.Fax & """," & _
        Me.Manager & "," & _
        Me.IsManager & "," & _
        True & ")"

Open in new window