Recordset changes not being saved (1 Viewer)

[PeterWieland]

I am running some code based on a query to update a field in a table:-

Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim FileExistsbol As Boolean
    Dim stFileName As String

    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryTestMediaFiles")
    
    rst.MoveFirst
    Do While Not rst.EOF
        On Error Resume Next
        stFileName = rst!Folder & "\" & rst!File
        stFileName = Trim(stFileName)
        FileExistsbol = Dir(stFileName) <> vbNullString
        rst.Edit
        If FileExistsbol = False Then
            rst!FileMissing = "yes"
        Else
            rst!FileMissing = "no"
        End If
        rst.Update
         rst.MoveNext
    Loop
    rst.Close

When I run the code, the changes to field FileMissing are not being saved to the table.

What I am trying to acheive is as follows:-

The table contains path and filename of mp3 files. However, the hard drive got corrupted, and I lost a lot of the actual media files (not backed up as I have the original CDs). I am trying to run through the table and flag up all of the tracks that have lost their media file so I can replace them. The Recordset comes from a query as the path and filename are in different tables (path in albums table, file name in tracks table, and not all entries in the database have media files anyway). There are over 10,000 tracks, so manually checking is not an option.

What am I doing wrong?

 

[RuralGuy]

Have you tested the "qryTestMediaFiles" query to see if it is updateable? You have basically disabled any error checking.

 

[PeterWieland]

Thanks RuralGuy,

I checked the query, and yes it is updateable.

I commented out the line that disables error checking, and got a 'data type conversion error'. Changed "yes" and "no" to True and False and it works.

I hadn't realised that On Error Resume Next disabled error checking!

Note to self: make sure you understand what other peoples code is doing before putting it in your app!

 

[RuralGuy]

Outstanding! Thanks for posting back with your success.