Microsoft Office Tutorials and References
In Depth Information
Action Queries in VBA
In the VBA Code window, store the SQL statement in a string variable, and
follow the DoCmd.RunSQL statement with that variable name. In the following
example, mySQL is the name of the variable that stores the SQL statement:
‘Declare a string variable named mySQL
Dim mySQL As String
‘Store an action SQL statement in the mySQL variable.
mySQL = “UPDATE Employees SET Employees.[Country/Region] = ‘USA’” & _
“ WHERE (((Employees.[State/Province])=’WA’))”
‘Run the action query.
Turning off warnings
Normally, when you run an action query (from Access or from VBA), Access
displays a warning before the query actually runs, stating that you’re about
to change records in a table — which gives you a chance to change your
mind. In many cases, though, you won’t want that warning to appear. If you
know that the query does what it purports to do, and you’re writing code for
other people to use, presenting a warning message that your users may not
know how to respond to is pointless.
To prevent that warning from appearing when your code executes and to
enable the query to run without asking for permission, use the SetWarnings
method of the DoCmd object to disable the warnings. In Figure 5-7, the code
includes a DoCmd.SetWarnings False to turn off permission-asking just
before executing a RunSQL statement. Then the code turns the normal warning
messages back on (DoCmd.SetWarnings True) after the query runs.