Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: SQL Statement giving "Syntax error insert into statement"

  1. #1
    New Member
    Join Date
    Sep 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SQL Statement giving "Syntax error insert into statement"

    First, I want to make everyone aware that I'm new to VBA, SQL, AND Access. Our data guy let suddenly so I've been given the job of maintaining my organizations databases even though my background is in Educational research. So I'm sorry if I give too much/not enough information and if my code is all wrong.

    Basically, I'm maintain a database where individuals enter info into a form about classroom make up. The person who set up the database used VBA to enter information from the form into the table. I was asked to add additional data points which include the teacher's and TA's names and certifications. teacher's names are collected through a text box and certifications are collected through a Y/N text box.

    I'm trying to mirror what his code was and asked for some help from him but he confused me with the types of variables. Any way now when I click the add information on the form I get a message that states "Syntax error: INSERT INTO statement" and the statement DoCmd.RunSQL (sql) gets highlighted in yellow

    If any one could please help me (and maybe direct me to some information on all of this) I would be super grateful.
    Again, sorry for any mistakes.

    Code:
     Dim i As Integer
     Dim loop_ctr As Integer
     Dim sql As String
     Dim VISIT_ID_FK As Integer
     Dim classtype As String
     Dim classdur As String
     Dim classlang1 As String
     Dim classlang2 As String
     Dim classgrp As String
     Dim teach1 As String
     Dim tcert1 As Integer
     Dim teach2 As String
     Dim tcert2 As Integer
     Dim ta1 As String
     Dim tacert1 As Integer
     Dim ta2 As String
     Dim tacert2 As Integer
     Dim ta3 As String
     Dim tacert3 As Integer
     Dim upkscis As String
     Dim strUser As String
     Dim dtmDate As Date
    
     'Remember that the NetworkUserName() function is
     'in the ajbFieldLevel module
     loop_ctr = Me.txt_class_num.Value
     VISIT_ID_FK = Me.tbox_visitid.Value
     classtype = Me.cbo_class_type.Column(0)
     classdur = Me.cbo_class_duration.Column(0)
     classlang1 = Me.cbo_class_lang_1.Column(0)
     classlang2 = Me.cbo_class_lang_2.Column(0)
     classgrp = Me.cbo_class_group.Column(0)
     upkscis = Me.cbo_upk_scis.Column(0)
     teach1 = Me.txt_teacher1.Value
     tcert1 = Me.ck_tcert.Value
     teach2 = Me.txt_teacher2.Value
     tcert2 = Me.ck_tcert2.Value
     ta1 = Me.txt_TA1.Value
     tacert1 = Me.ck_ta_cert1.Value
     ta2 = Me.txt_TA2.Value
     tacert2 = Me.ck_ta_cert2.Value
     ta3 = Me.txt_TA3.Value
     tacert3 = Me.ck_ta_cert3.Value
     strUser = NetworkUserName()
     dtmDate = Now()
     'build out sql statement
     sql = "INSERT INTO tbl_4410_class_data" & vbCrLf & _
          "(VISIT_ID_FK, Class_Type, Class_Duration, Class_Language_1, Class_Language_2," & vbCrLf & _
          " Class_Group_Size, UPK_In_SCIS_FL," & vbCrLf & _
          " Teacher_1, Teacher_1_Certification" & vbCrLf & _
          " Teacher_2, Teacher_2_Certification" & vbCrLf & _
          " TA_1, TA_1_Certification, TA_2, TA_2_Certification," & vbCrLf & _
          " TA_3, TA_3_Certification" & vbCrLf & _
          " EnteredBy, EnteredOn)" & vbCrLf & _
          " Values ('" & VISIT_ID_FK & "', '" & classtype & "', '" & classdur & "', " & vbCrLf & _
          "'" & classlang1 & " ', '" & classlang2 & "', '" & classgrp & "', '" & upkscis & "', " & vbCrLf & _
          "'" & teach1 & "', ' & tcert1 & ', '" & teach2 & "', ' & tcert2 & ', " & vbCrLf & _
          "'" & ta1 & "', ' & tacert1 & ', '" & ta2 & "', ' & tacert2 & ', " & vbCrLf & _
          "'" & ta3 & "', ' & tacert3 & ', '" & strUser & "', #" & dtmDate & "#)"
    
     For i = 1 To loop_ctr
         DoCmd.RunSQL (sql)

  2. #2
    New Member
    Join Date
    Aug 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL Statement giving "Syntax error insert into statement"

    The first thing I see is you have put line controls (vbcrlf) into your sql statement it shoud be more like this:
    Code:
    SQL = "INSERT INTO tbl_4410_class_data " & _
                      "(VISIT_ID_FK, Class_Type, "Class_Duration, Class_Language_1, Class_Language_2 " & _
                      " and so on "
    The second thing is After the VALUES you have quotes around all of the variables, this in only for strings, not integers. For Example
    Code:
    "VALUES(" & VISIT_ID_FK & ", '" & ClassType & "', "
    Last edited by baderms; Sep 14th, 2017 at 06:49 AM.

  3. #3
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    793
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL Statement giving "Syntax error insert into statement"

    The third thing is all field names and also values must be separated by comma. You are missing commas after the following field names:

    Teacher_1_Certification
    Teacher_2_Certification
    TA_3_Certification

    Edit:
    There is also more actually. You are missing double quotes in the values section: starting with tcert1.

    Edit2:
    Some corrections for the mentioned problems in my post. Basically both field names and field values are separated with comma, make sure each field name has a corresponding value, and make sure using double quotes to inject variables, otherwise those will be inserted into the string as "string".

    Code:
    Sql = "INSERT INTO tbl_4410_class_data" & vbCrLf & _
          "(VISIT_ID_FK, Class_Type, Class_Duration, Class_Language_1, Class_Language_2," & _
          " Class_Group_Size, UPK_In_SCIS_FL," & _
          " Teacher_1, Teacher_1_Certification," & _
          " Teacher_2, Teacher_2_Certification," & _
          " TA_1, TA_1_Certification, TA_2, TA_2_Certification," & _
          " TA_3, TA_3_Certification," & _
          " EnteredBy, EnteredOn)" & vbCrLf & _
          " VALUES " & vbCrLf & _
          " ('" & VISIT_ID_FK & "', '" & classtype & "', '" & classdur & "', " & _
          "'" & classlang1 & "', '" & classlang2 & "', '" & classgrp & "', '" & upkscis & "', " & _
          "'" & teach1 & "', '" & tcert1 & "', '" & teach2 & "', '" & tcert2 & "', " & _
          "'" & ta1 & "', '" & tacert1 & "', '" & ta2 & "', '" & tacert2 & "', " & _
          "'" & ta3 & "', '" & tacert3 & "', '" & strUser & "', #" & dtmDate & "#)"
    Last edited by smozgur; Sep 14th, 2017 at 07:03 AM. Reason: more info

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,357
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL Statement giving "Syntax error insert into statement"

    Here is a little debugging tip.

    You are creating a complex SQL statement in VBA. There are a number of things that you might have done wrong. A lot of times, it can be difficult to see by looking at the VBA code. However, if you view the actual SQL code you create, a lot of times the issues will be evident.

    Before you actually run the SQL code, just add this line which will return it to a Message Box:
    Code:
    MsgBox sql
    Then inspect the actual sql code that it created. Does it look correct?

    What I often do is manually create the same query using the Query Builder. Once you get it to work properly, switch to SQL View and inspect the code. This is the code that you want to build with VBA.
    You can easily compare that to what you created using the method I just described.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,433
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL Statement giving "Syntax error insert into statement"

    The first thing I see is you have put line controls (vbcrlf) into your sql statement it shoud be more like this:
    Note that having the newlines in the sql string is actually useful for debugging the sql because you can view it more easily when you print it out.
    And as per Joe's note above - always print it out and look at it when you have a complex sql string that doesn't work as expected:

    Code:
    sql = sql & _
    ...
    ...
    ...
    MsgBox sql
    'or
    Debug.Print sql
    
    Last edited by xenou; Sep 14th, 2017 at 09:48 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    New Member
    Join Date
    Sep 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL Statement giving "Syntax error insert into statement"

    I want to thank you all for your help. Turns out that I was missing some commas and some misplaced double quotes like smozgur & baderms. Can't believe that was it! Thank you again. I appreciate every one responding. I really learned a lot.

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,357
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL Statement giving "Syntax error insert into statement"

    You are welcome.
    Glad you were able to figure it out.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    793
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL Statement giving "Syntax error insert into statement"

    Glad to hear it helps

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com