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

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/de...n_20naming.asp

"Lisa" wrote:

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

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!

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



"Jeff L" wrote:

> 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!
>
>

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



"Lisa" wrote:

> 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
>
>
>
> "Jeff L" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
<CB4B6685-1CC7-42FB-A1DA-(E-Mail Removed)>:
> 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.

--
Roy-Vidar

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!

Jeff

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

"Jeff L" wrote:

> 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!
>
>