Converting SQL statement to VBA

[trpatrick]

In a form I have created a button that will perform an update query and I want to have the query in VBA code instead of creating a special query for a simple function. How do I structure the following SQL statement in VBA code in the On Click event?



UPDATE Badges SET Badges.Status = "a"
WHERE (((Badges.Status)="c"));

[CJ_London]

number of ways - easiest is

currentdb.execute("UPDATE Badges SET Status = 'a' WHERE Status='c'",dbfailonerror)

note the conversion of double quotes to single quotes around a and c

[gpeirce]

As someone learning, is this the same as:

strSQL = "UPDATE Badges SET Badges.Status = 'a' WHERE Badges.Status = 'c' "
DoCmd.RunSQL strSQL

[orange]

If you use a string variable
and assign it

strSQL = "UPDATE Badges SET Badges.Status = 'a' WHERE Badges.Status = 'c' "


You can then use

currentdb.execute strSQL, dbFailonError

This will give an error message if an error occurs.

[CJ_London]

that is another way - but it will generate prompts - just try it and decide for yourself

see this link for a comparison of the two methods

http://www.utteraccess.com/wiki/inde...SQL_vs_Execute

[trpatrick]

Thank you for your help.

[mathhero]

to convert SQL to VBA, you can use this program.
convert QuerySql To VBA_Sql.zip