renaming files through database, VBA help needed to Loop 1

[philwages]

I need to rename about 8000 files using field names from my database (actual names are [Image1] to [Image2]). I'm using the below code but it keeps giving me error messages on the Name As function. It can do 1 record (and sometimes up to 5 or 6) at a time but I'm trying to get it to loop until it hits the last record. The below code is based on the last record containing "zzzzzzzzz" as the last entry for field [title]. There may be a better way of going about this and I'm completely up to any suggestions. I "learned" some VBA just this last week in my Access database management class, so I'm now trying to put it to use, so be gentle! Here is the code I've used, let me know what I'm doing wrong:

Private Sub Rename_Button_Click()
Dim thisislastrecord As Boolean
thisislastrecord = False
Do
Name [Image1] As [Image2]
SendKeys "{PGDN}", 100
If [Title] = "zzzzzzzzz" Then
thisislastrecord = True
End If
Loop Until thisislastrecord = True
End Sub

 

[VBslammer]

I wouldn't rely on 'SendKeys' to handle record navigation. Try opening a recordset and using 'MoveNext' to cycle records:

Sub RenameImages()
On Error GoTo ErrHandler

  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strPath1 As String
  Dim strPath2 As String

  Set dbs = CurrentDb
  
  ' Open the table.
  Set rst = dbs.OpenRecordset("MyTable", dbOpenTable)
  
  ' loop through each record and attempt to rename.
  With rst
    While Not .EOF
      
      ' convert nulls to empty strings.
      strPath1 = Nz(.Fields("Image1"), "")
      strPath2 = Nz(.Fields("Image2"), "")
      
      ' attempt renaming only if both strings have length.
      If Len(strPath1) > 0 And Len(strPath2) > 0 Then
        Name strPath1 As strPath2
      End If
      
      ' next record.
      .MoveNext
      
    Wend
  End With

ExitHere:
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description, strPath1, strPath2
  Resume Next
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[philwages]

It works! Thanks a million!