Error in INSERT TO Statement

[Lisa]

I have this code that was used to insert fields from a form to a table, when
I click on the button to add the data to the table I am getting an error
"Error in INSERT TO Statement"

I have copied this code from another form that it worked in, just changed to
form fields for the new form and it's not working. I didn't create the code
and not sure how to check where the error is. Can anyone help?

Private Sub Command122_Click()
On Error GoTo Err_btnSave_Click
Dim strSQL As String
Dim numMatching As Integer
Dim Recordset As Recordset
Dim conDatabase As ADODB.Connection
Set conDatabase = CurrentProject.Connection


Dim Month As String
RepNm = cmbRep.Value
If RepNm = "" Then
MsgBox "Choose the Reps name you are quality checking from the
dropdown."
Else

strSQL = "Insert into TBL_QualitySales ( " _
& "RepName ," & "Date ," & "ANI ," & "TransType ," _
& "Q1_Answer ," & "Q1_Comment ," & "Q2_Answer ," &
"Q2_Comment ," _
& "Q3_Answer ," & "Q3_Comment ," & "Q4_Answer ," &
"Q4_Comment ," & "Q5_Answer ," & "Q5_Comment ," & "Q6_Answer ," & "Q6_Comment
," & "Q7_Answer ," & "Q7_Comment ," _
& "Q8_Answer ," & "Q8_Comment ," & "Q9_Answer ," &
"Q9_Comment ," & "Q10_Answer ," & "Q10_Comment ," & "Q11_Answer ," &
"Q11_Comment ," & "Q12_Answer ," & "Q12_Comment ," _
& "Q13_Answer ," & "Q13_Comment ," & "Q14_Answer ," &
"Q14_Comment ," & "Q15_Answer ," & "Q15_Comment ," & "Q16_Answer ," &
"Q16_Comment ," & "Q17_Answer ," & "Q17_Comment ," _
& "Q18_Answer ," & "Q18_Comment ," & "Q19_Answer ," &
"Q19_Comment ," _
& "Q20_Answer ," & "Q20_Comment ," & "Q21_Answer ," &
"Q21_Comment ," _
& "Q22_Answer ," & "Q22_Comment ," & "Q23_Answer ," &
"Q23_Comment) " _
& "Values(" _
& cmbRep & "," & "'" & txtqdate & "'," & "'" & txtANI &
"'," & "'" & cbtrans & "'," _
& "'" & FrmQ1_Results & "'," & "'" & txt_Q1Comments & "',"
& "'" & FrmQ2_Results & "'," & "'" & txt_Q2Comments & "'," _
& "'" & FrmQ3_Results & "'," & "'" & txt_Q3Comments & "',"
& "'" & FrmQ4_Results & "'," & "'" & txt_Q4Comments & "'," & "'" &
FrmQ5_Results & "'," & "'" & txt_Q5Comments & "'," & "'" & FrmQ6_Results &
"'," & "'" & txt_Q6Comments & "'," & "'" & FrmQ7_Results & "'," & "'" &
txt_Q7Comments & "'," _
& "'" & FrmQ8_Results & "'," & "'" & txt_Q8Comments & "',"
& "'" & FrmQ9_Results & "'," & "'" & txt_Q9Comments & "'," & "'" &
FrmQ10_Results & "'," & "'" & txt_Q10Comments & "'," & "'" & FrmQ11_Results &
"'," & "'" & txt_Q11Comments & "'," & "'" & FrmQ12_Results & "'," & "'" &
txt_Q12Comments & "'," _
& "'" & FrmQ13_Results & "'," & "'" & txt_Q13Comments &
"'," & "'" & FrmQ14_Results & "'," & "'" & txt_Q14Comments & "'," & "'" &
FrmQ15_Results & "'," & "'" & txt_Q15Comments & "'," & "'" & FrmQ16_Results &
"'," & "'" & txt_Q16Comments & "'," & "'" & FrmQ17_Results & "'," & "'" &
txt_Q17Comments & "'," _
& "'" & FrmQ18_Results & "'," & "'" & txt_Q18Comments &
"'," & "'" & FrmQ19_Results & "'," & "'" & txt_Q19Comments & "'," _
& "'" & FrmQ20_Results & "'," & "'" & txt_Q20Comments &
"'," & "'" & FrmQ21_Results & "'," & "'" & txt_Q21Comments & "'," _
& "'" & FrmQ22_Results & "'," & "'" & txt_Q22Comments &
"'," & "'" & FrmQ23_Results & "'," & "'" & txt_Q23Comments & "'" _
& ")"
'MsgBox "SQL: " & strSQL
conDatabase.Execute (strSQL)
MsgBox "Added quality data for Rep: " & RepName & " and Date:
" & Date
DoCmd.Close acForm, "FRM_Sales", acSaveNo

End If

 

[Klatuu]

Hard to read through all that and find a syntax problem; however, I do see
one problem. You have a field in your recordset with the name Date. Date is
an Access reserved word and will cause errors. It may cure your problem is
you change
& "RepName ," & "Date ," & "ANI ," & "TransType ," _
to
& "RepName ," & "[Date] ," & "ANI ," & "TransType ," _

Avoid using any reserved words as field names. First, Date is a reserved
work, it also means nothing. What date? ExpirationDate, EntryDate,
TransactionDate. Meaningful names are very helpful to you and anyone else
reading your code. It is also a good idea to use a standard naming
convention that will prevent even ambiguous names from causing problems, like
dtmDate or dteDate.

Here is a site I hope will be helpful to you
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

 

[Jeff L]

After you set your strSQL statement, there is a statement for a MsgBox
that looks to be commented out. Take out the single quote at the
beginning of that line and run your code again. The result will be
that you get a MsgBox that will show you the exact statement that is
going to run at conDatabase.Execute (strSQL). In looking at the long
strSql statement, it looks like you are not going to have any values
being entered. You need a Me. in front of all your variables, ie
Me.cmbRep, Me.txtqdate, Me.txtANI, etc.

Hope that helps a little!

 

[Lisa]

Jeff thanks for the input but that didn't work.. so I'll ask it this way..
what's the best way to do do this, I have a form that I creating (the
question is a label) The question has an Option Group and a Comment Text Box.
What I need to do is take the result of the option group and place the
result in the table. Here are the table and form fields

Table Field: Form Field:
RepName (text format) cmbRep (combo box)
EntryDate (date format) txtqdate (todays date)
ANI (text format) txtANI
TransType (text format) cbTrans (combo box)
Q1_Answer (number format) FrmQ1_Results
Q1_Comment (memo format) txt_Q1Comments
Q2_Answer (number format) FrmQ1_Results
Q2_Comment (memo format) txt_Q2Comments
Q3_Answer (number format) FrmQ3_Results
Q3_Comment (memo format) txt_Q3Comments
Q4_Answer (number format) FrmQ4_Results
Q4_Comment (memo format) txt_Q4Comments

THERE ARE 23 QUESTIONS SO THIS WOULD GO ON FOR 23 QUESTIONS. The name of
the table is TBL_QualitySales and the name of the form is FRM_Sales

Any help would be greatly appreciated.

Lisa

 

[Lisa]

So I removed the ' to not comment out the msg box and it gives me the SQL for
it and it seems fine (gives me the message box), I hit ok then I get "Syntax
Error in String ")'.

 

[RoyVidar]

So I removed the ' to not comment out the msg box and it gives me the
SQL for it and it seems fine (gives me the message box), I hit ok
then I get "Syntax Error in String ")'.

In stead of msgbox, try

debug.print strSQL

then hit ctrl+g to find the statement in the immediate pane. You could
try to copy paste the result into the SQL view of the query builder,
and
see if you can find out where the problem is. If not, you could try
posting that here.

 

[Jeff L]

Lisa,

I believe that you could easily accomplish what you are trying to do by
attaching each of the fields on your form to a field in your table.
You can do that by making the Record Source of the form whatever the
name of your table is. Then for each of your fields on the form,
change the Control Source to the appropriate field name from your
table. This way you are writing your data directly to your table and
not having to rely on a long and complicated SQL statement.

Hope that helps!

 

[Lisa]

Jeff

I owe you lunch.. I don't know I was trying to make it so complicated...
Thanks for your help with this...