resume loop at next loop (1 Viewer)

wazz

Super Moderator
Local time
Today, 21:13
Joined
Jun 29, 2004
Messages
1,711
if i get an error in the middle of a For...Next loop and go to an error handler, how can i skip what was left of that loop and resume the looping process at the top of the next loop?
 

wazz

Super Moderator
Local time
Today, 21:13
Joined
Jun 29, 2004
Messages
1,711
i couldn't quite get that to go. but i'm going to try it again and see where it takes me.

i did get it just now by putting a label just before the 'Next' line and used a GoTo 'label'. that works.

tnx.
 

ByteMyzer

AWF VIP
Local time
Today, 06:13
Joined
May 3, 2004
Messages
1,409
Try something like the following:
Code:
[COLOR="Navy"]Sub[/COLOR] Err_Test()

[COLOR="navy"]On Error GoTo[/COLOR] Err_Handler

[COLOR="navy"]Dim[/COLOR] X [COLOR="navy"]As Long

    For[/COLOR] X = 1 [COLOR="navy"]To[/COLOR] 10

        [COLOR="DarkGreen"]' Insert your loop code here[/COLOR]

Skip:
    [COLOR="navy"]Next[/COLOR] X

    [COLOR="navy"]Exit Sub[/COLOR]

Err_Handler:
    [COLOR="darkgreen"]' Insert your Error Handling Code here[/COLOR]

    [COLOR="navy"]Resume[/COLOR] Skip

[COLOR="navy"]End Sub[/COLOR]
 

Banana

split with a cherry atop.
Local time
Today, 06:13
Joined
Sep 1, 2005
Messages
6,318
Wouldn't it be easier just to do a Resume Next for that loop by either resetting error handler or branch it into a separate procedure with On Error Resume Next?
 

ByteMyzer

AWF VIP
Local time
Today, 06:13
Joined
May 3, 2004
Messages
1,409
No, not if he wants to "skip what was left of that loop and resume the looping process at the top of the next loop".
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Sep 12, 2006
Messages
15,613
but a label at the bottom of the loop, rather than the top (eg maybe immediately before a rst.movenext, or whatever manages the loop iteration)
(or even at any convenient place after the code that didnt execute as a result of the error

in the error handler put

resume thatloop

------
make sure that the error hanlder is reset somewhere in the loop, or you wont handle any further errors
 

ByteMyzer

AWF VIP
Local time
Today, 06:13
Joined
May 3, 2004
Messages
1,409
Yes, something like:
Code:
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="navy"]As[/COLOR] DAO.Recordset
[COLOR="navy"]Dim[/COLOR] X [COLOR="navy"]As Long


    Set[/COLOR] rs = DBEngine(0)(0).OpenRecordset("SELECT ...")

[COLOR="navy"]On Error GoTo[/COLOR] Err_RS_Skip
    [COLOR="navy"]Do While Not[/COLOR] rs.EOF

        [COLOR="darkgreen"]' Insert recordset code here[/COLOR]

Err_RS_Skip:
        [COLOR="navy"]If Not[/COLOR] rs.EOF [COLOR="navy"]Then[/COLOR] rs.MoveNext
    [COLOR="navy"]Loop[/COLOR]

    rs.Close
    [COLOR="navy"]Set[/COLOR] rs = [COLOR="navy"]Nothing


On Error GoTo[/COLOR] Err_For_Next_Skip
    [COLOR="navy"]For[/COLOR] X = 1 [COLOR="navy"]To[/COLOR] 100

        [COLOR="DarkGreen"]' Insert For-Next code here[/COLOR]

Err_For_Next_Skip:
    [COLOR="navy"]Next[/COLOR] X
 

wazz

Super Moderator
Local time
Today, 21:13
Joined
Jun 29, 2004
Messages
1,711
it seems the label is way to go. this is what i have now. i was just playing with some code from the help files on Documents Collection (Documents Collection (DAO)). the sample doesn't contain any error handling for missing item(s) so i was trying to figure out how best to add that (errors do arise).

still needs work but getting there and has helped me understand a little bit what the heck these documents and collections are. :)
Code:
Sub ContainerPropertyX()
[COLOR=green]'Code written by wazz 2008-06-29 18:28[/COLOR]
[COLOR=green]'Last modified by wazz[/COLOR]
[COLOR=green]'2008-06-29: - needs work. getting error 3265 on:[/COLOR]
[COLOR=green]'               Debug.Print "Document: " & contLoop.Documents(0).Name[/COLOR]
[COLOR=green]'               "item not found in this collection."[/COLOR]
[COLOR=green]'2009-04-11: - added label.[/COLOR]
 
On Error GoTo ErrorHandler
 
    Dim db As Database
    Dim contLoop As Container
 
    Set db = CurrentDb
 
[COLOR=green] ' Display the container name for the first Document[/COLOR]
[COLOR=green] ' object in each Container object's Documents collection.[/COLOR]
    For Each contLoop In db.Containers
        [COLOR=green]'Debug.Print db.Containers(0).Name[/COLOR]
        Debug.Print "Container: " & contLoop.Documents(0).Container
        Debug.Print "  Document(0): " & contLoop.Documents(0).Name
ResumeNext:
    Next contLoop
 
    db.Close
    Set db = Nothing
 
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err.Number = 3265 Then
        Debug.Print "doc(0) not found in this collection "
        GoTo ResumeNext
    End If
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
        "Error Description: " & Err.Description & vbCrLf & _
        "Procedure: ContainerPropertyX"
    Resume ErrorHandlerExit
    Resume
End Sub
btw, i tried putting the error handling inside the loop like ByteMyzer did, but the code (at this point) could error on both lines and i didn't want to add the handling more than once. haven't worked that out yet. (the one line inside the loop that is commented-out is part of trying to figure this out. you can ignore that for now but it might help in getting the error handling inside the loop...
 
Last edited:

wazz

Super Moderator
Local time
Today, 21:13
Joined
Jun 29, 2004
Messages
1,711
figured out the problem. much simpler now.
Documents Collection:
"The Count property setting is never Null. If its value is 0, there are no objects in the collection."

Code:
    Dim db As DAO.Database
    Dim iCtrLoop As Integer
 
    Set db = CurrentDb
 
    [COLOR=green]'Loop all containers.[/COLOR]
    For iCtrLoop = 0 To db.Containers.Count - 1
        With db.Containers(iCtrLoop)
            [COLOR=green]'Display the container name.[/COLOR]
            Debug.Print "Container: " & .Name
            [COLOR=green]'If count value is zero, there are no objects in the collection.[/COLOR]
            If .Documents.Count > 0 Then
                Debug.Print "  Document(0): " & .Documents(0).Name
            Else
                Debug.Print "  Container """ & .Name & """ contains no docs.  "
            End If
        End With
    Next iCtrLoop
 
    Set db = Nothing
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Sep 12, 2006
Messages
15,613
ErrorHandler:
If Err.Number = 3265 Then
Debug.Print "doc(0) not found in this collection "
GoTo ResumeNext
End If
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & vbCrLf & _
"Procedure: ContainerPropertyX"
Resume ErrorHandlerExit
Resume
End Sub

wass - you CANT do it like this - you have to say RESUME resumenext -you dont terminate the error handler until you issue a resume statmeent, and you cant reset the error handler while you are still in a error handler

On Error GoTo ErrorHandler 'see below

Dim db As Database
Dim contLoop As Container

Set db = CurrentDb

' Display the container name for the first Document
' object in each Container object's Documents collection.
For Each contLoop In db.Containers

On Error GoTo ErrorHandler 'my addition

secondly your code doesnt reset the error handler anyway, after the first error - you need the above line INSIDE the loop, so it gets reset each time, just in case
 

wazz

Super Moderator
Local time
Today, 21:13
Joined
Jun 29, 2004
Messages
1,711
i knew there was something fishy (i.e. wrong) about that but couldn't put my finger on it. i didn't see the difference between 'terminating' and 'resetting' the errhandler.

are you saying, the statement 'On Error GoTo ErrorHandler' is only good the first time it's used. once there's an error and you go back to the main body of code, the initial 'On Error GoTo ErrorHandler' doesn't apply? or is it just specific to this procedure and the way it's written (was written).
 

wazz

Super Moderator
Local time
Today, 21:13
Joined
Jun 29, 2004
Messages
1,711
just reading about the 'On Error Statement'. i think it's clear. 'On Error Resume Next' might be appropriate for the loop with an extra 'On Error GoTo' after the loop.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Sep 12, 2006
Messages
15,613
on error goto

transfers control to the error handler

in order to go back to the programme you HAVE to resume label:

you cant just goto label:

-------
the problem is, if you try to goto label, you havent yet terminated the eror handler, and therefore you cant set another error handler, because you stil have an active handler -

hope that makes sense
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 09:13
Joined
Apr 27, 2015
Messages
6,280
Dave, I realize this thread is over 8 years old, but this advice is spot on. I was google searching for the proper way to add error handling while looping through record sets and the help you gave WAZZ really saved me a few tantrums.

Grazie Mille!
 

Users who are viewing this thread

Top Bottom