RunSql to Update Records Question (Access 2007)

[LA Lawyer]

I am running code in a form similar to this:

Dim SQL As String

SQL = "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _
"WHERE Employees.Title = 'Sales Manager'"

DoCmd.RunSQL SQL

The problem is that Access asks me to confirm the update. How do I avoid
the confirmation of the update? I want this just to run.

 

[Douglas J. Steele]

Either

Dim SQL As String

SQL = "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _
"WHERE Employees.Title = 'Sales Manager'"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

or, better

Dim SQL As String

SQL = "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _
"WHERE Employees.Title = 'Sales Manager'"


CurrentDb.Execute SQL, dbFailOnError

The reason I say the Execute method is better is not only does it not have
the pop-up, but it'll raise a trappable error if something goes wrong
running the query.

 

[Tony Toews [MVP]]

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

And don't forget to put docmd.setwarings true in the error handling
just to be on the safe side.

Tony