If File Doesn't Exist, Update Recordset, Else Nothing

[foxhound]

Hello,

With the following code, I am trying to determine if a picture exists in a certain folder. If it does not exist, I want the current recordset field "sref" to be updated to "00000000.jpg" If it does exist, I would like to do nothing and loop to the next record. Any suggestions?

Sub UpdateTable()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim FileName As String
Dim Picture As String

Const FilePath = "C:\Data\Photos\"
Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ProductPhotos")
rst.MoveFirst
Do While Not rst.EOF
Picture = rst!xref
FileName = Dir(FilePath & Picture)
If FileName = vbNullString Then
rst.Edit
rst!xref = "00000000.jpg"
rst.Update
Else
'do nothing then go to next record
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Thanks for any help you can offer

Foxhound

 

[dk]

OK, what's the problem? I tried your code and it seemed to work although in your post you said that you wanted to update field 'sref' but the code refers to 'xref'. Maybe that's your problem. Does this code work for you?

Sub UpdateTable()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim FileName As String
    Dim Picture As String

    Const FilePath = "C:\Data\Photos\"
    Set db = CurrentDb()
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM ProductPhotos")
    rst.MoveFirst
    Do While Not rst.EOF
        Picture = rst!xref
        FileName = Dir(FilePath & Picture)
        If FileName = vbNullString Then
            rst.Edit
            rst!xref = "00000000.jpg"
            rst.Update
        Else
            'do nothing then go to next record
        End If
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End Sub