On Error GoTo not working; Code breaks

Question

I am writing a VBA function to import data from one table to another in Access. The table I'm importing into has more strict data constraints (i.e. types, size etc.), so I'm expecting a lot of errors.

Rather than sift through every VBA error that comes up, I want my recordset loop to skip the entire current record and make a note of it in a separate table whenever it runs into an error. So every other line I've inserted On Error GoTo RecordError. But for some reason it's not handling every error. My code just breaks and tells me what the error is. I have the "Break on Unhandled Exceptions" option checked already.

Here's a screenshot that should explain it.

Why would it be breaking on the line immediately following an Error handler?

Answer 1

I think you're not understanding how VB(A) error handling works. Follow these principles:

• An On Error... statement only applies to the routine (Sub or Function) in which it appears (though it will also catch errors that "bubble up" from routines that are called from within the routine in which you use it).
• On Error sets a state. That is, Once you issue an On Error... it remains in force for the rest of the routine, unless superceded by a new On Error....

• There are four forms of On Error...:

  1. On Error GoTo <label>: <label> must be defined in the same routine, by writing the label name immediately followed by a colon (:) on a line by itself.
  2. On Error Resume: immediately retries the error-throwing statement. Hardly ever used, since it's potentially infinite.
  3. On Error Resume Next: ignores the error & continues. Sometimes useful at the end of routines for cleanup (for instance, if you want to Close a Recordset that may or may not be open). Alternatively, this form can also be used if you check the Err object immediately after any potentially error-throwing line (if Err.Number is zero (0), the statement succeeded without throwing an error). This is way too much work for most situations.
  4. On Error GoTo 0: turns off error handling.

Given this, it's usual to place the On Error... statement immediately followng the routine's declaration (the Sub or Function statement), though some people put their Dim statements in between. If you want to temporarily change the manner of error handling within a routine, put the "new" one right before the code to which it is to apply, and (if used), the "revert" (reissuing the original), right after.

Even given all that, I have no idea why it would break on the error-throwing line when "Break on Unhandled Errors" is selected, unless you've managed to confuse it so much that it thinks there's no active error handling (and I'd be surprised if it compiled if that were the case).

Note that David Heffernan gave you the essential part of this in his answer, and it was here before mine....

Answer 2

The reason it is not working is because you cannot use On Error Goto ... within an error handler.

see http://www.cpearson.com/excel/errorhandling.htm

you cannot use On Error to skip a few lines, instead on error should go to a error handler which then resume's to the desired next line (in your example you could probably get away with one error handler which contains a resume next which will take you back to the next field).

thanks to Tim Williams on this question: The second of 2 'On Error goto ' statements gets ignored

and BTW ParseInt on a ZIP will destroy zip codes that begin with a 0, zipcodes should probably be treated as text.