Insert into table

[krisjuchau]

To give a little background, I have a form that initially inserts data
into table on submit. I have a second form that pulls from the table
and repopulates the form fields for editing purposes. I want to be
able to update the table on submit of this form. My query syntax seems
to be a little bit off, any help would be great. Here is the code:
Private Sub cmdSubmit_Click()
Dim strCustID, strCustName, strQ1, strQ2, strQ3, strQ4, strQ5 As
String
Dim strQ6, strQ7, strQ8, strQ9, strQ10, strQ11, strQ12 As String
Dim strQ13, strQ14, strType As String
Dim strInsertSQL, strCustComp As String
Dim intDate As Date
strCustID = Me.txtCustID.Value & ""
strCustName = Me.txtName.Value & ""
strQ1 = Me.txtQ1.Value & ""
strQ2 = Me.txtQ2.Value & ""
strQ3 = Me.txtQ3.Value & ""
strQ4 = Me.txtQ4.Value & ""
strQ5 = Me.txtQ5.Value & ""
strQ6 = Me.txtQ6.Value & ""
strQ7 = Me.txtQ7.Value & ""
strQ8 = Me.txtQ8.Value & ""
strQ9 = Me.txtQ9.Value & ""
strQ10 = Me.txtQ10.Value & ""
strQ11 = Me.txtQ11.Value & ""
strQ12 = Me.txtQ12.Value & ""
strQ13 = Me.txtQ13.Value & ""
strQ14 = Me.txtQ14.Value & ""
strType = Me.txtType.Value & ""
strDate = Me.txtDate.Value & ""
strCustComp = Me.txtCust.Value & ""
strInsertSQL = "UPDATE tblDevelop [CustName],[Q1S],[Q2S]," & _
"[Q3S],[Q4S],[Q5S],[Q6S],[Q7S],[Q8S], [Q9S],
[Q10S],
[Q11S], [Q12S], [Q13S], [Q14S]," & _
"[TYPE], [DATE], [CustComp]) " & _
"VALUES (" & Chr$(34) & strCustName & Chr$(34) & ",
" & Chr$(34) & strQ1 & Chr$(34) & ", " & Chr$(34) & strQ2 & Chr$(34)
&
", " & _
"'" & strQ3 & "', '" & strQ4 & "', '" & strQ5 & "',
'" & strQ6 & "', '" & strQ7 & "', " & _
"'" & strQ8 & "', '" & strQ9 & "', '" & strQ10 &
"',
'" & strQ11 & "', '" & strQ12 & "', '" & strQ13 & "', '" & strQ14 &
"', '" & strType & "', '" & strDate & " ', " & Chr$(34) & strCustComp
& Chr$(34) & ") WHERE tblDevelop.DevID = " & Me.txtDevID.Value
Call DoCmd.RunSQL(strInsertSQL)
MsgBox "Thank You"
End Sub

 

[pbaldy]

For starters, there's no opening parentheses on the UPDATE clause. I don't know if it's just cut/paste errors, but there are a lot of cases of new lines without line continuation characters. Add

Debug.Print strInsertSQL

to examine the resulting SQL string in the Immediate window, which may help you find other problems.

 

[krisjuchau]

Ok, I've cleaned everything up with some direction, here is what I have now. Problem remains the same when the code is called it throws a syntax errror:

Private Sub cmdSubmit_Click()

Dim strInsertSQL As String

With Me
strInsertSQL = ("UPDATE tblDevelop [CustName], [Q1S], [Q2S], [Q3S], [Q4S]," & _
"[Q5S], [Q6S], [Q7S], [Q8S], [Q9S], [Q10S], [Q11S], [Q12S], [Q13S], [Q14S]," & _
"[TYPE], [DATE], [CustComp])" & _
"VALUES (""" & .txtName & """, """ & .txtQ1 & """, """ & .txtQ2 & """, """ & .txtQ3 & """, """ & _
.txtQ4 & """, """ & .txtQ5 & """, """ & .txtQ6 & """, """ & .txtQ7 & """, """ & .txtQ8 & """, """ & _
.txtQ9 & """, """ & .txtQ10 & """, """ & .txtQ11 & """, """ & .txtQ12 & """, """ & .txtQ13 & """, """ & _
.txtQ14 & """, """ & .txtType & """, """ & .txtDate & """ , """ & .txtCust & """) WHERE tblDevelop.DevID = " & Me.txtDevID & ";")
End With

CurrentDb.Execute strInsertSQL, dbFailOnError

MsgBox "Thank You"

End Sub

 

[pbaldy]

I'm sorry, I completely spaced there. Somehow I had insert in my head when you're actually updating. The proper syntax for an update query is

UPDATE TableName
SET Field1 = 123, Field2 = 'Text', Field3 = #11/6/08#
WHERE...

The syntax you're using would be appropriate for an append query, but isn't for an update query.

 

[krisjuchau]

I appreciate the response. Is it not possible to use an insert query when there is already data in the table? As for the update query, can you give me an example of how I would use the syntax with the code i have?

 

[pbaldy]

Well, you'd use an insert query to add new records to the table, an update query to modify existing records. Which is it you want to do?

 

[WayPay]

If you want to update:

 strUpdateSQL = _
" UPDATE tblDevelop SET " & _
"    [CustName]          = " & """" & .txtName  & """," & _
"    [Q1S]               = " & """" & .txtQ1    & """," & _
"    [Q2S]               = " & """" & .txtQ2    & """," & _
"    [Q3S]               = " & """" & .txtQ3    & """," & _
"    [Q4S]               = " & """" & .txtQ4    & """," & _
"    [Q5S]               = " & """" & .txtQ5    & """," & _
"    [Q6S]               = " & """" & .txtQ6    & """," & _
"    [Q7S]               = " & """" & .txtQ7    & """," & _
"    [Q8S]               = " & """" & .txtQ8    & """," & _
"    [Q9S]               = " & """" & .txtQ9    & """," & _
"    [Q10S]              = " & """" & .txtQ10   & """," & _
"    [Q11S]              = " & """" & .txtQ11   & """," & _
"    [Q12S]              = " & """" & .txtQ12   & """," & _
"    [Q13S]              = " & """" & .txtQ13   & """," & _
"    [Q14S               = " & """" & .txtQ14   & """," & _
"    [TYPE]              = " & """" & .txtType  & """," & _
"    [DATE]              = " & """" & .txtDate  & """," & _
"    [CustCom            = " & """" & .txtCust  & """," & _
" WHERE tblDevelop.DevID = " & """" & .txtDevID & """"

If you want to insert:

strInsertSQL = _
" INSERT " & _
" INTO tblDevelop " & _
"    ( " & _
"        [CustName], [Q1S], [Q2S], " & _
"        [Q3S], [Q4S], [Q5S], " & _
"        [Q6S], [Q7S], [Q8S], " & _
"        [Q9S], [Q10S], [Q11S], " & _
"        [Q12S], [Q13S], [Q14S], " & _
"        [TYPE], [DATE], [CustComp] " & _
"    ) " & _
"    VALUES " & _
"    ( " & _
"        """ & .txtName & """, """ & .txtQ1 & """, """ & .txtQ2 & """, " & _
"        """ & .txtQ3 & """, """ & .txtQ4 & """, """ & .txtQ5 & """, " & _
"        """ & .txtQ6 & """, """ & .txtQ7 & """, """ & .txtQ8 & """, " & _
"        """ & .txtQ9 & """, """ & .txtQ1 & """, """ & .txtQ1 & """, " & _
"        """ & .txtQ12 & """, """ & .txtQ13 & """, """ & .txtQ14 & """, " & _
"        """ & .txtType & """, """ & .txtDate & """, """ & .txtCus & """" & _
"    )"

My brain hurts. This would be SO much easier in Perl .

 

[krisjuchau]

Thanks, I am using the provided syntax but it is still throwing a syntax error. I've been battling this for about 3 days.
Here is the code, any clues.

Private Sub cmdSubmit_Click()

Dim strUpdateSQL As String

With Me
 strUpdateSQL = _
" UPDATE tblDevelop SET " & _
"    [CustName]          = " & """" & .txtName & """," & _
"    [Q1S]               = " & """" & .txtQ1 & """," & _
"    [Q2S]               = " & """" & .txtQ2 & """," & _
"    [Q3S]               = " & """" & .txtQ3 & """," & _
"    [Q4S]               = " & """" & .txtQ4 & """," & _
"    [Q5S]               = " & """" & .txtQ5 & """," & _
"    [Q6S]               = " & """" & .txtQ6 & """," & _
"    [Q7S]               = " & """" & .txtQ7 & """," & _
"    [Q8S]               = " & """" & .txtQ8 & """," & _
"    [Q9S]               = " & """" & .txtQ9 & """," & _
"    [Q10S]              = " & """" & .txtQ10 & """," & _
"    [Q11S]              = " & """" & .txtQ11 & """," & _
"    [Q12S]              = " & """" & .txtQ12 & """," & _
"    [Q13S]              = " & """" & .txtQ13 & """," & _
"    [Q14S]               = " & """" & .txtQ14 & """," & _
"    [TYPE]              = " & """" & .txtType & """," & _
"    [CustComp]            = " & """" & .txtCust & """," & _
" WHERE tblDevelop.DevID = " & """" & .txtDevID & """"
End With

CurrentDb.Execute strUpdateSQL, dbFailOnError

MsgBox "Thank You"

End Sub

 

[pbaldy]

Have you added the Debug line I suggested earlier, so you can see what the finished string is? Are all those fields text fields in the table?

 

[WayPay]

What's the error you're getting? And could you post the statement you're executing?

I don't have a form set up with the used fields, so I can't really test the code .

 

[krisjuchau]

Thanks, that helped. I determined that I kept on trying to match a string in the table where it was an integer. Here is the final working code for reference:

Private Sub cmdSubmit_Click()

Dim strUpdateSQL As String

With Me
 strUpdateSQL = _
" UPDATE tblDevelop SET " & _
"    [CustName]          = " & """" & .txtName & """," & _
"    [Q1S]               = " & """" & .txtQ1 & """," & _
"    [Q2S]               = " & """" & .txtQ2 & """," & _
"    [Q3S]               = " & """" & .txtQ3 & """," & _
"    [Q4S]               = " & """" & .txtQ4 & """," & _
"    [Q5S]               = " & """" & .txtQ5 & """," & _
"    [Q6S]               = " & """" & .txtQ6 & """," & _
"    [Q7S]               = " & """" & .txtQ7 & """," & _
"    [Q8S]               = " & """" & .txtQ8 & """," & _
"    [Q9S]               = " & """" & .txtQ9 & """," & _
"    [Q10S]              = " & """" & .txtQ10 & """," & _
"    [Q11S]              = " & """" & .txtQ11 & """," & _
"    [Q12S]              = " & """" & .txtQ12 & """," & _
"    [Q13S]              = " & """" & .txtQ13 & """," & _
"    [Q14S]              = " & """" & .txtQ14 & """," & _
"    [DATE]              = " & """" & .txtDate & """" & _
" WHERE tblDevelop.DevID = " & " " & .txtDevID
End With
                  
CurrentDb.Execute strUpdateSQL, dbFailOnError
                                                 
MsgBox "Thank You"

End Sub