Is It Possible to Control Access Warning Pop-Up Message Boxes?

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

When running an update query, Access displays a warning message box and
prompts for a response ("You are about to run an update query that will
modify data in your table").
I have a macro that runs a series of update queries. Right now, I get the
message box/response for each query. In this case, there is no need for the
message box at all. Is there a way to (ideally) turn off this message for
the duration of the query) or to answer it once for all of the queries?
Thanks
TerryoMSN
 
It is possible to turn the warning off ...

WARNING!!! If you forget to turn it back on, Access can be doing things you
didn't expect, and you won't know it until someone notices that the data is
all hinkey ...

You can even turn the warnings off in a macro ...

WARNING!!! If you forget to turn it back on, Access can be doing things you
didn't expect, and you won't know it until someone notices that the data is
all hinkey ...

.... and in the same macro, at the end, you can turn the warnings back on.

WARNING!!! If you forget to turn it back on, Access can be doing things you
didn't expect, and you won't know it until someone notices that the data is
all hinkey ...

Please note that if something goes sideways during either the macro or the
queries it runs, the warnings may NOT be turned back on. To ensure this
hasn't happened, you may want to create another macro that ONLY turns on the
warnings, and get in the habit of running it manually after EVERY TIME you
run your update queries macro.

WARNING!!! If you forget to turn it back on, Access can be doing things you
didn't expect, and you won't know it until someone notices that the data is
all hinkey ...

(hint: it might take a little more work initially, but if you use a
procedure to do those update queries, you can turn the warnings off/on
there, AND you can add it error handling to your procedure that ensures your
warnings are turned back on...)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
You can use the SetWarnings action to turn the warnings off,
but you need to make sure to turn them back on again after
the queries run.

A better method, if you are familiar with VBA, is to run your
action queries through code using something like;

Dim strSQL As String

strSQL = "Update tblMytable Set This = That;"

CurrentDb.Execute strSQL, dbFailOnError

This method bypasses the Access UI messages altogether
so you don't have to worry about turning Access warnings on
and off.
 
Terry -

In your macro, before the first query, SetWarnings to False. Remember at
the end of the macro to set them back to True.
 
Terry said:
When running an update query, Access displays a warning message box and
prompts for a response ("You are about to run an update query that will
modify data in your table").
Click to expand...

The problem with DoCmd.RunSQL is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
The problem with DoCmd.RunSQL is that it ignores any errors.
Either of the following will display any error messages received
by the query. If using DAO, use Currentdb.Execute
strSQL,dbfailonerror.. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText You can then remove the docmd.setwarnings lines.
Click to expand...

But don't do either of the latter without an error handler!

That's why I wrote my RunSQL() function, as a drop-in replacement fr
DoCmd.RunSQL -- it's nothing but a wrapper around the Execute
command, with appropriate error handling. Use Google Groups to find
the code for it -- I've posted it a bazillion times.
If you're going to use docmd.setwarnings make very sure you put
the True statement in any error handling code as well.
Click to expand...

I NEVER set it off. EVER. I think most experienced Access developers
would say the same thing. Dependence on it probably indicates you're
taking shortcuts (such as using menu commands with DoCmd.RunCommand)
where there are other more appropriate and more reliable methods to
accomplish the same thing.
 
Back
Top