Edit Method Example (MDB)

The following example opens a Recordset object and locates each record satisfying the search criteria for the Title field. The procedure then uses the Edit method to prepare the record for subsequent editing, changes the job title, and saves the change.

Sub ChangeTitle()
    Dim dbs As Database, rst As Recordset
    Dim strCriteria As String, strNewTitle As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Set search criteria.
    strCriteria = "Title = 'Sales Representative'"
    strNewTitle = "Account Executive"
    ' Create dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
    ' Find first occurrence.
    rst.FindFirst strCriteria
    ' Loop until no matching records.
    Do Until rst.NoMatch
        With rst
            .Edit                            ' Enable editing.
            !Title = strNewTitle    ' Change title.
            .Update                        ' Save changes.
            .FindNext strCriteria    ' Find next occurrence.
        End With
    Loop
    rst.Close
    Set dbs = Nothing
End Sub

Tip   Using an update query to alter data is more efficient. For example, you can use the following code to achieve the same results:

Sub ChangeTitleSQL()
    Dim dbs As Database
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create SQL string.
    strSQL = "UPDATE Employees SET Title = 'Account Executive' " _
        & "WHERE Title = 'Sales Representative' "
    ' Run action query.
    dbs.Execute strSQL
    ' Return number of records updated.
    Debug.Print dbs.RecordsAffected
    Set dbs = Nothing
End Sub