MS Access VBA - Run-time error 3144 - Error in Update Statement

B

bbcdancer

The field 'TRADERSIGNOFFID' in 'tblApp_TraderSignOff' table is a
'Autonumber' and 'Long Integer' data type.

Somehow, I can't seem to correct SQL syntax error for this VBA update
query in MS Access.

Can U spot what I am doing wrong and correct it? Many thanks in
Advance... Brenda xxx

'---------------------------------------------------------------


Private Sub CmdSaveChangesTSO_Click()

Dim H As Database, rsCust As Recordset
Set H = CurrentDb
Dim strSQL As String

If IsNull(CboTRADER_SIGNOFF_ID.Value) Then

MsgBox "Please select a 'Trader Sign Off' record from the Combo
list first."

Exit Sub
Else

strSQL = "UPDATE tblApp_TraderSignOff """
' table text
strSQL = strSQL & """ SET EFC_CASE_REF= """ & Me![TxtEFC_CASE_REF]
strSQL = strSQL & """, TRADE_ID= """ & Me![TxtTRADE_ID]
strSQL = strSQL & """, ABACUS_ID= """ & Me![TxtABACUS_ID]
strSQL = strSQL & """, TRADE_DETAILS= """ & Me![TxtTRADE_DETAILS]
strSQL = strSQL & """, TRADER_ID= """ & Me![TxtTRADER_ID]
strSQL = strSQL & """, STAMM_SHORT_NAME= """ & Me![TxtSHORT_NAME]
strSQL = strSQL & """, DETAILS_OF_ERROR= """ & Me![TxtDETAILS_OF_ERROR]
strSQL = strSQL & """, TRADER_COST_CENTRE= """ & Me![TxtTRADERCC]
strSQL = strSQL & """, CURRENCY_CODE= """ & Me![TxtCURRENCY_CODE]
strSQL = strSQL & """, BASE_ERROR_FINANCE_COST= """ & Me![TxtEFC_AMT]
strSQL = strSQL & """, EFC_USD_EQUIV= """ & [TxtEFC_AMT_USD]
strSQL = strSQL & """, EFC_BREAKDOWN= """ & Me![TxtEFC_BREAKDOWN]
strSQL = strSQL & """, TRADERSIGNOFFSTATUS= """ &
Me![TxtSIGNOFF_STATUS]
strSQL = strSQL & """, USERID= """ & Me![TxtUserID]
strSQL = strSQL & """, TIMESTAMP= """ & Now()

strSQL = strSQL & """ WHERE TRADERSIGNOFFID =""" &
Me![CboTRADER_SIGNOFF_ID]

H.Execute strSQL

MsgBox "Changes have been saved."

End If


End Sub


'---------------------------------------------------------------
 
G

Guest

I may be missing something, but I'm kinda confused about your reasoning for
putting """ in all your statements. strSQL & "SET EFC_CASE_REF= " &
Me![TxtEFC_CASE_REF] should work fine, it may be destorting your string.


I'm assuming your updating off of a form because your basing it on a button
click. Sometimes Me! doesn't work right in grabing the data (in my
experiences). Checking the name of your field names would be another good
place to start, and to make sure your data types match up.

Maybe change your function to msgbox(Me![TxtEFC_CASE_REF]) to make sure your
data is pulling out correctly also, and do that with each field name to
troubleshoot where the error may be occuring. If they all turn out, change
the msgbox to display your string and see what pulls up and make sure the
formatting it correct from that point also.
 
D

Douglas J. Steele

Any time you put 3 double quotes into a string expression like that, the
result is one double quote appearing in the string. In many of the places
where you have """, you don't need it.

For instance, you definitely don't need it after the name of the table, nor
before the keyword SET. Whether or not you need it after EFC_CASE_REF=
depends on what the data type is for EFC_CASE_REF: if it's text, you need
it, if it's numeric, you don't.

strSQL = "UPDATE tblApp_TraderSignOff """
strSQL = strSQL & """ SET EFC_CASE_REF= """ & Me![TxtEFC_CASE_REF]

should be either

strSQL = "UPDATE tblApp_TraderSignOff "
strSQL = strSQL & " SET EFC_CASE_REF= """ & Me![TxtEFC_CASE_REF]

or, if numeric,

strSQL = "UPDATE tblApp_TraderSignOff "
strSQL = strSQL & " SET EFC_CASE_REF= " & Me![TxtEFC_CASE_REF]

Dates need to be delimited using # (and need to be in mm/dd/yyyy format,
regardless of what your Short Date format has been set to through Regional
Settings*). Since you're already said that TRADERSIGNOFFID is a numeric
field, that means your string needs to end:

strSQL = strSQL & """, TIMESTAMP= " & Format(Now(), "\#mm\/dd\/yyyy\#")
strSQL = strSQL & " WHERE TRADERSIGNOFFID =" & Me![CboTRADER_SIGNOFF_ID]

Try printing out the final value of strSQL, and see whether it looks okay to
you. If you can't figure out the correct quotes, post back with details of
what each field is.

* Okay, it's not strictly true that it must be in mm/dd/yyyy format. Any
unambiguous format, such as yyyy-mm-dd or dd mmm yyyy will work. The point
is, if it's dd/mm/yyyy, it will not work for the first 12 days of each
month. (It will work from the 13th day on because Access will realize that
there isn't a month greater than 12, and so will correctly interpret, say,
13/08/2006)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The field 'TRADERSIGNOFFID' in 'tblApp_TraderSignOff' table is a
'Autonumber' and 'Long Integer' data type.

Somehow, I can't seem to correct SQL syntax error for this VBA update
query in MS Access.

Can U spot what I am doing wrong and correct it? Many thanks in
Advance... Brenda xxx

'---------------------------------------------------------------


Private Sub CmdSaveChangesTSO_Click()

Dim H As Database, rsCust As Recordset
Set H = CurrentDb
Dim strSQL As String

If IsNull(CboTRADER_SIGNOFF_ID.Value) Then

MsgBox "Please select a 'Trader Sign Off' record from the Combo
list first."

Exit Sub
Else

strSQL = "UPDATE tblApp_TraderSignOff """
' table text
strSQL = strSQL & """ SET EFC_CASE_REF= """ & Me![TxtEFC_CASE_REF]
strSQL = strSQL & """, TRADE_ID= """ & Me![TxtTRADE_ID]
strSQL = strSQL & """, ABACUS_ID= """ & Me![TxtABACUS_ID]
strSQL = strSQL & """, TRADE_DETAILS= """ & Me![TxtTRADE_DETAILS]
strSQL = strSQL & """, TRADER_ID= """ & Me![TxtTRADER_ID]
strSQL = strSQL & """, STAMM_SHORT_NAME= """ & Me![TxtSHORT_NAME]
strSQL = strSQL & """, DETAILS_OF_ERROR= """ & Me![TxtDETAILS_OF_ERROR]
strSQL = strSQL & """, TRADER_COST_CENTRE= """ & Me![TxtTRADERCC]
strSQL = strSQL & """, CURRENCY_CODE= """ & Me![TxtCURRENCY_CODE]
strSQL = strSQL & """, BASE_ERROR_FINANCE_COST= """ & Me![TxtEFC_AMT]
strSQL = strSQL & """, EFC_USD_EQUIV= """ & [TxtEFC_AMT_USD]
strSQL = strSQL & """, EFC_BREAKDOWN= """ & Me![TxtEFC_BREAKDOWN]
strSQL = strSQL & """, TRADERSIGNOFFSTATUS= """ &
Me![TxtSIGNOFF_STATUS]
strSQL = strSQL & """, USERID= """ & Me![TxtUserID]
strSQL = strSQL & """, TIMESTAMP= """ & Now()

strSQL = strSQL & """ WHERE TRADERSIGNOFFID =""" &
Me![CboTRADER_SIGNOFF_ID]

H.Execute strSQL

MsgBox "Changes have been saved."

End If


End Sub


'---------------------------------------------------------------
Click to expand...
 
B

bbcdancer

Still Not working...

Here are the data types.

Field name Data Type
TRADERSIGNOFFID AutoNumber
EFC_CASE_REF Text
TRADE_ID Text
ABACUS_ID Text
TRADE_DETAILS Text
TRADER_ID Text
STAMM_SHORT_NAME Text
DETAILS_OF_ERROR Text
TRADER_COST_CENTRE Text
CURRENCY_CODE Text
BASE_ERROR_FINANCE_COST Number Double
EFC_USD_EQUIV Number Double
EFC_BREAKDOWN Text
TIMESTAMP Date/Time General Date
TRADERSIGNOFFSTATUS Text
USERID Text
 
G

Guest

have you tried changing your message box to print out the sting? That would
be the best place to try to determine where an error might be occuring.
Sometimes using me!FieldName doesn't work, sometimes you have to use
forms.formName.Fieldname, or replace the . with !. I'm using Access 2000 and
I have to use Me.FieldName to get value out. Using .Net code, you would have
to do, Form.FieldName.Value for text boxes, and Form.Fieldname.SelectedIndex
for combo boxes. I know that Access 2003 goes more off the .Net framework
then 2000 does (I think?) so you might have to try adding .value to the end?
 
D

Douglas J. Steele

Please post what you changed your code to along with this information.

Also, did you try my suggestion to "Try printing out the final value of
strSQL, and see whether it looks okay to you. If you can't figure out the
correct quotes, post back with details of what each field is."? If not, do
so, and let us see what your string looks like.

Finally, try changing your line of code from:

H.Execute strSQL

to

H.Execute strSQL, dbFailOnError

and make sure you've got error trapping in the routine, so that we can see
exactly what it's complaining about.
 
Top