Help with SQL UPDATE command

[meadt]

Hi Guys,

I am trying to use an UPDATE command to change a record. The UPDATE result is dependant on two variables. One to indicate which record ( j ) and one to indicate the information to put in the record ( Comp )

this is what ive written:

DoCmd.RunSQL "UPDATE [Command Data Table] SET [Command Word] = (comp) WHERE [Primary] = (j)"

But when I run it, it comes up with a box asking me to 'enter parameter value' rather than realise i'm refering to a variable thats already got a value.

Any help?

Cheers,

 

[pbaldy]

You have to concatenate the variables:

DoCmd.RunSQL "UPDATE [Command Data Table] SET [Command Word] = '" & comp & "' WHERE [Primary] = " & j

Presuming the first is text and the second is numeric. I would also use CurrentDb.Execute, as it's more efficient and doesn't throw the warnings.

 

[meadt]

Worked a treat!

Thanks for the advice on using CurrentDb.Execute as well, I'll look in to it.

Thanks again!

 

[pbaldy]

No problemo! By the way, if it's not too late I'd get rid of the spaces in your names. They're more trouble than they're worth in the long run.