Modify an existing record in a DAO Recordset

You can modify existing records in a table-type or dynaset-type Recordset object by using the Edit and Update methods.

To modify an existing record in a table-type or dynaset-type Recordset object:

1. Go to the record that you want to change.

2. Use the Edit method to prepare the current record for editing.

3. Make the necessary changes to the record.

4. Use the Update method to save the changes to the current record.

The following code example shows how to change the job titles for all sales representatives in a table called Employees.

Dim dbsNorthwind As DAO.Database 
Dim rstEmployees As DAO.Recordset 
 
   Set dbsNorthwind = CurrentDb 
   Set rstEmployees = dbsNorthwind.OpenRecordset("Employees") 
 
   rstEmployees.MoveFirst 
   Do Until rstEmployees.EOF 
      If rstEmployees!Title = "Sales Representative" Then 
         rstEmployees.Edit 
         rstEmployees!Title = "Account Executive" 
         rstEmployees.Update 
      End If 
      rstEmployees.MoveNext 
   Loop 

Note

If you don't use the Edit method before you try to change a value in the current record, a run-time error occurs. If you edit the current record and then move to another record or close the Recordset object without first using the Update method, your changes are lost without warning. For example, omitting the Update method from the preceding example results in no changes being made to the Employees table.

You can also terminate the Edit method and any pending transactions without saving changes by using the CancelUpdate method. While you can terminate the Edit method just by moving off the current record, this is not practical when the current record is the first or last record in the Recordset, or when it is a new record. It's generally simpler to use the CancelUpdate method.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.