update statement error in Access VBA

Question

I am trying to update several columns in a table called "tbl_Responses" but it gives an error after running which I have been trying to find!

varX= DLookup("[ID]", tableName, "[supplierNum] = " & supplierNum)
updateFieldsTbl1="column1,column2,column3,column4"
updateValuesTbl1 ="name,lastname, bla bla"
'There is nothing wrong with those 3 variables above.


sqlUp = "UPDATE " & tableName & "(" & updateFieldsTbl1 & ")" & " SET " & "(" & _ updateValuesTbl1 & ") WHERE ID = " & varX
DoCmd.RunSQL sqlUp

It gives an error in UPDATE statement line. Can you please tell what I am doing wrong? Thanks in advance

Alex K. · Accepted Answer · 2012-05-25 14:36:28Z

2

Your string parses to:

UPDATE tableName (column1,column2,column3,column4) SET (name,lastname, bla bla) ...

Which is similar to the INSERT format which is not valid for an UPDATE, the correct format is:

UPDATE tableName 
SET column1 = 'name',
    column2 = 'lastname',
    columnN =  valueN
WHERE ID = X

build the string thusly and it should work.

answered May 25, 2012 at 14:36

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DJ. · Accepted Answer · 2012-05-25 14:38:22Z

1

That's the syntax for an INSERT - for an UPDATE it's

UPDATE Table SET Field1=Value1, Field2=Value2, etc WHERE ...

answered May 25, 2012 at 14:38

DJ.

16.3k3 gold badges45 silver badges46 bronze badges

Collectives™ on Stack Overflow

update statement error in Access VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related