Disable warnings when running Delete and Append Queries

[BTU_needs_assistance_43]

A database I'm working on has an "Import Data" button on the main form and
everything it does works hunky dory but it runs about twenty-odd "You are
about to run an append and delete query" warnings and the user has to click
"Yes" on a message box for each one. Not really a life and death thing, but
it would be really convenient to be able to disable or overwrite that warning
so that the user could just click the button and let it do its thing til it
finished. Is it possible to turn those warnings off? Maybe write some code
that would just press the button for the user? What are your ideas out there?

 

[Keith Wilby]

A database I'm working on has an "Import Data" button on the main form and
everything it does works hunky dory but it runs about twenty-odd "You are
about to run an append and delete query" warnings and the user has to
click
"Yes" on a message box for each one. Not really a life and death thing,
but
it would be really convenient to be able to disable or overwrite that
warning
so that the user could just click the button and let it do its thing til
it
finished. Is it possible to turn those warnings off? Maybe write some code
that would just press the button for the user? What are your ideas out
there?

Search the help for "Set Warnings".

Keith.
www.keithwilby.co.uk

 

[ryguy7272]

DoCmd.SetWarnings False 'disable warnings
'Do your stuff here
DoCmd.SetWarnings True 'enable warnings

HTH,
Ryan---

 

[M Skabialka]

Before the actions start:
DoCmd.SetWarnings False
and when they are finished
DoCmd.SetWarnings True

Mich

 

[Jack Leach]

Use DoCmd.SetWarnings False to disable such warnings. Be sure to turn it
back on in your exit code though... don't want to accidently leave this one
off.

Alternatively, I believe CurrentDb.Execute "queryname or sql" will run
without warnings by default (and also includes an option for dbFailOnError to
make error trapable code). The only problem with this approach is the
Expression Service is not available (code like
Forms!ThisForm.Controls!ControlName.Value won't work).

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

I'm suprised so many people offer the DoCmd.Setwarnings code without
mentioning safegaurding against any errors. If DoCmd.SetWarnings True is not
included in the error handler routine, or the exit procedure (which the error
handler should redirect to), you will not receive ANY warnings for any
subsequent operations of this SQL happens to fail.

This is also a good reason for considering using CurrentDb.Execute rather
than DoCmd.RunSQL

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[BTU_needs_assistance_43]

Would it be possible for me to write an IF/THEN/ELSE statement that would
take possible errors into account?

IF "error occurs" Then DoCmd.Setwarnings = True

Something like that?

 

[Jack Leach]

Not really... that's essentially what error handling does.

a standard example:

Private Sub Button_Click()
On Error Goto Err_Handler

DoCmd.SetWarnings False
DoCmd.RunSQL "your query"
DoCmd.SetWarnings True

Exit_Proc:
DoCmd.SetWarnings True
Exit Sub
Err_Handler:
MsgBox "Error message here"
Resume Exit_Proc
End Sub


Or if you really wanted to, you could do an IF/THEN statement to handle the
error, but only if you have On Error Resume Next, otherwise as soon as an
error occurs it goes right to the error handler (on error resume next doesn't
have very many places in normal coding... this way is not recommended at all)

ex:

Private Sub Button_Click
On Error Resume Next

DoCmd.SetWarnings False
DoCmd.RunSQL "yourquery"
If Err.Number <> 0 Then DoCmd.SetWarnings True
'You would need this one anyway...
DoCmd.SetWarnings True

End Sub



The only way to check for errors using IF/THEN is to use On Error Resume
Next, which as you can see in this case you wouldn't have to check anyway...
you still need to turn on the warnings. The problem with On Error Resume
Next is, say you have a misspelled query name in the DoCmd.RunSQL statement,
there's no way you would know about it (until you noticed things going very
wrong with your data and had to track it back to this procedure... not fun).


So, really all your code should resemble the first example, with an error
handling procedure and and exit procedure for each sub/function.

As far as making sure the warnings are turned back on, that's no big deal
either. Assuming you have an exit procedure, just add that extra
DoCmd.SetWarnings into the exit procedure and you'll never have to worry
about it. Try an example using a nonexistent query name to see how it
handles it.

I made the comment about people not mentioning the dangers involved with not
resetting the warnings mainly because of how much of a royal pain it would be
for you if you didn't know about it. Consider, in the exit procedure of the
first example, leaving that DoCmd.Setwarnings line out, and mistyping the
query name... you would get no other warnings. Who knows how long it would
be until you realized it, and how much work it would take. A single
one-liner in the exit procedure makes that nasty possibility go away.

So if you don't use normal error handling and exit procedures, by all means,
start doing so, and just add in that one line in the exit. If you're not
aware, check out www.mztools.com and download the addin for VBA. It wall
automatically add the exit and error handlers for you in any procedure.
Myself and hundreds of others swear by this program.

good luck!
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[BTU_needs_assistance_43]

I understand what you're saying alot better now. The thing is the database is
already done except for a small section I'm working on and my supervisor
actually asked me to see if I could edit that part which is distantly related
to the section I'm working on. It already draws in files and data into the
database and its only drawback is that you do have to click yes about twenty
times before it finishes uploading. The query and table names it references
are set in stone basically so unless someone far down the line tries to
revamp the entire database (which would be remarkably stupid) I don't think
that will be a problem, although I see where your precautions come in.

Sadly my office internet firewall won't allow me to download any executable
files so I'll have to do it without MZTools, although I may download it at
home to play with and see what I come up with. Thank you very much for your
help, your're great at this.

 

[JimBurke via AccessMonster.com]

Do you really ever want that warning at all in a production environment? If
my users had to get that message every time a delete or append query was run
they'd quit using the software. I check for whether the application is
production or not by seeing if the startup window is displayed or not (I
manually change this setting when I put a new version into production). I set
options based on whether the DB is production or not, e.g.:

optionsFlag = CurrentDb.Properties("StartupShowDBWindow")
Application.SetOption "Confirm Action Queries", False
Application.SetOption "Confirm Document Deletions", optionsFlag

This way I get these warnings when I'm in my test DB but the production users
don't. My users cannot do anything within access that the application doesn't
allow them to do. i.e. they are never in design mode. If you allow users in
design mode that's a whole different story, because then they could
potentially accidentally delete anything in the DB.

 

[Tony Toews [MVP]]

I'm suprised so many people offer the DoCmd.Setwarnings code without
mentioning safegaurding against any errors. If DoCmd.SetWarnings True is not
included in the error handler routine, or the exit procedure (which the error
handler should redirect to), you will not receive ANY warnings for any
subsequent operations of this SQL happens to fail.

This is also a good reason for considering using CurrentDb.Execute rather
than DoCmd.RunSQL

Totally agree with you.

Tony

 

[Jack Leach]

Just so I can be sure this is perfectly clear:

SetWarnings is very serious business... you want to make *absolutely* sure
that you have this one under firm control. You can quite easily RUIN ALL OF
YOUR DATA if this practice fails during live use.

It comes back to Garbage In Garbage Out. A faulty SetWarnings operation can
introduce garbage to make garbageman weep, and by the time it's realized,
chances are very good that this bad data has seeded into other areas, in
which case the effects multiply and spread. At that point the only way to
recover your data is if you have kept extensively detailed transaction
information for every record in the database, or to recover backed up data
from a period before the faulty setwarnings operation was introduced (you
very much wouldn't want this to happen because an object or field name gets
changed a few years later).

Anyway, I didn't mean to keep goin on about it, but for such a seemingly
simple operation, this can have devestating effects.
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)