SQL Statement giving "Syntax error insert into statement"

[DocArgh]

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.

 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)

 

[baderms]

The first thing I see is you have put line controls (vbcrlf) into your sql statement it shoud be more like this:

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

"VALUES(" & VISIT_ID_FK & ", '" & ClassType & "', "

 

[smozgur]

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".

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 & "#)"

 

[Joe4]

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:

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.

 

[xenou]

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:

sql = sql & _
...
...
...
[B]MsgBox sql
[/B]'or
[B]Debug.Print sql
[/B]

 

[DocArgh]

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.

 

[Joe4]

You are welcome.
Glad you were able to figure it out.

 

[smozgur]

Glad to hear it helps