Extra code in On Error

[Stoss]

Hello all,

I have a question about adding in code in the error handling.

How does the error handling execute the code. For example, in the following code, I want to delete the querydef if there was a problem executing it. I understand that there are a million ways to handle this and this isn't probably the best way but I have had other situations that this would have worked great for (but didn't for the same reason).

So, if I add in the line to delete the querydef it gives me an error. My question is, what code is executed and when? I thought that the code under DRStats_Err_Exit: would not execute unless it was told to, is this correct? But it seems that if I try to add any code in there it will always run.

On Error GoTo DRStats_Err

    ....all misc code here
    
DRStats_Err_Exit:
    'THE NEXT LINE IS IN QUESTION....
    dbs.QueryDefs.Delete strQName
    Exit Sub

DRStats_Err:
    MsgBox Error$
    Resume DRStats_Err_Exit

Thanks,
Stoss

 

[pbaldy]

The code in the exit handler (your DRStats_Err_Exit) will typically always run, up until the Exit Sub. It is code in the error handler that will not run unless there's an error.

 

[Stoss]

So, why does the code in the error handler run only if there is a problem but the error exit code always runs? How does the compiler distinguish between the two?

So, in this case, if I put the code to delete the querydef in the Error section before "resume exit" it would work and not give me a problem if there was in fact NO error in the execution.

-Stoss

 

[SpentGeezer]

Just put Exit Sub above your error handling code. That wont run unless there is an error.

On Error GoTo DRStats_Err

....all misc code here

Exit Sub

DRStats_Err_Exit:
'THE NEXT LINE IS IN QUESTION....
dbs.QueryDefs.Delete strQName
Exit Sub

DRStats_Err:
MsgBox Error$
Resume DRStats_Err_Exit

 

[pbaldy]

Code goes where it's told to go. In your case, it runs into and through the exit handler until it hits the Exit Sub (which stops it from running into the error handler). I actually disagree with Geezer's suggestion, generally speaking. Part of the reason for having an exit handler is to "clean up", so you might have code to set variables to Nothing, etc. It would be more common to have "GoTo DRStats_Err_Exit" to direct code to the exit handler than to avoid it. In your case, there might be a more appropriate place to delete the QueryDef, or perhaps you should test for it existing before deleting. Or you could trap for the specific error thrown when deleting a non-existent object and use Resume Next instead of Resume DRStats_Err_Exit. I have a very basic primer on error handlers here:

http://www.baldyweb.com/ErrorTrap.htm

 

[vbaInet]

To support pbaldy's error handling tips:

http://www.fmsinc.com/tpapers/vbacode/debug.asp

http://support.microsoft.com/kb/146864

 

[Stoss]

Thanks all for you help.

I used the exit sub before it got to the error handling and that seems like a good way to go. It makes it easy to read and does exactly what I need it to.

Thanks Again!
-Stoss