Database Hanging - Echo & Set Warnings Macro functions

[Guest]

I'm using the Echo and Set Warnings features of a Macro to hide system
messages from a user.

The Macro lines are
Echo Yes
Set Warnings Yes
Open Query (to delete records)

My macro runs fine when these messages are set to Yes. I can use the Macro
in a Form On Open properties without problem. And open the form via a button
on another form.

When I change the Echo and Set Warnings settings to No, everything hangs:
The form won't open when accessed via a button on another form. The database
freezes.

I have two macros which include append queries and have the same problem
when I turn off the echo and set warnings.

Why is this?
Is there another way to hide unnecessary system messages from the user as
macros are running behind a form?
What do I need to do to correct the problem?

Thanks very much for your help.

 

[Guest]

Hi, Sorsche.

When I change the Echo and Set Warnings settings to No, everything hangs:
The form won't open when accessed via a button on another form. The database
freezes. .. . .
Why is this?

You've instructed Access not to repaint the screen, so you won't see what's
going on until you give Access the instruction to repaint the screen again.
Whenever the screen is not being repainted, it appears to "freeze." That
doesn't mean that your actions aren't being carried out, though. You just
don't get to watch the show.

What do I need to do to correct the problem?

The simplest thing would be to turn the Echo function back to Yes as soon as
the operation is done. However, I'd recommend that you not even use macros,
because if anything goes wrong, there's no error handling. That means you
won't be told what the problem is, and there's nothing you can do to fix it
even if you knew what it was. This may require closing Access and reopening
the database application -- at the risk of losing a bunch of work that hasn't
been saved.

Instead, I'd recommend using VBA code to avoid the warning messages, such as
"Are you sure you want to delete 4,391 record(s)?" To do so, you could run
the queries by selecting a button on a form that uses simple VBA code, such
as the following example:

Private Sub AppendNewOrdersBtn_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "qryAppendNewOrders", dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in AppendNewOrdersBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.... where AppendNewOrdersBtn is the name of the button and
qryAppendNewOrders is the name of the action query that appends records to an
existing table. A delete query is also an action query, so this same syntax
can be used for delete queries, too. If there's an error in executing the
action query (such as the required table is missing), then the user will see
the necessary error message. If there's no error, then the query will run
silently in the background.

Another option is to turn off the confirmation warnings. However, that
turns off the confirmation warnings for every Access database application,
not just the current one, so this may not be desirable. To turn off these
warnings, select the Tools -> Options menu to open the Options dialog window.
Select the "Edit/Find" tab and uncheck the "Action queries" checkbox.
Select the "OK" button to save your change.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Steve Schapel]

Sorsche,

Putting a SetWarnings/No action in your macro before the first of the
OpenQuery actions, will be sufficient to suppress the display of the
action query confirmation messages. There is no need to use the Echo
action at all.