Error Handling within a loop…

Topic

New Reply

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…………….

Reply | Quote

Replies

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.

Reply | Quote

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????????

Reply | Quote

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 🙂

Reply | Quote

Sorry, Peter – I should have been much clearer on that! Glad you got it sorted.

Reply | Quote