Running DELETE queries from code with no Confirmation prompt

[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?

 

[Graham Mandeno]

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.

 

[Steve Schapel]

Hey, Graham, nice of you to drop by! ;-)

 

[Guest]

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

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

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?

 

[Graham Mandeno]

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

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

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

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?

 

[Steve Schapel]

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.

 

[Graham Mandeno]

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.

No - not even then! ;-)