Bypassing Pop-Up Dialogs

[PatK]

I have a series of queries I must run on a monthly basis, that are make-table
type. The tables created already "exist." I have button control on a form,
that, when pressed, it initiated the series of about 10 queries that create
all the tables.

The problem is that I get a pop-up telling me, for example, that it is going
to overwrite the data in the output table, and then, at the end, it asks me
if it is ok to write all the rows into the table.

My response are always "yes" to all 30+ of these queries pop-ups. Is it
possible to have change a setting to automatically bypass these (what I refer
to as) cautionary messages?

I could just proceed each query with some code to delete the output tables,
and I may do that, yet (once I figure out how), but thought to keep it
simple, if that is possible, and see if there is just a way to supply a
default "yes" answer to all these prompts. Ideas?

On a secondary note, assuming I go into this "invisible"mode, running 10
queries, it would be great to have some sort of "status" output at the bottom
of the access window, showing, for example, I am at step 2 of 10, etc. Is
there a way to pop something out that does not require user intervention
using status options in access? (if this make any sense?)

thanks!

Patk.

 

[Jason Lepack]

look up docmd.showwarnings

 

[George Nicholson]

1) You can turn off the Access "cautionaries" with Set Warnings. Be sure to
turn them back on.
BTW, deleting tables generates warnings also.

You could also use:
CurrentDB.Execute "queryName", dbFailOnError
to run your queries. I believe this bypasses the Access warnings since it
uses a DAO command "outside" of Access's knowledge.

You could also (but don't):
Tools>Options>Edit/Find and uncheck the 3 "Confirm" options.

2) SysCmd provides a means to send a message to the Status bar at the bottom
of the Access window (or use it as a progress meter...). Be sure to clear
it when you are done.



Private Sub MyButton_Click()
Dim varSysCmdRef As Variant

DoCmd.SetWarnings False

varSysCmdRef = SysCmd(acSysCmdSetStatus, "Running 1 of 10")
DoEvents
'(Run query1)

'...repeat for queries 2-9

varSysCmdRef = SysCmd(acSysCmdSetStatus, "Running 10 of 10")
DoEvents
'(Run query10)

DoCmd.SetWarnings True
varSysCmdRef = SysCmd(acSysCmdSetStatus, " ")
End Sub

 

[PatK]

Thanks Jason/George! This helps (and I agree about not turning off the
confirmation messages...I just wanted to do it for this set of queries). I
will give these ideas a shot!

Patk

 

[PatK]

the:

docmd.setwarnings False (or True) did the trick. Thanks for the pointers.
I really appreciate it.

PatK