can visual basic turn off confirmation of action queries

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

Guest

My compiled Access program requires confirmation of action queries on some
machines but not on others. It's a real nuisance; I need to find some way to
turn it off. I can't find any documentation for how to turn it off in visual
basic.
 
DoCmd.SetWarnings

But its an all or nothing deal. It *shouldn't* work sporatically among
machines.
 
David C. Holley said:
DoCmd.SetWarnings

But its an all or nothing deal. It *shouldn't* work sporatically among
machines.
Click to expand...

Different users may have different setting for the option "Confirm
action queries" (Tools -> Options, Edit/Find tab). I agree, careful use
of SetWarnings should handle the problem. Another approach, which I
favor when possible, is to use the DAO Execute method (usually by code
like "CurrentDB.Execute strQuery, dbFailOnError") instead of
DoCmd.RunSQL or DoCmd.OpenQuery.
 
Hmmm, it looks like SetWarnings does more than turning off action query
warnings, and apparently unlike turning off the warnings from the menu, doing
it with SetWarnings is only good until the macro (or function?) exits. I'd
like to be able to just turn off the action query warnings once when the
program loads and have the result be the same as if the user (who generally
doesn't have access installed) had turned off the action query warnings from
the Options:Edit/Find:Confirm menu he doesn't have.

One would think the runtime module would behave the same on all machines,
but on at least some machines with Access installed the warnings are set to
Off, and on some machines without Access the warnings are set to On.

Thanks for your help.
 
Thanks, I was hoping there would be something that would be a global
equivalent of turning off the warnings in the Edit/Find tab, rather than
having to turn them off every time I run a query from a macro.
 
waynemb said:
Hmmm, it looks like SetWarnings does more than turning off action
query warnings, and apparently unlike turning off the warnings from
the menu, doing it with SetWarnings is only good until the macro (or
function?) exits. I'd like to be able to just turn off the action
query warnings once when the program loads and have the result be the
same as if the user (who generally doesn't have access installed) had
turned off the action query warnings from the
Options:Edit/Find:Confirm menu he doesn't have.

One would think the runtime module would behave the same on all
machines, but on at least some machines with Access installed the
warnings are set to Off, and on some machines without Access the
warnings are set to On.
Click to expand...

If you "execute" your queries instead of "running" them then there is no
confirmation prompt.

CurrentDB.Execute "QueryName", dbFailOnError
 
Thanks, another interesting workaround. Unfortunately most of my queries are
called from macros, and I'd hate to have to go back and turn it all into vb
code. I was hoping that there would be some coding equivalent of just
turning off the confirmations from the menu bar.
 
Amazing what spending some time in an Irish Pub or better yet a Taverna
will do to jog the memory. I believe that the SetOption method is what
you're looking for. I scanned the Help Topic breifly, but it does
include a mention about Confirming Action Queries. The topic with the
info is entitled 'Set Options From Visual Basic' and includes the
various options and equivalent string values to access the option using
SetOption and GetOption. From there, if you're all macro based, its just
a matter creating an AutoExec macro to call the VBA code that sets the
value.
 
David C. Holley said:
Amazing what spending some time in an Irish Pub or better yet a
Taverna will do to jog the memory. I believe that the SetOption
method is what you're looking for. I scanned the Help Topic breifly,
but it does include a mention about Confirming Action Queries. The
topic with the info is entitled 'Set Options From Visual Basic' and
includes the various options and equivalent string values to access
the option using SetOption and GetOption. From there, if you're all
macro based, its just a matter creating an AutoExec macro to call the
VBA code that sets the value.
Click to expand...

You can set options from Visual Basic, all right, but it's a potentially
dangerous and often ill-mannered thing to be doing. A user's personal
preferences as to options, like that of display resolution (which is
another thing that is often asked in these newsgroups), should be
respected by the developer. It's all well and good to say, "I'll just
change these options while my application runs, and then change them
back when it closes." But what happens if the application crashes
without closing properly? The options chosen by the developer, not the
user, remains in place, affecting other applications.

And even if that doesn't happen, the user may be confounded by behavior
in this particular database that is not what they expect. A use who s
familiar with Access and knows how he has set his options may reasonably
expect this application to conform to those options. He may be cavalier
about running delete queries, because he knows he'll have a chance to
undo them if they turn out to delete all records in the table by
mistake. Imagine his shock when he finds that >poof!< the warning he
had counted upon didn't appear.
 
Yeppers. That's why I prefer to wrap my DoCmd.RunSQL in
DoCmd.SetWarnings statements. I personally think that RunSQL should be
modified to include a parameter to supress messages if desired.
 
Thanks, good point. Most of my users though, not only don't have Access on
their machines, they have no idea what Access might be, so the confirmation
messages are a killer.
 
waynemb said:
Thanks, good point. Most of my users though, not only don't have
Access on their machines, they have no idea what Access might be, so
the confirmation messages are a killer.
Click to expand...

As you no doubt know when viewing options in "tools - options" the majority
of what you see are "Access options for the current user irrespective of the
file being used" and small number are for "this file only irrespective of
the user".

I've often wondered why they don't expose all (or at least a lot more) of
these as "file options irrespective of user". I can certainly see the value
in having an option that is "global", but there should also be a file-level
option of the same type that over-rides the global one that can used for
distributed apps.

I think it is perfectly reasonable for a developer to want some of these
global options to be set a certain way to make their app work as expected
and in many cases to reduce support calls/questions. We should not have to
tinker with the users global options in order to get OUR app to behave a
certain way. As already stated, setting them back "the way you found them"
is not always so easy to do or reliable.
 
waynemb said:
Thanks, good point. Most of my users though, not only don't have
Access on their machines, they have no idea what Access might be, so
the confirmation messages are a killer.
Click to expand...

If you're sure that no one is going to be troubled your
option-twiddling, you can follow David Holley's suggestion about
Application.GetOption and Application.SetOption. If your application
were to change the options on *my* PC, I'd be incensed.
 
So why not go with something like this...

In a split DB, add a table to the front end named tblOptionsLocal and
add a table to back named tblOptionsGlobal. When the database is opened,
and Autoexec macro deletes all of the records in the tblOptionsLocal.
The code then copies the NAMES of the OPTIONS to be changed from the
tblOptionsGlobal to tblOptionslocal. From their more code loops through
the tblOptionsLocal capturing the values for the options set on the
machine and stores them into the table. The next step reads the options
and values in the tblOptionsGlobal and sets the options. Doing so saves
the settings locally, adjusts the settings appropriately and creates a
situation where Access can be returned to its inital state. From there
create a hidden form with code that exists on the OnClose event that
loops through the tblOptionsLocal and sets them. Granted the local
options won't be restored if the PC craps out or if Access is otherwise
shut down abnormally.

David H
 
Well if it did and you were incensed, you could always start a BLOG to
rant and rave over it - where those types of postings belong.

David H
 
David C. Holley said:
Well if it did and you were incensed, you could always start a BLOG to
rant and rave over it - where those types of postings belong.

David H
Click to expand...

What's the problem, Mr. Holley? That rather sounds like an attack, but
I'm not aware of having attacked you. I am indeed counseling waynemb
against changing his users' option settings, on the grounds that it
tends to upset the users. It's up to him, of course, how much he wants
to take that into account.
 
Sorry about that I sent the message before puttin the lol and :) into
it. No offense was intended. We don't need another spat going on. How
about a drink or two, I know a really good Irish Pub and an even better
Taverna?
 
David C. Holley said:
Sorry about that I sent the message before puttin the lol and :) into
it. No offense was intended. We don't need another spat going on. How
about a drink or two, I know a really good Irish Pub and an even
better Taverna?
Click to expand...

Fair enough, and the PC Datasheet brawl is ugly enough to put anyone on
edge. If you're in the vicinity of central NJ, I'll stand the first
round; if not, I'll lift a glass to you on my next night out.
 
Back
Top