Error handling while using Application.Echo method

Question

I was having a problem of screen flickering when running a module. Then decided to used the method Application.Echo However, I have notice that, using Application.Echo method without Error handling causes my screen to go blank if indeed an error occurs within the module. As a result, I have thought of two approaches and would like to know which approach would be more efficient and if indeed these are the right ways of dealing with this kind of problem.

Approach 1:

Sub loopThrough()
On Error GoTo ErrorHandler
Me.Requery
Application.Echo False

'A for loop here........

 Application.Echo True

 exitErr:
 Application.Echo True
 Exit Sub

ErrorHandler: MsgBox Err.Description
GoTo exitErr

End Sub

Approach 2:

Sub loopThrough()
On Error Resume Next
Me.Requery
Application.Echo False

'A for loop here........

Application.Echo True
End Sub

Erik A · Accepted Answer · 2020-07-02 12:56:06Z

3

Never go for approach 2, many things can go wrong here!

Take the following example code:

Sub loopThrough()
On Error Resume Next
Me.Requery
Application.Echo False
'Append new data to table
CurrentDb.Execute "INSERT INTO MyTable SELECT * FROM NewData", dbFailOnError
'Truncate the new data table, this data has been appended
CurrentDb.Execute "DELETE * FROM NewData"
Application.Echo True
End Sub

Say NewData contained an entry that could not fit into MyTable. That operation fails, we continue on, truncate NewData, poof, data gone without a trace.

Did an error occur? Not a clue, because we didn't get notified of an error probably not? Oh, wait, there's missing data! How did that happen?

If you take your first approach here, you:

Get an error message
Don't delete your data if there's an error
Have code that's a tiny bit lengthier

Do remind yourself not to call Application.Echo True twice, that's not necessary.

The usual structure in VBA for proper code is:

Public Sub SomeSub()
    On Error GoTo ErrHandler
    'Usual code here

ExitHandler:
    'Perform operations needed when exiting, e.g. close open connections, set Application.Echo to true
    'Then exit:
    Exit Sub
ErrHandler:
    'Report error here
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Note that I use the Resume statement. That's specifically intended to jump out of an error handler. As far as I know it has no direct benefits outside of being syntactically clear, but I'm not 100% sure of that.

answered Jul 2, 2020 at 12:56

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fil Over a year ago

Thanks for making it clear. However, the Resume statement does not seems to disable the process triggered by the Application.Echo False without using another Application.Echo True in the 'ExitHandler. This makes the use of Application.Echo True` twice inevitable and this makes me wonder.

Erik A Over a year ago

The twice here referred to in your normal script before the exit handler, and in the exit handler itself. If you need to display error messages in a messagebox then you can't get around it, but if you log errors/have a form to display errors/any other reasonable approach then you don't need to do this twice.

Fil Over a year ago

I see. Thank you

Collectives™ on Stack Overflow

Error handling while using Application.Echo method

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related