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