By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

For Each Next Problem

P: 12
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.

Expand|Select|Wrap|Line Numbers
  1. Function CopyFiles()
  2. Dim strImageLoc As String
  3. Dim strMoveToFilename As String
  4. Dim retval As String
  5. Dim strSQL As String
  6. Dim strText As Variant
  7. Dim Prefix As String
  8. Dim Suffix As String
  9. Dim db As DAO.Database, rst As DAO.Recordset
  11. strSQL = "SELECT qryImageCopyTo.strImageLoc, qryImageCopyTo.strMoveToFilename " _
  12. & "FROM qryImageCopyTo;"
  14. Set db = CurrentDb
  15. Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
  17. strText = rst("strImageLoc")
  19. On Error Resume Next
  21. For Each strText In rst
  22.   FileCopy rst("strImageLoc"), rst("strMoveToFilename")
  23. Next strText
  26. End Function
I am new to using loops, so I have probably made a real Newbie error.


Mar 16 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. Do While Not rst.EOF
  2.     FileCopy rst("strImageLoc"), rst("strMoveToFilename")
  3.     rst.Movenext
  4. Loop 
  5. rst.Close
Note also that if you open a recordset you must explicitly close it again, hence the Close immediately after the loop.

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.
Mar 16 '08 #2

P: 12
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!

Mar 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.