What error handling code do you suggest?
Here's a skeleton:
'====================
Private Sub Form_Close()
On Error Goto Err_Proc
'put all your normal code here
Exit_Proc:
'cleanup
Exit Sub
Err_Proc:
MsgBox "Error " & Str(Err.Number) _
& " - " & Err.Description
Resume Exit_Proc
Resume
End Sub
'====================
This is a simple-as-possible error handler (the best way to go, IMO). It
will display a message stating the error number and the dscription of the
error. Here's a breakdown on how it works and why you need what's there:
On Error Goto Err_Proc
This should always be the first line after the sub/function declaration. It
tells your procedure that when an error occurs, go to the line label
Err_Proc. Any errors with the exception of something that the Access
developers might have missed will now be directed to your "handler"
Exit_Proc:
Exit Sub
This is your exit point for the sub... if anything goes wrong anywhere in
your code, or you happen to meet a condition and no longer need the rest of
the code to run, always direct your code here by using Goto Exit_Proc. Put
any "cleanup" code here: ex. closing recordsets and setting objects to
Nothing so they aren't hanging around in memory waiting to confuse Access and
screw things up.
Resume Exit_Proc
The Resume statement is used in conjunction with errors... this simply tells
your code to go to the label Exit_Proc (which you will be closing out any
open objects, etc. before it hits the Exit Sub line). With no intervention
from you, this line will always be run on an error... so your procedure can
elegantly exit.
Error_Proc:
This is the *label* that defines your error handler... per the line On Error
Goto Err_Proc, all errors will be handled in the code that follows this label.
The msgbox line should be self-explanitory. But it also has a second very
important use... it stops your code until you click OK, giving you means to
enter your code for debugging (Alt + F11 opens the code window... now drag
the yellow arrow to a line you want to execute and hit F8 to run the line).
Resume
This one is key for debugging... under normal circumstances, this line will
never be executed (the line before it will always redirect to the exit point
of the procedure). A simple Resume statement (as opposed to Resume <label>
or Resume Next) will send the code to the line that caused the error. So
here's the debugging procedure...
- Add the error handler
- Recreate the error
- When the MsgBox pops up with the number and description:
- Hit Alt+F11 to open the vba window
- your MsgBox line in the error handler should be highligted in yellow, with
a little yellow arrow at the left
- Drap/Drop the yellow arrow on the left to the Resume statement
- Hit F8 (to proccess that particular line of code)
- your code should jump back to the line in the procedure that caused the
error, allowing you to pinpoint the source of the error.
Keep in mind that this won't tell us WHAT the problem IS, but simply where
the problem is coming from... but that is always the first step to to
resolving any issue.
Error handling should be included in every sub or function you write. Go to
www.mztools.com to download a complete free addin for the VBE that will add
your customized error handler at the click of a button. This addin is
recommended by hundreds (thousands?) of developers... in fact, one fan states
that he loves MZTools so much he will name his firstborn son MZTools
(hahaha). Seriously though, it's that good... no hidden crap, just an awsome
awsome tool.
So do this and let me know how you make out. If, by some strange chance,
you are still not able to catch the error by this method, don't lose hope...
there's other but far more invloved) ways. I doubt that will be the case
though. If this doesn't work then there is a problem with Access, not your
code. Post back, we'll get this one figured out!
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
