Access - CurrentDB.Execute Update Syntax Error

Posted on 2012-09-10
Last Modified: 2012-09-10
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

Question by:wlwebb
    LVL 84

    Accepted Solution

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

    Assisted Solution

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

    Author Comment

    Tried both..... get Run-time error 3061

    With Irog's
    Too few parameters.  Expected 1.

    With LSMs
    Too few parameters.  Expected 2.

    LVL 29

    Expert Comment

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

    Author Comment

    the curCtlTtlAmt.... are in a Public Sub...

    Also, this is a Tabbed form subform

    Author Closing Comment

    Sorry, typo on the Where clause ... my fault...  worked like a charm !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now