3

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.

Improve this question
12
  • Why not check the recordset before adding? Commented Feb 25, 2015 at 17:25
  • 1
    Are you handling it this way because your top rst might not be open when you call .AddNew? Commented Feb 25, 2015 at 17:25
  • stackoverflow.com/questions/17611545/… Commented Feb 25, 2015 at 17:27
  • @Brad why ADO with MS Access? stackoverflow.com/questions/2631678/vba-check-if-object-is-set Commented Feb 25, 2015 at 17:28
  • 1
    You're asking whether there is something like Resume Previous (there is not). The code executes sequentially and would not go "backwards" or to some previous point without explicitly being told where to go. In this case, how would the compiler know to resume at the beginning of the With block (and not at some other point)? Commented Feb 25, 2015 at 17:46

3 Answers 3

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I never write the 'Call', which is useless, I just wrote it here to keep asker's style. You're right, it's useless and (therefore) unelegant.
Sorry for the confusing code. I understand that the code as written would not open the recordset. This, however, isn't the issue I was having. Even if I added the ByRef arg, this still wouldn't work as when the error handler resumes, it will still resume on the ".AddNew" line which is referencing the Nothing recordset.
@OpiesDad: yes it would
@OpiesDad pass parameters instead of using globals, most of your problems will start fading away...
I like you. We could always use more VBA Reviewers on Code Review if you get tired of SO.
3

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.

Improve this answer

Comments

3

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 ;)

Improve this answer

2 Comments

You can Resume into a with block as long as the error handler doesn't execute an End With.
@DavidZemens ouch! I'd rather keep my innocence and believe you can't do that... wouldn't that smell terrible?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.