Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 04-10-2009, 10:28 AM   #1
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
resume loop at next loop

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?

__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wazz is offline   Reply With Quote
Old 04-10-2009, 10:34 AM   #2
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
Re: resume loop at next loop

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.
__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wazz is offline   Reply With Quote
Old 04-10-2009, 10:38 AM   #3
ByteMyzer
AWF VIP
 
Join Date: May 2004
Location: United States
Posts: 1,385
Thanks: 0
Thanked 48 Times in 43 Posts
ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough
Re: resume loop at next loop

Try something like the following:
Code:
Sub Err_Test()

On Error GoTo Err_Handler

Dim X As Long

    For X = 1 To 10

        ' Insert your loop code here

Skip:
    Next X

    Exit Sub

Err_Handler:
    ' Insert your Error Handling Code here

    Resume Skip

End Sub

ByteMyzer is offline   Reply With Quote
Old 04-10-2009, 11:35 AM   #4
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Re: resume loop at next loop

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?
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 04-10-2009, 11:45 AM   #5
ByteMyzer
AWF VIP
 
Join Date: May 2004
Location: United States
Posts: 1,385
Thanks: 0
Thanked 48 Times in 43 Posts
ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough
Re: resume loop at next loop

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".
ByteMyzer is offline   Reply With Quote
Old 04-10-2009, 12:05 PM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,744
Thanks: 55
Thanked 1,017 Times in 983 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: resume loop at next loop

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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 04-10-2009, 12:44 PM   #7
ByteMyzer
AWF VIP
 
Join Date: May 2004
Location: United States
Posts: 1,385
Thanks: 0
Thanked 48 Times in 43 Posts
ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough
Re: resume loop at next loop

Yes, something like:
Code:
Dim rs As DAO.Recordset
Dim X As Long


    Set rs = DBEngine(0)(0).OpenRecordset("SELECT ...")

On Error GoTo Err_RS_Skip
    Do While Not rs.EOF

        ' Insert recordset code here

Err_RS_Skip:
        If Not rs.EOF Then rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing


On Error GoTo Err_For_Next_Skip
    For X = 1 To 100

        ' Insert For-Next code here

Err_For_Next_Skip:
    Next X

ByteMyzer is offline   Reply With Quote
Old 04-10-2009, 09:28 PM   #8
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
Re: resume loop at next loop

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()
'Code written by wazz 2008-06-29 18:28
'Last modified by wazz
'2008-06-29: - needs work. getting error 3265 on:
'               Debug.Print "Document: " & contLoop.Documents(0).Name
'               "item not found in this collection."
'2009-04-11: - added label.
 
On Error GoTo ErrorHandler
 
    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
        'Debug.Print db.Containers(0).Name
        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...
__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by wazz; 04-10-2009 at 09:41 PM.
wazz is offline   Reply With Quote
Old 04-10-2009, 10:12 PM   #9
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
Re: resume loop at next loop

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
 
    'Loop all containers.
    For iCtrLoop = 0 To db.Containers.Count - 1
        With db.Containers(iCtrLoop)
            'Display the container name.
            Debug.Print "Container: " & .Name
            'If count value is zero, there are no objects in the collection.
            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
__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by wazz; 04-11-2009 at 12:28 AM. Reason: added 'With db.Containers(iCtrLoop)' and changed loop var name.
wazz is offline   Reply With Quote
Old 04-12-2009, 06:10 AM   #10
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,744
Thanks: 55
Thanked 1,017 Times in 983 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: resume loop at next loop

Quote:
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

Quote:
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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
NauticalGent (10-07-2017)
Old 04-12-2009, 12:05 PM   #11
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
Re: resume loop at next loop

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).
__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wazz is offline   Reply With Quote
Old 04-12-2009, 12:47 PM   #12
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
Re: resume loop at next loop

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.
__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wazz is offline   Reply With Quote
Old 04-12-2009, 03:59 PM   #13
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,744
Thanks: 55
Thanked 1,017 Times in 983 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: resume loop at next loop

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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 04-12-2009, 11:35 PM   #14
wazz
Super Moderator
 
wazz's Avatar
 
Join Date: Jun 2004
Location: Vancouver, BC, Canada.
Posts: 1,711
Thanks: 0
Thanked 3 Times in 3 Posts
wazz will become famous soon enough
Re: resume loop at next loop

yep. tnx.
__________________
Access 2003 / XP Pro
"We draw our own designs
But fortune has to make that frame." - N. Peart

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wazz is offline   Reply With Quote
Old 10-07-2017, 08:36 PM   #15
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,741
Thanks: 379
Thanked 231 Times in 202 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: resume loop at next loop

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!

__________________
“I had the RIGHT to remain silent...but I didn’t have the ABILITY.” - Ron White
NauticalGent is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Resume Statement KenHigg Modules & VBA 3 02-05-2009 10:36 AM
Cant Use find and Replace now error coolcatkelso Forms 1 01-17-2009 03:58 PM
Data type mismatch.. akw20 Modules & VBA 8 12-20-2008 12:31 AM
How do I get my form to automatically enter new data? BenW Forms 6 07-16-2008 12:23 PM
Ambigious Name Help fenhow Modules & VBA 12 12-17-2007 02:48 PM




All times are GMT -8. The time now is 07:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World