error handling

[Guest]

I have VBA code that has error handling. When the code is finished, my error
handling gives me andd error number 0. The process runs fine and there are
no errors. Any ideas about the error number 0?

Code:

Public Sub ExpUpdateDB(Working As String, Final As String)
On Error GoTo Err_ExpUpdateDB


DBEngine.CompactDatabase Working, "C:\temp.mdb"
Kill Working
FileCopy "C:\Temp.mdb", Working
FileCopy "C:\Temp.mdb", Final
Kill "C:\Temp.mdb"

Err_ExpUpdateDB:
MsgBox "Error Exporting Update DB: " & Final & ". Error Number: " &
Err.Number & " Error Description: " & Err.Description _
, , "Error"

 

[Jeff Conrad]

Hi Kim,

Your code has an error handler routine, but the only place to exit the routine is below the error.
Code will progress normally from top to bottom unless told otherwise. Your code completes
everything, but then reaches the error handler and you tell it to display an error whether or not
one actually occurred. You need to have an exit function(sub) area above the error handler.
The following illustration may help:

Public Function SomeFunction()
On Error GoTo ErrorPoint

' Regular code here

ExitPoint:
Exit Function

ErrorPoint:
' Some way to display or ignore the error
Resume ExitPoint

End Function

So once the regular code is finished it comes to the ExitPoint and is told to leave the function.
The ErrorPoint code should ONLY be reached if a true error has occurred. We then tell the code to go
back to the ExitPoint and exit gracefully. We generally want to have only one entry point for code
and one exit point. Does this make sense?

I would change your code to this:

Public Sub ExpUpdateDB(Working As String, Final As String)
On Error GoTo Err_ExpUpdateDB

DBEngine.CompactDatabase Working, "C:\temp.mdb"
Kill Working
FileCopy "C:\Temp.mdb", Working
FileCopy "C:\Temp.mdb", Final
Kill "C:\Temp.mdb"

ExitPoint:
Exit Sub

Err_ExpUpdateDB:
MsgBox "Error Exporting Update DB: " & Final & ". Error Number: " & _
Err.Number & " Error Description: " & Err.Description , , "Error"
Resume ExitPoint

End Sub

That should take care of it.

 

[Guest]

Thanks!

Hi Kim,

Your code has an error handler routine, but the only place to exit the routine is below the error.
Code will progress normally from top to bottom unless told otherwise. Your code completes
everything, but then reaches the error handler and you tell it to display an error whether or not
one actually occurred. You need to have an exit function(sub) area above the error handler.
The following illustration may help:

Public Function SomeFunction()
On Error GoTo ErrorPoint

' Regular code here

ExitPoint:
Exit Function

ErrorPoint:
' Some way to display or ignore the error
Resume ExitPoint

End Function

So once the regular code is finished it comes to the ExitPoint and is told to leave the function.
The ErrorPoint code should ONLY be reached if a true error has occurred. We then tell the code to go
back to the ExitPoint and exit gracefully. We generally want to have only one entry point for code
and one exit point. Does this make sense?

I would change your code to this:

Public Sub ExpUpdateDB(Working As String, Final As String)
On Error GoTo Err_ExpUpdateDB

DBEngine.CompactDatabase Working, "C:\temp.mdb"
Kill Working
FileCopy "C:\Temp.mdb", Working
FileCopy "C:\Temp.mdb", Final
Kill "C:\Temp.mdb"

ExitPoint:
Exit Sub

Err_ExpUpdateDB:
MsgBox "Error Exporting Update DB: " & Final & ". Error Number: " & _
Err.Number & " Error Description: " & Err.Description , , "Error"
Resume ExitPoint

End Sub

That should take care of it.

 

[Jeff Conrad]

You're welcome, glad to help.