Loop through Table while Renaming Files

N

Nick X

Adapted from:
http://www.fmsinc.com/FRee/NewTips/Access/accesstip31.asp

I need to loop through the a table to make the following changes:
If [Photo1] Like "Photo*" Then
fOK = RenameFileOrDir([Path1] & [Photo1], _
[Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _
[Path1] & [TAXPINNO] & "_" & [Round] & "_P1.jpg", _
[Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P1.jpg"))

End If

If [Photo2] Like "Photo*" Then
fOK = RenameFileOrDir([Path1] & [Photo2], _
[Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _
[Path1] & [TAXPINNO] & "_" & [Round] & "_P2.jpg", _
[Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P2.jpg"))
End If

Then I can set the PhotoEdit field to True.

Thanks,
Nick
 
J

John W. Vinson

Adapted from:
http://www.fmsinc.com/FRee/NewTips/Access/accesstip31.asp

I need to loop through the a table to make the following changes:
If [Photo1] Like "Photo*" Then
fOK = RenameFileOrDir([Path1] & [Photo1], _
[Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _
[Path1] & [TAXPINNO] & "_" & [Round] & "_P1.jpg", _
[Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P1.jpg"))

End If

If [Photo2] Like "Photo*" Then
fOK = RenameFileOrDir([Path1] & [Photo2], _
[Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _
[Path1] & [TAXPINNO] & "_" & [Round] & "_P2.jpg", _
[Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P2.jpg"))
End If

Then I can set the PhotoEdit field to True.

Thanks,
Nick
Click to expand...

You can't loop through a table per se, but you can create a Recordset based on
the table and loo through that. You don't indicate anything about your table
name or much about its structure, but it would be something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String
Set db = CurrentDb
strSQL = "SELECT * FROM yourtable WHERE [Photo1] LIKE "Photo*" _
& " OR [Photo2] LIKE "Photo*" ' prefilter the records
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.EOF
If rs![Photo1] LIKE "Photo*" Then
<your code>
End If
If rs![Photo2] LIKE "Photo*" Then ' what if they are BOTH true???
<your code>
End If
rs.MoveNext
Loop
 
N

Nick X

John,
Thank you. Your answer helped me properly organize my code. Now on to the
next issue. (I will create a new post if I can't figure this one out)
Thanks,
Nick

John W. Vinson said:
Adapted from:
http://www.fmsinc.com/FRee/NewTips/Access/accesstip31.asp

I need to loop through the a table to make the following changes:
If [Photo1] Like "Photo*" Then
fOK = RenameFileOrDir([Path1] & [Photo1], _
[Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _
[Path1] & [TAXPINNO] & "_" & [Round] & "_P1.jpg", _
[Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P1.jpg"))

End If

If [Photo2] Like "Photo*" Then
fOK = RenameFileOrDir([Path1] & [Photo2], _
[Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _
[Path1] & [TAXPINNO] & "_" & [Round] & "_P2.jpg", _
[Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P2.jpg"))
End If

Then I can set the PhotoEdit field to True.

Thanks,
Nick
Click to expand...

You can't loop through a table per se, but you can create a Recordset based on
the table and loo through that. You don't indicate anything about your table
name or much about its structure, but it would be something like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String
Set db = CurrentDb
strSQL = "SELECT * FROM yourtable WHERE [Photo1] LIKE "Photo*" _
& " OR [Photo2] LIKE "Photo*" ' prefilter the records
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.EOF
If rs![Photo1] LIKE "Photo*" Then
<your code>
End If
If rs![Photo2] LIKE "Photo*" Then ' what if they are BOTH true???
<your code>
End If
rs.MoveNext
Loop
Click to expand...
 
N

Nick X

Working code:

Private Sub cmdTest_Click()
Dim fOK As Boolean
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

strSQL = "SELECT " & _
"tblVLM_Inspection_Archive.Path1, tblVLM_Inspection_Archive.Photo1,
tblVLM_Inspection_Archive.Photo2, " & _
"tblVLM_Inspection_Archive.Photo3, tblVLM_Inspection_Archive.Photo4,
tblVLM_Inspection_Archive.K_PID, " & _
"tblVLM_Inspection_Archive.TAXPINNO,
tblVLM_Inspection_Archive.Round, tblVLM_Inspection_Archive.PhotoEdit, " & _
"tblVLM_Inspection_Archive.PhotoNameArchive" & _
"FROM tblVLM_Inspection_Archive " & _
"WHERE (((tblVLM_Inspection_Archive.PhotoEdit)=0)) "

Set rs = db.OpenRecordset(strSQL)

With rs
If .EOF = False And .BOF = False Then
.MoveFirst
Do While .EOF = False
.Edit

rs!PhotoNameArchive = rs![Photo1] + ", " + rs![Photo2] +
", " + rs![Photo3] + ", " + rs![Photo4]

If rs![Photo1] Like "Photo*" Then
If IsNull(rs![K_PID]) Or rs![K_PID] = "" Or
rs![K_PID] = " " Then
fOK = RenameFileOrDir(rs![Path1] & rs![Photo1],
rs![Path1] & rs![TAXPINNO] & "_" & rs![Round] & "_P1.jpg")
rs![Photo1] = rs![TAXPINNO] & "_" & rs![Round] &
"_P1.jpg"
Else
fOK = RenameFileOrDir(rs![Path1] & rs![Photo1],
rs![Path1] & rs![K_PID] & "_" & rs![TAXPINNO] & "_" & rs![Round] & "_P1.jpg")
rs![Photo1] = rs![K_PID] & "_" & rs![TAXPINNO] &
"_" & rs![Round] & "_P1.jpg"
End If
End If

If rs![Photo2] Like "Photo*" Then
If IsNull(rs![K_PID]) Or rs![K_PID] = "" Or
rs![K_PID] = " " Then
fOK = RenameFileOrDir(rs![Path1] & rs![Photo2],
rs![Path1] & rs![TAXPINNO] & "_" & rs![Round] & "_P2.jpg")
rs![Photo2] = rs![TAXPINNO] & "_" & rs![Round] &
"_P2.jpg"
Else
fOK = RenameFileOrDir(rs![Path1] & rs![Photo2],
rs![Path1] & rs![K_PID] & "_" & rs![TAXPINNO] & "_" & rs![Round] & "_P2.jpg")
rs![Photo2] = rs![K_PID] & "_" & rs![TAXPINNO] &
"_" & rs![Round] & "_P2.jpg"
End If
End If

If rs![Photo3] Like "Photo*" Then
If IsNull(rs![K_PID]) Or rs![K_PID] = "" Or
rs![K_PID] = " " Then
fOK = RenameFileOrDir(rs![Path1] & rs![Photo3],
rs![Path1] & rs![TAXPINNO] & "_" & rs![Round] & "_P3.jpg")
rs![Photo3] = rs![TAXPINNO] & "_" & rs![Round] &
"_P3.jpg"
Else
fOK = RenameFileOrDir(rs![Path1] & rs![Photo3],
rs![Path1] & rs![K_PID] & "_" & rs![TAXPINNO] & "_" & rs![Round] & "_P3.jpg")
rs![Photo3] = rs![K_PID] & "_" & rs![TAXPINNO] &
"_" & rs![Round] & "_P3.jpg"
End If
End If

If rs![Photo4] Like "Photo*" Then
If IsNull(rs![K_PID]) Or rs![K_PID] = "" Or
rs![K_PID] = " " Then
fOK = RenameFileOrDir(rs![Path1] & rs![Photo4],
rs![Path1] & rs![TAXPINNO] & "_" & rs![Round] & "_P4.jpg")
rs![Photo4] = rs![TAXPINNO] & "_" & rs![Round] &
"_P4.jpg"
Else
fOK = RenameFileOrDir(rs![Path1] & rs![Photo4],
rs![Path1] & rs![K_PID] & "_" & rs![TAXPINNO] & "_" & rs![Round] & "_P4.jpg")
rs![Photo4] = rs![K_PID] & "_" & rs![TAXPINNO] &
"_" & rs![Round] & "_P4.jpg"
End If
End If

rs!PhotoEdit = 1
.Update
.MoveNext
Loop
End If
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append data / Prevent duplicates 4
do loop through a table 1
get a value from as we loop through table 2
Newbee - how to loop through table and delete unwanted records 2
Looping through Query to create multiple sheets in excel- Just need the loop 2
Codes Showing through in Browser 10

Top