Access VBA to Update Table Record

Question

I am trying to assign code to a button in MS Access that will update a table record. When the button is clicked, I'd like it to reference a user-updated item number in a nearby list box (List26), lookup that matching item number field in a table (Assets), and change a field (Owner) on that record to be blank.

I have been digging around and found some logic around the DAO Recordset but I am not familiar with VBA enough to get it setup correctly or know if this is the right path. Below is what I've gotten to so far:

Private Sub Check_In_Device_Click()
    Dim rec As DAO.Recordset

'Table1 called "Assets"
    Set rec = CurrentDb.OpenRecordset("SELECT * FROM Assets")
    
'if the data in List26 matches an Item# in Asset table...
    If [Item].value = [List26].value Then
    rec.MoveFirst
    rec.Edit
'change Owner field to null
    rec![Owner].value = ""
    rec.Update
    rec.Close
    End If
End Sub

Answer 1

Item is probably numeric, and use Null to blank a field, thus try:

Private Sub Check_In_Device_Click()

    Dim rec As DAO.Recordset

    ' Table1 called "Assets"
    Set rec = CurrentDb.OpenRecordset("SELECT * FROM Assets")

    If rec.RecordCount > 0 Then    
        ' If the data in List26 matches an Item# in Asset table...
        rec.MoveFirst
        rec.FindFirst "Item = '" & Me!List26.Value & "'"
        If Not rec.NoMatch Then
            ' Item found.
            rec.Edit
            ' Change Owner field to null
            rec!Owner.Value = Null
            rec.Update
        End If
    End If
    rec.Close

End Sub

Answer 2

OpenRecordset take a table/query name and a type, you will need to open as dbOpenDynaset to use .findfirst

Make sure your listbox is set up to return the correct value using the Bound Column property (the default will probably be the unique key of the record).

Something like this should do it:

Private Sub Check_In_Device_Click()
    Dim rec As DAO.Recordset
    
    Set rec = CurrentDb.OpenRecordset("Assets", dbOpenDynaset)
    
    With rec
        .FindFirst "[Item] Like '" & List26.Value & "'"
        .Edit
        ![Owner] = ""
        .Update
        .Close
    End With
    
End Sub

If you want to loop through the entire recordset you can use:

    Dim rec As DAO.Recordset
    
    Set rec = CurrentDb.OpenRecordset("Assets", dbOpenDynaset)
    
    With rec
        .MoveFirst
        Do Until .EOF
            If ![Item] Like List26.Value Then
                .Edit
                ![Owner].value = Null
                .Update
            End If
            .MoveNext
        Loop
        .Close
    End With