Search code examples
sqlms-accessvbams-access-2010ms-access-2013

Run time error 3144 from UPDATE statement

CurrentDb.Execute "UPDATE Transaction SET receipt_id=" & txtreceipt_id & ", tdate=" & txttdate & ",total_cost=" & txttotal_cost & ",total_disc=" & txttotal_disc & " WHERE receipt_id=" & txtreceipt_id & " "

I get a run time 3144 syntax error. Can't seem to locate the error.

enter image description here

Solution

  • Consider using a parameterized query with MS Access' QueryDefs to accurately specify the data types of your binded values and avoid concatenation and quote wrapping which renders hard to maintain code. Adjust below types as needed in PARAMETERS clause (compliant in Access SQL).

    SQL (save as an MS Access stored query only once)

    PARAMETERS [txtreceipt_id_PARAM] LONG, [txttdate_PARAM] DATE,
           [txttotal_cost_PARAM] DOUBLE, [txttotal_disc_PARAM] DOUBLE;
UPDATE [Transaction] 
SET receipt_id = [txtreceipt_id_PARAM], 
    tdate = [txttdate_PARAM],
    total_cost = [txttotal_cost_PARAM], 
    total_disc = [txttotal_disc_PARAM]
WHERE receipt_id = [txtreceipt_id_PARAM];

    VBA (dynamically bind values to parameter placeholders)

    Dim qdef as QueryDef

Set qdef = CurrentDb.QueryDefs("mySavedQuery")

qdef![txtreceipt_id_PARAM] = txtreceipt_id
qdef![txttdate_PARAM] = txttdate
qdef![txttotal_cost_PARAM] = txttotal_cost
qdef![txttotal_disc_PARAM] = txttotal_disc

qdef.Execute dbFailOnError

Set qdef = Nothing