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?

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.

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.

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

Thx.

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.

MLH wrote:

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