How to Snub "You are about to ..... " dialog boxes in MS Access

  • Thread starter Thread starter Roshan Abbasi via AccessMonster.com
  • Start date Start date
R

Roshan Abbasi via AccessMonster.com

While processing a series of database tasks - like inserting, deleting and
updating a number of records at one time, MS Access presents a dialog box for
each of such operations asking to confirm, every time.
How to snub these dialog boxes to appear.
 
Hi Roshan
If the queries are being run via a Macro then insert the Set Warnings action
before the queries are listed. The Set Warnings box at the foot of the macro
should obviously be set to No. You should then include a further action to
set the warnings back to Yes after the queries have run.

If you're running the queries via vb code then insert:
DoCmd.SetWarnings = False

Hope this helps,

Lee
 
Roshan,

If you are doig this with a macro, add a SetWarnings action with
argument No before the first action query is run.

If you are using code to run SQL queries, use:
CurrentDb.Execute strSQL, dbFailOnError
instead of:
DoCmd.RunSQL strSQL

If you are running saved action queries from code with DoCmd.OpenQuery,
then add this:
DoCmd.SetWarning = False
before the firt one, and make sure to add this:
DoCmd.SetWarning = True
before the end of the sub/function.

Note: SetWarnings > No suppresses the warnings temporarily in a macro;
they are reset to default (warnings on) as soon as the macro finishes.
In code, on the contrary, DoCmd.SetWarning = False has a permanent
effect, therefore you need to restore the warnings before the end of
your procedure. If your code execution is interrupted before the
warnings are restored, they will remain suppressed until you close
Access. In such an event, open the immediate window (Ctrl+G) and type:
DoCmd.SetWarning = True
and press enter.
With the above in mind, because macros sometimes end up converted to
code, it is a good idea to add a SetWarnings > Yes at the end o the
macro, even though it makes no difference in the macro.

HTH,
Nikos
 
Thanks to Lee and Nikos.
Special thanks to Kikos for such an elaborated help on the topic - you are
great Nikos.

Regards.
 
Roshan,

Just a note... If you're running several queries, executing CurrentDb() can
get quite expensive, so it's usually better to get a reference to it, and
use that reference rather that rebuilding the Databases collection many
times.

Dim db As Database
Set db = CurrentDb()

db.Execute "........"
db.Execute "........"
db.Execute "........"
db.Execute "........"

Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top