CODEWITHSUNDEEP
vbams-access
Adrienn Krammer
Adrienn Krammer

Reputation: 15

syntax error in update statement run-time error 3144

I got syntax error in update statement.Run-time error: 3144 I use the following code

CurrentDb.Execute "UPDATE product " & _
    " SET [product name] = '" & Me.txtName & "'" & _
    ", [cost of product] = " & Me.txtCost & "" & _
    ", [weight] = " & Me.txtWeight & "" & _
    ", [group] = '" & Me.CmbGroup & "'" & _
    ", [group ID] = '" & Me.txtGroupID & "'" & _
    ", [Ordered] = " & Me.txtOrdered & "" & _
    " WHERE [roduct name] = " & Me.txtName.Tag & ""

What can be the problem? If it makes sense, then Me.txtCost , Me.txtWeight and me.txtOrdered are number

Thanks for your help!

Upvotes: 0

Views: 9825

Answers (2)

Parfait
Parfait

Reputation: 107767

To future readers of this post, reconsider interpolating or concatenating VBA values into a dynamic SQL query. Consider parameterized queries with MS Access' QueryDefs. This will avoid mistyping, misquoting, and unreadable, and unmaintainable code.

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

PARAMETERS [txtName_PARAM] TEXT, [txtCost_PARAM] DOUBLE,
           [txtWeight_PARAM] DOUBLE, [CmbGroup_PARAM] TEXT,
           [txtGroupID_PARAM] TEXT, [txtOrdered_PARAM] LONG,
           [txtName_Tag_PARAM] TEXT;
UPDATE product 
SET [product name] = [txtName_PARAM],
    [cost of product] = [txtCost_PARAM],
    [weight] = [txtWeight_PARAM],
    [group] = [CmbGroup_PARAM],
    [group ID] = [txtGroupID_PARAM],
    [Ordered] = [txtOrdered_PARAM],
    WHERE [product name] = [txtName_Tag_PARAM];

VBA (dynamically bind values to parameter placeholders)

Dim qdef as QueryDef

Set qdef = CurrentDb.QueryDefs("mySavedQuery")

qdef![txtName_PARAM] = Me.txtName
qdef![txtCost_PARAM] = Me.txtCost
qdef![txtWeight_PARAM] = Me.txtWeight
qdef![CmbGroup_PARAM] = Me.CmbGroup
qdef![txtGroupID_PARAM] = Me.txtGroupID 
qdef![txtOrdered_PARAM] =  Me.txtOrdered
qdef![txtName_Tag_PARAM] = Me.txtName.Tag 

qdef.Execute dbFailOnError

Set qdef = Nothing

Upvotes: 1

mwolfe02
mwolfe02

Reputation: 24237

Two problems that I see:

  1. Typo in WHERE [roduct name] (should be WHERE [product name])
  2. Missing quotes around Me.txtName.Tag at the end of the statement

Try this instead:

CurrentDb.Execute "UPDATE product " & _
    " SET [product name] = '" & Me.txtName & "'" & _
    ", [cost of product] = " & Me.txtCost & "" & _
    ", [weight] = " & Me.txtWeight & "" & _
    ", [group] = '" & Me.CmbGroup & "'" & _
    ", [group ID] = '" & Me.txtGroupID & "'" & _
    ", [Ordered] = " & Me.txtOrdered & "" & _
    " WHERE [product name] = '" & Me.txtName.Tag & "'"

Upvotes: 2

Related Questions