Prev: Query works on dev db, gets a Run-time error '3073': Operation mus
Next: Access 2007-Splitting a Database
From: Terry on 15 Apr 2010 12:15
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
From: Jeff Boyce on 15 Apr 2010 12:30
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.

"Terry" <Terry(a)discussions.microsoft.com> wrote in message
news:6D9A2154-FA0B-4674-BDF3-DA1B16898423(a)microsoft.com...
> 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


From: Beetle on 15 Apr 2010 12:51
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.

--
_________

Sean Bailey


"Terry" wrote:

> 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
From: Daryl S on 15 Apr 2010 13:05
Terry -

In your macro, before the first query, SetWarnings to False. Remember at
the end of the macro to set them back to True.
--
Daryl S


"Terry" wrote:

> 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
From: Tony Toews [MVP] on 15 Apr 2010 15:00
Terry <Terry(a)discussions.microsoft.com> wrote:

>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").

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/
 |  Next  |  Last
Pages: 1 2
Prev: Query works on dev db, gets a Run-time error '3073': Operation mus
Next: Access 2007-Splitting a Database