Changing an existing record in a Recordset object is a four-step process:
The following example illustrates how to change the job titles for all sales representatives in a table called Employees. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database, rst As Recordset, strCriteria As String
Set dbs = OpenDatabase(strDbPath)
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)
strCriteria = "Title = ""Sales Representative"""
With rst
' Move to start of recordset.
.MoveFirst
' Find first matching record.
.FindFirst strCriteria
Do While Not .NoMatch
' Edit record and update.
.Edit
!Title = "Account Executive"
.Update
' Find next matching record.
.FindNext strCriteria
Loop
End With
If you don’t use the Edit method before you try to change a value in the current record, a run-time error occurs.
Important If you change 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 object, or is a new record. It’s therefore generally simpler to use the CancelUpdate method.
Dynaset-type Recordset objects can be based on a multiple-table query, with the query often implementing a one-to-many relationship. For example, suppose you want to create a multiple-table query that combines fields from the Orders and Order Details tables. Generally speaking, you can’t change values in the Orders table because it’s on the “one” side of the relationship. Depending on your application, however, you may want to be able to make changes to the Orders table. To make it possible to freely change the values on the “one” side of a one-to-many relationship, use the dbInconsistent constant of the OpenRecordset method to create an inconsistent dynaset.
Set rst = dbs.OpenRecordset("Sales Totals",, dbInconsistent)
When you update an inconsistent dynaset, you can easily destroy the relational integrity of the data in the dynaset. You must take care to understand how the data is related across the one-to-many relationship and update the values on both sides in a way that preserves data integrity.
The dbInconsistent constant is available only for dynaset-type Recordset objects. It’s ignored for table-, snapshot-, and forward-only-types, but no compile or run-time error is returned if dbInconsistent is used with those types of Recordset objects.
Even with an inconsistent Recordset object, some fields may not be updatable. For example, you can’t change the value of an AutoNumber field, and a Recordset object based on certain linked tables may not be updatable.
See Also For more information about consistent and inconsistent updates, see Chapter 4, “Queries.”