Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error Handling within a loop...

    Hi everyone

    I have a problem which i am sure is quite straightforward to solve, but you know what it's like when you don't know the answer.... :-)

    OK. I have written some code to copy files at night. I use a table full of file locations and a Do Until..... Loop to work through thr recordset. This weekend i came across a problem. A user had moved/renamed one of the files and the code bombed out.

    I have found a function that checks for the existance of each file, and returns a boolean value depending on whether it exists or not. So far, so good......

    My problem is getting the code to exit the loop at that point on finding no file, and going back to the beginning of the Do Until, in other words moving down to the next record.

    I've tried Exit Do, but that just takes me right out of my looping procedure, and i'm not sure how to use the Resume statement.

    Here's the bit of my procedure where i think i need to put the new code:

    Set db = CurrentDb
    Set mytable = db.OpenRecordset("tblFilestoBackUp")

    If mytable.RecordCount = 0 Then
    Exit Sub
    Else
    mytable.MoveFirst
    If Not mytable.BOF Then
    Do Until mytable.EOF
    strOldFileName = mytable("FilestoBackUp")

    Dim intDirExist As Integer
    intDirExist = fIsFileDIR("FilestoBackUp")
    Select Case intDirExist
    Case 0

    ****NEW CODE HERE THAT STARTS NEW LOOP***

    End Select

    Code continues until recordset is completed................

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Error Handling within a loop...

    Hi Peter,
    I don't think you actually need any code for the Case 0 statement, you just need to put your processing code within a Case Else section of your select statement or use an If...Then - i.e.
    Set db = CurrentDb
    Set mytable = db.OpenRecordset("tblFilestoBackUp")

    If mytable.RecordCount = 0 Then
    Exit Sub
    Else
    mytable.MoveFirst
    If Not mytable.BOF Then
    Do Until mytable.EOF
    strOldFileName = mytable("FilestoBackUp")

    Dim intDirExist As Integer
    intDirExist = fIsFileDIR("FilestoBackUp")
    If intDirExist <> 0 Then
    Code processes the file
    End If
    Loop
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Handling within a loop...

    Hi Rory

    First of all, thank you for your prompt reply. I can see from your post that i am trying to over-complicate matters and understand exactly what you are getting at.

    I've modified my code to suit (exactly as your suggestion) using an If...Then statement inserting the End If just before the Loop statement.

    If the file exists, it gets copied as planned, and the code loops through to the next line in the table, but if the file does not exist, it loops but does not advance one lie putting me into an endless loop. Any ideas????????

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Handling within a loop...

    OK, sorted it. Put the End If before the mytable.movenext. Good Grief! Thanks for your help. My application is now one bit more idiot proof :-)

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Error Handling within a loop...

    Sorry, Peter - I should have been much clearer on that! Glad you got it sorted. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •