Update/Insert Statement

K

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
 
K

Klatuu

There are some problems here, some of which are not really causing errors,
but are not good form.
For starters, take this line:

Dim strCustID, strCustName, strQ1, strQ2, strQ3, strQ4, strQ5 As String

All the variables in the line except for strQ5 is being Dimmed as a variant.
Only strQ5 is being dimmed as a string. Each variable has be individually
typed; otherwise, it is typed as a Variant. It should be:

Dim strCustID As String, strCustName As String, strQ1 As String, strQ2 As
String, strQ3 As String, strQ4 As String, strQ5 As String

Lines like this:
strQ1 = Me.txtQ1.Value & ""
Do nothing other than populate the variable strQ1 with the value of
Me.txtQ1. If you are trying to surround it with quotes, it should be:

strQ1 = """" & Me.txtQ1 & """"

Note I omitted the .Value property. Although technically correct, it isn't
necessary because it is the default property for a text box control.

Dim intDate As Date
The usual prefix for a Date is dtm or dte, int usually meand Integer
Dim dtmDate As Date

[TYPE], [DATE]
Neither of these should be used as names because they are both reserved
words. It usually does resolve the problem when you enclose them in
brackets, but Date will usually cause problems even in brackets if you use
it in any calculation or expression. In addition, they really have no
meaning. Type of what or what kind of Type? What Date? BirthDate,
TransactionDate, CancelDate. Try to make more meaningful names. It makes
your code easier to read and avoids the use of reserved words.

Now, to fix your SQL. Notice I ommited the use of the variables. There is
no reason to take the steps to populate the variables when you can build
your SQL directly from the controls:

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

Note: the Currentdb.Excute method is much faster than the RunSQL
The With construct saves having to type the Me over and over again. It also
is faster because the reference does not have to be reestablished for each
object. What With does tells Access that every thing that starts with a dot
between the With and the End With referes to the object in the With
statement.

Now, be aware that the code was written in this editor, so if you get any
syntax errors, you will have to work them out, but this is the basics.


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
Click to expand...
 
K

krisjuchau

There are some problems here, some of which are not really causing errors,
but are not good form.
For starters, take this line:

Dim strCustID, strCustName, strQ1, strQ2, strQ3, strQ4, strQ5 As String

All the variables in the line except for strQ5 is being Dimmed as a variant.
Only strQ5 is being dimmed as a string.  Each variable has be individually
typed; otherwise, it is typed as a Variant.  It should be:

Dim strCustID As String, strCustName As String, strQ1 As String, strQ2 As
String, strQ3 As String, strQ4 As String, strQ5 As String

Lines like this:
strQ1 = Me.txtQ1.Value & ""
Do nothing other than populate the variable strQ1 with the value of
Me.txtQ1.  If you are trying to surround it with quotes, it should be:

strQ1 = """" & Me.txtQ1 & """"

Note I omitted the .Value property.  Although technically correct, it isn't
necessary because it is the default property for a text box control.

Dim intDate As Date
The usual prefix for a Date is dtm or dte, int usually meand Integer
Dim dtmDate As Date

[TYPE], [DATE]
Neither of these should be used as names because they are both reserved
words.  It usually does resolve the problem when you enclose them in
brackets, but Date will usually cause problems even in brackets if you use
it in any calculation or expression.  In addition, they really have no
meaning.  Type of what or what kind of Type?  What Date? BirthDate,
TransactionDate, CancelDate.  Try to make more meaningful names.  It makes
your code easier to read and avoids the use of reserved words.

Now, to fix your SQL.  Notice I ommited the use of the variables.  There is
no reason to take the steps to populate the variables when you can build
your SQL directly from the controls:

    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

Note: the Currentdb.Excute method is much faster than the RunSQL
The With construct saves having to type the Me over and over again.  Italso
is faster because the reference does not have to be reestablished for each
object.  What With does tells Access that every thing that starts with a dot
between the With and the End With referes to the object in the With
statement.

Now, be aware that the code was written in this editor, so if you get any
syntax errors, you will have to work them out, but this is the basics.


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:
Click to expand...
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 & ""
Click to expand...
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
Click to expand...
   Call DoCmd.RunSQL(strInsertSQL)
Click to expand...
MsgBox "Thank You"
Click to expand...
End Sub
Click to expand...
Click to expand...

I think I was able to clean up the code as you suggested but every
time I run it, it throws a syntax error, which is pretty much where I
started at yesterday, here is the code now:

Private Sub cmdSubmit_Click()

Dim strCustID As String, strCustName As String, strQ1 As String
Dim strQ2 As String, strQ3 As String, strQ4 As String, strQ5 As String
Dim strQ6 As String, strQ7 As String, strQ8 As String, strQ9 As String
Dim strQ10 As String, strQ11 As String, strQ12 As String
Dim strQ13 As String, strQ14 As String, strType As String
Dim strInsertSQL As String, strCustComp As String
Dim intDate As Date

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
 
S

Stefan Hoffmann

hi kris,

strInsertSQL = "UPDATE tblDevelop [CustName],[Q1S],[Q2S]," & _
Click to expand...
Insert or Update ???
& .txtQ12 & """, """ & .txtQ13 & """, """ & _
.txtQ14 & """, """ & .txtType & """, """ & .txtDate & """ , """
& .txtCust & """) WHERE tblDevelop.DevID = " & Me.txtDevID & ";")
End With

CurrentDb.Execute strInsertSQL, dbFailOnError
Click to expand...
Use a utility function like:

Public Function SqlQuote(AString As String) As String

SqlQuote = "'" & Replace(AString, "'", "''") & "'"

End Function

e.g.

strInsertSQL = ".." & _
"VALUES (" & _
SqlQuote(txtName.Value) & ", " & _
SqlQuote(txtQ1.Value) & ", " & _
..
SqlQuote(txtQ14.Value) & ", " & _
...

mfG
--> stefan <--
 
S

Stefan Hoffmann

hi kris,
(e-mail address removed) wrote:
I appreciate the advice but I am not that familiar with functions and
am not sure of how the syntax should look, can you expound a little on
your advice?
Click to expand...
Just past it before your Sub:


Public Function SqlQuote(AString As String) As String

SqlQuote = "'" & Replace(AString, "'", "''") & "'"

End Function


Private Sub cmdSubmit_Click()

mfG
--> stefan <--
 
K

krisjuchau

hi kris,


Just past it before your Sub:

Public Function SqlQuote(AString As String) As String

   SqlQuote = "'" & Replace(AString, "'", "''") & "'"

End Function

Private Sub cmdSubmit_Click()

mfG
--> stefan <--
Click to expand...

I think I have the syntax correct but I only want to update the table
row where DevId is equal to the forms txtDevId. I don't see the where
statement in the above suggestion, where would that be placed. Below
is what I have so far:

Public Function SqlQuote(AString As String) As String
SqlQuote = "'" & Replace(AString, "'", "''") & "'"
End Function

Private Sub cmdSubmit_Click()

Dim strInsertSQL As String

strInsertSQL = ".." & _
"VALUES (" & _
SqlQuote(txtName.Value) & ", " & _
SqlQuote(txtQ1.Value) & ", " & _
SqlQuote(txtQ2.Value) & ", " & _
SqlQuote(txtQ3.Value) & ", " & _
SqlQuote(txtQ4.Value) & ", " & _
SqlQuote(txtQ5.Value) & ", " & _
SqlQuote(txtQ6.Value) & ", " & _
SqlQuote(txtQ7.Value) & ", " & _
SqlQuote(txtQ8.Value) & ", " & _
SqlQuote(txtQ9.Value) & ", " & _
SqlQuote(txtQ10.Value) & ", " & _
SqlQuote(txtQ11.Value) & ", " & _
SqlQuote(txtQ12.Value) & ", " & _
SqlQuote(txtQ13Value) & ", " & _
SqlQuote(txtQ14.Value) & ", " & _
SqlQuote(txtType.Value) & ", " & _
SqlQuote(txtDate.Value) & ", " & _
SqlQuote(txtCust.Value) & ")"

MsgBox "Thank You"

End Sub
 
S

Stefan Hoffmann

hi Kris,

I think I have the syntax correct but I only want to update the table
row where DevId is equal to the forms txtDevId. I don't see the where
statement in the above suggestion, where would that be placed. Below
is what I have so far:
Click to expand...
Okay. The string must be built like this:

strSQL = "UPDATE yourTable " & _
"SET " & _
"Q1S = " & SqlQuote(txtQ1.Value) & ", " & _
...
"Q2S = " & SqlQuote(txtQ2.Value) & " " & _
"WHERE DevId = " & txtDevId.Value

mfG
--> stefan <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with INSERT INTO statement 12

Top