For Each Next Problem

[TonyRandom]

Hello Everyone,
I am having a spot if difficulty creating a process loop using the For Each Next procedure.
I basically have a field in a table that stores a file location, This is passed to a query which has two fields. One field is the original file location, and the other is a new file path and filname based on the primary key for the record.
The trouble is that it performs the required action on the first record in the query, and then exits the loop before processing any subsequent records.

Here is the code I am trying to use, please can anyone help.

Function CopyFiles()
Dim strImageLoc As String
Dim strMoveToFilename As String
Dim retval As String
Dim strSQL As String
Dim strText As Variant
Dim Prefix As String
Dim Suffix As String
Dim db As DAO.Database, rst As DAO.Recordset
 
strSQL = "SELECT qryImageCopyTo.strImageLoc, qryImageCopyTo.strMoveToFilename " _
& "FROM qryImageCopyTo;"
 
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
strText = rst("strImageLoc")
 
On Error Resume Next
 
For Each strText In rst
  FileCopy rst("strImageLoc"), rst("strMoveToFilename")
Next strText
 
 
End Function

I am new to using loops, so I have probably made a real Newbie error.

Regards

Tony

[Stewart Ross]

Hi Tony. A FOR loop isn't the right one for this application, and FOR..EACH would apply to an array or a Collection of some kind (such as looping through the Forms collection to display the names of all forms).

Replace lines 21-23 above with:

Do While Not rst.EOF
    FileCopy rst("strImageLoc"), rst("strMoveToFilename")
    rst.Movenext
Loop 
rst.Close

Note also that if you open a recordset you must explicitly close it again, hence the Close immediately after the loop.

-Stewart
ps line 17 does not seem to be required - you should remove it, as the recordset's end of file (EOF) status is not tested before accessing the recordset at that point in your code. If you were to try to read the string and the recordset was empty (no records) you would get a run-time error at that point.

[TonyRandom]

Thanks Stewart,
I did say I am a newbie to the process loops, and this has been bugging me all afternoon. It worked first time.

Thanks again!

Tony