How to determine the state of SetWarnings revisited?

[MLH]

In my Tues, Mar 21 2006 original post on this
topic, Allen Browne made a very good case for
use of the Execute method instead of firing an
action query with RunSQL or OpenQuery. Ex-
ploring the topic just a bit further...

I'm wondering if VB receives any event notice
indicating that a confirmation dialog just opened?
That is, can I tell if a confirmation dialog WAS
encountered and then act on it?

Suppose SetWarnings is True, and I run a query
which appends a test record to a test table - can I
capture the event of the confirmation dialog
popping up, so I know afterward that it DID?
Also, can I answer it YES automatically?

[pietlinden]

why not just declare a public variable that stores the value of
SetWarnings and then change it whenever you run a SetWarnings
True/False? Then you can return the value anywhere you want. Sounds
to me like you're making this infinitely harder than it really is.

[David W. Fenton]

pi********@hotmail.com wrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

why not just declare a public variable that stores the value of
SetWarnings and then change it whenever you run a SetWarnings
True/False? Then you can return the value anywhere you want.
Sounds to me like you're making this infinitely harder than it
really is.

Better yet, write a function to do it and always use that function
to change it. E.g., make one optional parameter, and if you supply
the parameter, set it accordingly, and set a static variable
(internal to the function) to match the setting. When you want to
know what the status is, you can call it without the optional
argument and act on the returned value.

This is basically a way of using a function as a property. You could
define a custom property in a class module, but then you'd have to
refer to it as part of the class module. Unless you've alredy got a
class module in place for global settings, I'd go with the function.
However, if you're using a class module for storing and setting the
values of global settings, then it makes more sense to implement
this as a property Let/Get.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[MLH]

You're right. That would take care of it. Of course, once the code
is written and functional, of course its no more difficult then. I'm
just curious - exploring other avenues.

Thx.

>why not just declare a public variable that stores the value of
SetWarnings and then change it whenever you run a SetWarnings
True/False? Then you can return the value anywhere you want. Sounds
to me like you're making this infinitely harder than it really is.

[MLH]

You guys are both right. I'll go with one of your suggestions.

Thx.

>
Better yet, write a function to do it and always use that function
to change it. E.g., make one optional parameter, and if you supply
the parameter, set it accordingly, and set a static variable
(internal to the function) to match the setting. When you want to
know what the status is, you can call it without the optional
argument and act on the returned value.

This is basically a way of using a function as a property. You could
define a custom property in a class module, but then you'd have to
refer to it as part of the class module. Unless you've alredy got a
class module in place for global settings, I'd go with the function.
However, if you're using a class module for storing and setting the
values of global settings, then it makes more sense to implement
this as a property Let/Get.

[MLH]

On a related topic...
In A97, if I click Tools, Options, the Edit/Find tab and
unselect Confirm Record Changes, Confirm Document
Deletions and Confirm Action Querirs - will A97 launch
as if DoCmd.SetWarnings False is in effect?

Or, are there other actions that SetWarnings' setting
modifies the behaviour of as well as these three?

A97 HELP says you can use the SetWarnings action
to turn system messages on or off. However, it does
not mention or imply that SetWarnings False is the same
as unselecting the three checkboxes described above.

I'm just wondering - seeiking opinions.

[Tim Marshall]

MLH wrote:

I'm wondering if VB receives any event notice
indicating that a confirmation dialog just opened?
That is, can I tell if a confirmation dialog WAS
encountered and then act on it?

Just use the execute method and hadnle errors. If no errors, the
statement was passed.

For example (this air code uses David's dblocal function for the
database variable):

Sub sWhatever()

dim strStage as string 'Stage we're at if error thrown
dim strS as string

on error goto err_proc

'Clear records

strStage = "Deleting Existing Records"
strs = "Delete * from tbl_table"
dblocal.execute strs, dbfailonerror

'Add new records

strStage = "Adding New Records"
strS = "Insert into tbl_table (blah, blah...."
dblocal.execute strs, dbfailonerror

Exit_Proc:

Exit Sub

Err_Proc:

select case err.number
case 13 'type mismatch
msgbox strstage & vbcrlf & vbcrlf & _
"Incorrect data type for field...."
resume exit_proc
case 3022 'PK violation, rejig PK and resume
<code to redo strS>
resume 'Or a msgbox saying stuff related to PK
case else
msgbox "Error " & err.number & " ' & err.description & _
vbcrlf & vbcrlf & "Encountered when " & strStage, _
vbCritical, "Error on Sub Whatever", _
err.helpfile, err.help.context
resume exit_proc
end select

End Sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me