Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3764
  • Last Modified:

Access - CurrentDB.Execute Update Syntax Error

Hello all....

Trying to update a particular value in a table with vb CurrentDb.Execute Update......

I keep getting an Run-time error 3144 Syntax Error in Update Statement

Checked all the name typing and they're all correct.  

The full code of the sub is
Private Sub UpdateTotals()

Dim v As Integer, CurTtlAmtIn As Currency, CurTtlAmtOut As Currency, CurTtlNetAmt As Currency, CurTtlAmtVal As Currency

CurTtlAmtIn = curCtlTtlAmtIn
CurTtlAmtOut = curCtlTtlAmtOut
CurTtlNetAmt = curCtlTtlNetAmt
CurTtlAmtVal = curCtlTtlAmtVal

v = Val(Forms![frm_DataReporting]![WVLVLControlTotals].Form![txtNewShiftPrtgLVLCtlID])

    CurrentDb.Execute "UPDATE ShiftReportingLVLCtl (TtlAmtIn, TtlAmtOut, TtlNetAmt, TtlAmtVal) SET (" & CurTtlAmtIn & "," & CurTtlAmtOut & "," & CurTtlNetAmt & "," & CurTtlAmtVal & ")" & " WHERE ShiftReportingLVLCtlID=" & v, dbFailOnError
    
    
    
End Sub

Open in new window

0
wlwebb
Asked:
wlwebb
  • 3
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Update statements should read like this:

UPDATE ShiftReport SET Field1=Value1, Field2=Value2 etc etc

So something like:

CurrentDb.Execute "UPDATE ShiftReportingLVLCtl SET TtlAmtIn=" & CurTtlAmtIn & ", TltAmtOut=" & CurTtlAmtOut & ", TtlNetAmt=" & CurTltNetAmt & ", TtlAmtVal=" & CurTtlNetAmt & " WHERE ShiftReportingLVLCtlID=" & v, dbFailOnError
0
 
IrogSintaCommented:
Try it this way:
CurrentDb.Execute "UPDATE ShiftReportingLVLCtl  SET TtlAmtIn=" & CurTtlAmtIn  & ", TtlAmtOut="  & CurTtlAmtOut & ", TtlNetAmt=" & CurTtlNetAmt & ", TtlAmtVal=" & CurTtlAmtVal & ") WHERE ShiftReportingLVLCtlID=" & v, dbFailOnError

Open in new window


Edit... Looks like you already got an answer :-)
0
 
wlwebbAuthor Commented:
Tried both..... get Run-time error 3061

With Irog's
Too few parameters.  Expected 1.

With LSMs
Too few parameters.  Expected 2.

???????????
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
IrogSintaCommented:
How about:
Private Sub UpdateTotals()
    Dim v As Integer

    v = Val(Forms![frm_DataReporting]![WVLVLControlTotals].Form![txtNewShiftPrtgLVLCtlID])

    CurrentDb.Execute "UPDATE ShiftReportingLVLCtl  SET TtlAmtIn=" & Me.curCtlTtlAmtIn & ", TtlAmtOut="  & Me.curCtlTtlAmtOut & ", TtlNetAmt=" & Me.curCtlTtlNetAmt & ", TtlAmtVal=" & Me.curCtlTtlAmtVal & " WHERE ShiftReportingLVLCtlID=" & v, dbFailOnError
      
End Sub

Open in new window

If this code is in the form frm_DataReporting, then you can also use:

 v = Val(Me.[WVLVLControlTotals].Form![txtNewShiftPrtgLVLCtlID])
0
 
wlwebbAuthor Commented:
the curCtlTtlAmt.... are in a Public Sub...

Also, this is a Tabbed form subform
0
 
wlwebbAuthor Commented:
Sorry, typo on the Where clause ... my fault...  worked like a charm !
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now