Last week, I added a simple procedure to an existing Access database. While testing it, I noticed that VBA was ignoring the error-handling statement and routine. I ran a quick Compile and Repair and crossed my fingers, but VBA was still breaking at errors instead of passing control to the procedure’s error-handling routine.

I sent out a What’s going on? e-mail to several colleagues. I pulled up Microsoft’s Knowledgebase and began searching for answers. Finally, I sacrificed my favorite SQL book upon my altar to the Access gods.

I guess the gods were appeased because within minutes, friend and developer extraordinaire Jim Dettman responded to my e-mail and told me to check the Error Trapping settings in the Visual Basic Editor (VBE). I found Break On Unhandled Errors cleared and Break On All Errors checked. I checked On Unhandled Errors, and VBA executed my error-handling routines as expected.

I felt stupid for not thinking of it myself. How the option changed, I’ll probably never know. I certainly didn’t do it.

If you’d like to try it yourself, do the following:

  1. In the VBE, choose Options from the Tools menu.
  2. On the General tab, check Break On All Errors in the Error Trapping Section.
  3. Click OK.
  4. Run any routine that’s apt to return an error, as long as it has an error-handling routine. If necessary, create a temporary procedure that uses an Err.Raise statement.
  5. Repeat steps 1 through 3 to reset Break On Unhandled Errors.

When writing a procedure, you’ll want to add its error-handling statements. However, while developing and testing code, it’s good to turn off error trapping so you can experience all the errors. Instead of commenting out all the error-handling statements and routines, set Break On All Errors. VBA will ignore your error-handling statements and break on every error. That way, you’ll know whether you’re trapping everything appropriately. When you’re done, simply reset the option so your error handling can take over.

Just remember that the settings exist and you’ll avoid an unnecessary panic if your error trapping suddenly stops performing as expected.