CODEWITHSUNDEEP
ms-accessvba
OpiesDad
OpiesDad

Reputation: 3435

VBA Error Handler with "With" Statement

Without setting a Label, is it possible for a VBA Error Handler to resume at the beginning of a With statement?

For example:

Dim rst As DAO.Recordset
Set rst = Nothing

On Error GoTo ErrHand
With rst
    .AddNew
    !MyValue = 1
    .Update
    .Bookmark = .LastModified
End With

ErrHand:

If Err.Number <> 0 Then
    Call SetRST 'Assume this procedure opens the recordset appropriately
    Resume
End If
End Sub()

The code will cause an error on the ".AddNew" line, and then when it goes through the error handler will set the recordset, but then it will Resume on the ".AddNew" line. The problem is that it is still within the "With" statement where CommentRST Is Nothing. Is there a way to tell the error handler to resume at the "With RST" line instead or the ".AddNew" line without creating a label before the "With" statement or checking for a blank recordset first?

I know there are ways around this (as I've just suggested 2 of them), but am curious as to whether this is possible.

Upvotes: 3

Views: 603

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

A With block holds an instance of an object, and releases it at End With. If you jumped outside the With block, the object is gone.

So the answer is no, you can't Resume into the middle of a With block. well you actually can, but things get ugly and confusing.

This is a common misuse of the With statement you have here - you're merely using it because you're being lazy (no offense intended), and don't want to type rst. in front of every line that uses that object.

Proper usage would have the With block itself hold the reference, like this:

With GetRecordset 'a function that returns a recordset
    .AddNew
    !MyValue = 1
    .Update
    .Bookmark = .LastModified
    .Close
End With

..not that I'd recommend working with recordsets like this, but you get the idea ;)

Upvotes: 3

iDevlop
iDevlop

Reputation: 25262

Just add a byRef argument to SetRST.

e.g: 

Sub SetRST(byref myrs as recordset)
   'do stuff
   set myrs = ...

By the way, your error handling sample sucks: just add an Exit Sub before ErrHand:,
so you won't need to test if err.number<>0, because you know it will always be.

In your error handling, use: 

call SetRST rst

Edit:
I would prefer something like: 

If rst Is Nothing Then
    Set rst = something
End if
With rst
   'continue here

Upvotes: 4

David Zemens
David Zemens

Reputation: 53623

In this case, how would the compiler know to resume at the beginning of the With block (and not at some other point)? It would not, and although it may be logically connected (i.e., it's within the With block) that's still no reason to assume by rule that execution should resume at the start of that block, without explicit reference to resume at that point.

What you're asking essentially assumes the source of the error, and then expects that VBA has this assumption built in, but it would certainly not be applicable for all or even most cases, consider below, assume the rst is open/etc., the actual error raises at the Bookmark property, your error handler doesn't account for that, and so resuming at the beginning of the block will cause infinite failure loop!

On Error GoTo ErrHand
With rst
    .AddNew
    !MyValue = 1
    .Update
    .Bookmark = "George Washington"
End With

See the documentation on the Resume statement:

https://msdn.microsoft.com/en-us/library/office/gg251630.aspx

There are your options to Resume

If the error occurred in the same procedure as the error handler, execution resumes with the statement that caused the error. If the error occurred in a called procedure, execution resumes at the statement that last called out of the procedure containing the error-handling routine.

Or to Resume Next:

If the error occurred in the same procedure as the error handler, execution resumes with the statement immediately following the statement that caused the error. If the error occurred in a called procedure, execution resumes with the statement immediately following the statement that last called out of the procedure containing the error-handling routine (or On Error Resume Next statement).

Or to Resume <line>

Execution resumes at line specified in the required line argument. The line argument is a line label or line number and must be in the same procedure as the error handler.

Upvotes: 3

Related Questions