Running DELETE queries from code with no Confirmation prompt

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm running a DELETE query using the DoCmd.RunSQL method.

This runs OK, but it comes up with a-

"You are about to delete xxx records, continue?"

prompt. How can I turn off these prompts for VBA-initiated actions only?

I know I can disable prompts throughout Access, but I would prefer to keep
them active for interactive use, but disabled for scripted actions. Is this
possible?
 
Hi Colin

Two methods:

1. Use SetWarnings:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

2. (My favourite) Don't use RunSQL:

Set db = CurrentDb
db.Execute strSQL, dbFailOnError

The second method you can wrap in a transaction so as to control whether or
not you commit to the action. Also, you have access to db.RecordsAffected,
from which you can construct your own messages.
 
Good stuff, thanks Graham.

I particularly like the second option, as you say. I was slightly suprised
to see this kind of interactive prompt popping up from programmed actions,
but I guess this is because to some degree DoCmd is simulating actions in the
User Interface.

This raises a general question. Quite a bit of the Access 2003 documentation
still refers to DoCmd as the common way of achieving certain actions, and
some elements such as form Record Navigation buttons use DoCmd when added by
Access wizards.

However is it true that:

a) Anything that DoCmd does can be achieved by using alternative "direct"
VBA methods?

and maybe:

b) DoCmd itself is basically a bunch of convenience wrapper methods on top
of lower level VBA API calls?

Regards: Colin

Graham Mandeno said:
Hi Colin

Two methods:

1. Use SetWarnings:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

2. (My favourite) Don't use RunSQL:

Set db = CurrentDb
db.Execute strSQL, dbFailOnError

The second method you can wrap in a transaction so as to control whether or
not you commit to the action. Also, you have access to db.RecordsAffected,
from which you can construct your own messages.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

colin_e said:
I'm running a DELETE query using the DoCmd.RunSQL method.

This runs OK, but it comes up with a-

"You are about to delete xxx records, continue?"

prompt. How can I turn off these prompts for VBA-initiated actions only?

I know I can disable prompts throughout Access, but I would prefer to keep
them active for interactive use, but disabled for scripted actions. Is
this
possible?
Click to expand...
Click to expand...
 
Hi Colin

This is partially correct. DoCmd is primarily a way of invoking macro
actions from VBA code. However, there are some very common tasks for which
DoCmd is the only option - for example, opening a form or report with a
where condition or other arguments.

Whatever you do, don't hold up the wizard code as the paragon of fine
best-practice programming! Much of it has not changed since Access 95.
--
Regards,

Graham Mandeno [Access MVP]
Auckland, New Zealand

colin_e said:
Good stuff, thanks Graham.

I particularly like the second option, as you say. I was slightly suprised
to see this kind of interactive prompt popping up from programmed actions,
but I guess this is because to some degree DoCmd is simulating actions in
the
User Interface.

This raises a general question. Quite a bit of the Access 2003
documentation
still refers to DoCmd as the common way of achieving certain actions, and
some elements such as form Record Navigation buttons use DoCmd when added
by
Access wizards.

However is it true that:

a) Anything that DoCmd does can be achieved by using alternative "direct"
VBA methods?

and maybe:

b) DoCmd itself is basically a bunch of convenience wrapper methods on top
of lower level VBA API calls?

Regards: Colin

Graham Mandeno said:
Hi Colin

Two methods:

1. Use SetWarnings:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

2. (My favourite) Don't use RunSQL:

Set db = CurrentDb
db.Execute strSQL, dbFailOnError

The second method you can wrap in a transaction so as to control whether
or
not you commit to the action. Also, you have access to
db.RecordsAffected,
from which you can construct your own messages.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

colin_e said:
I'm running a DELETE query using the DoCmd.RunSQL method.

This runs OK, but it comes up with a-

"You are about to delete xxx records, continue?"

prompt. How can I turn off these prompts for VBA-initiated actions
only?

I know I can disable prompts throughout Access, but I would prefer to
keep
them active for interactive use, but disabled for scripted actions. Is
this
possible?
Click to expand...
Click to expand...
Click to expand...
 
Colin,

Don't want to speak for Graham, but I know for a fact he did not mean
that wizard code was the paragon of fine best-practice programming in
Access 95.
 
Steve Schapel said:
Colin,

Don't want to speak for Graham, but I know for a fact he did not mean that
wizard code was the paragon of fine best-practice programming in Access
95.
Click to expand...

No - not even then! ;-)
 
Back
Top