Search code examples
vbaformsms-accessfilterunbound

Access sub to update a single record always updating the first record in a table

I have an unbound form displaying fields from a table in my d/b. The user may select images and store them in a working folder by clicking the "Browse..." button. The button should also update a field in the record belonging to the selected image with the word "Picked". The images are being successfully stored, no problem with that. The Sub which performs the update only ever updates the first record.

The sub is called for each individual image, passing the image name as a parameter. I've used a Select query which returns all the records in that table, then I've attempted to apply a filter to the data. It appears to filter to a single record. The first pass updates the correct record. All subsequent passes continue to update the first record, despite being passed an new parameter value. It's probably a very simple oversight but I just can't see it.

Here is the unbound form:

enter image description here

Here is the VBA for the update of a single record:

Sub UpdateRecordInTable(strMyKey As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Debug.Print "Entering sub"
    ' Set the database reference
    Set db = CurrentDB
   
    Set rs = db.OpenRecordset("SELECT * FROM tempAllSrchImg_Result", dbOpenDynaset)
    
    rs.Filter = "ImgFileA = " & Chr(34) & strMyKey & Chr(34)
    
    ' Check if a record matches the filter
    If Not rs.EOF Then
        ' Move to the first matching record
        rs.MoveFirst
        Debug.Print "ImgFileA to be updated as per filter:", strMyKey
        Debug.Print "recordset's ImgFileA value:", rs("ImgFileA").Value
        ' Update the Picked field
        rs.Edit
        rs("Picked").Value = "Picked"  ' Set the new value
        rs.Update
        Debug.Print "Record updated successfully."
    Else
        Debug.Print "No matching record found."
    End If

    ' Clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Debug.Print "Exiting sub"
End Sub

I am expecting (and got) three executions of this sub. It appears to be passing the correct parameter to use in the filter. But it appears to be only ever updating one record. Here are the results of the Immediate Window:

Entering sub
ImgFileA to be updated as per filter:     Img000001.A.tiff
recordset's ImgFileA value: Img000001.A.tiff
Record updated successfully.
Exiting sub
Entering sub
ImgFileA to be updated as per filter:     Img000002.A.tiff
recordset's ImgFileA value: Img000001.A.tiff
Record updated successfully.
Exiting sub
Entering sub
ImgFileA to be updated as per filter:     Img000007.A.tiff
recordset's ImgFileA value: Img000001.A.tiff
Record updated successfully.
Exiting sub

I have also tried replacing the rs.Filter with either a direct SELECT query and then and Edit, or a direct UPDATE query on the table but both of the return Error Code 91 "Object variable or With block not set", even though creating a SQL query in the database using the query string selects or updates the correct record.

Thanks for your help with this.

Solution

  • You are misinterpreting how Recordset.Filter works - it applies to a subsequently opened Recordset.
    Take a close look at the example in the docs :

        'Now filter the Recordset to return only the customers from that city
    rst.Filter = "City = '" & strCity & "'"
    Set rstFiltered = rst.OpenRecordset

    So if you want or need to go the recordset route, filter it directly when opening the rs:

    strSql = "SELECT * FROM tempAllSrchImg_Result WHERE ImgFileA = " & Chr(34) & strMyKey & Chr(34)
Set rs = db.OpenRecordset(strSql, dbOpenDynaset)

' Check if a record matches the filter
If Not rs.EOF Then
    ' Move to the first matching record <- this is not needed!

    ' Update the Picked field
    rs.Edit
    rs("Picked").Value = "Picked"  ' Set the new value
    rs.Update
    Debug.Print "Record updated successfully."
Else
    Debug.Print "No matching record found."
End If

    But normally you would simply do an UPDATE query.

    strSql = "UPDATE tempAllSrchImg_Result SET Picked = 'Picked' " & _
         "WHERE ImgFileA = " & Chr(34) & strMyKey & Chr(34)
db.Execute strSql

    I should note: When concatenating variables with SQL, it is best to use Gustav's CSql() function. It handles string and other variables (e.g. dates), simplifies your code, and removes the possibility of SQL injection or simply errors due to problematic input.

    strSql = "UPDATE tempAllSrchImg_Result SET Picked = 'Picked' " & _
         "WHERE ImgFileA = " & CSql(strMyKey)