Table update with SQL using variables 1

[nq]

I have a table "tblAreas" with a field "Name".

I have a form displaying data from another table. On this form, a situation may arise when I wish to add, delete or change the value of "Name" in "tblAreas". I have written SQL statements that work for add and delete - both work. However, the update function does not. I have two variables derived from controls on the form:

varName - the current value - so WHERE can find the record to update
varNewName - the new value to update the existing value

The SQL statement is:

DoCmd.RunSQL "UPDATE tblAreas SET tblAreas.Name=varNewName WHERE tblAreas.Name=varName"

When this is executed, Access responds by asking for two parameter values - varNewName and varName
If I enter the appropriate values, the update works correctly, so the SQL code works. However, the SQL statement does not seem to pick up the variables to use.

 

[LittleSmudge]

Common error nq - and easily fixed.

DoCmd.RunSQL "UPDATE tblAreas " _
           & "SET Name = '" & varNewName & "' " _
           & "WHERE Name = '" & varName & "'"

You have to take the variables OUTSIDE the literal string.

And as they are Text values they need wrapping in single quote delimiters.



By the way - it is asking for trouble using field names that match system words like 'Type', 'Form', 'Name', 'Where' etc .. ..

If you're not too far down the development route I'd strongly advise a change of field name to something like AName ( or AreaName ) rather than just Name.


'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[nq]

Thanks very much for that. it works.
Yes, I've found out about file names like that and have taken steps to change them. Thanks.