Edit Method

Applies To

Dynaset-Type Recordset Object, Recordset Object, Table-Type Recordset Object.

Description

Copies the current record from a dynaset-type or table-type Recordset object to the copy buffer for subsequent editing.

Syntax

recordset.Edit

The recordset placeholder represents the name of an open, updatable Recordset object that contains the record you want to edit.

Remarks

Once you use the Edit method, changes made to the current record's fields are copied to the copy buffer. After you make the desired changes to the record, use the Update method to save your changes.

Caution

If you edit a record and then perform any operation that moves to another record without first using Update, your changes are lost without warning. In addition, if you close recordset or end the procedure which declares the recordset or the parent Database object, your edited record is discarded without warning.

When the Recordset object's LockEdits property setting is True (pessimistically locked) in a multiuser environment, the record remains locked from the time Edit is used until the updating is complete. If the LockEdits property setting is False (optimistically locked), the record is locked and compared with the pre-edited record just before it's updated in the database. If the record has changed since you used the Edit method, the Update operation fails with a trappable error (3197).

Note

Optimistic locking is always used on external database formats, such as ODBC and installable ISAM.

The current record remains current after you use Edit.

To use Edit, there must be a current record. If there is no current record or if recordset doesn't refer to an open table-type or dynaset-type Recordset object, an error occurs.

Using Edit produces an error under the following conditions:

There is no current record.
The database or recordset is read-only.
No fields in the record are updatable.
The database or recordset was opened for exclusive use by another user.
Another user has locked the page containing your record.

See Also

AddNew Method, Delete Method, LockEdits Property.

Example

This example finds the first record in a Recordset object that matches the search criteria and opens it for editing. Then it changes the value in the Title field and saves the change using the Update method.

Dim strCriteria As String, strNewTitle As String
strCriteria = "Title = 'My Right Hand'"    ' Create the criteria.
strNewTitle = "My Right Foot"    ' Create a new title.
rstTitles.FindFirst strCriteria    ' Make record current.
Do While Not rstTitles.NoMatch
    rstTitles.Edit    ' Open record.
    rstTitles.Fields("Title") = strNewTitle    ' Enter new title.
    rstTitles.Update    ' Save changes.
    rstTitles.FindNext strCriteria
Loop

The following example achieves the same effect without using the Edit method.

' Create the criteria string.
strSQLQuery = "UPDATE Titles SET Title = 'My Right Foot'" & _
    " WHERE Title = 'My Right Hand';"
dbsPublishers.Execute strSQLQuery    ' Execute the query.

This example opens a Recordset object and locates each record whose Title field satisfies the search criteria and copies it to the copy buffer. The example then prepares the record for subsequent editing, changes the job title, and saves the change using the Update method.

Dim dbsNorthwind As Database, rstEmployees As Recordset 
Dim strCriteria As String, strNewTitle As String
' Set search criteria.
strCriteria = "Title = 'Sales Representative'"
strNewTitle = "Account Executive"    ' Set new job title.
Set dbsNorthwind = DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
' Create dynaset.
Set rstEmployees = dbsNorthwind.OpenRecordset("Employees", _ 
    dbOpenDynaset)
rstEmployees.FindFirst strCriteria    ' Find first occurrence.
' Loop until no matching records.
Do Until rstEmployees.NoMatch
    With rstEmployees
       .Edit    ' Enable editing.
       !Title = strNewTitle    ' Change title.
       .Update    ' Save changes.
       .FindNext strCriteria    ' Find next occurrence.
    End With
Loop    ' End of loop.

Tip

Using an update query to change job titles might be more efficient. For example, you could use the following code to achieve the same results:

strSQL = "Update Employees Set Title = 'Account Executive' " & _
    "WHERE Title = 'Sales Representative' "
dbsNorthwind.Execute strSQL

Example (Microsoft Access)

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 Database variable pointing 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
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.

strSQL = "Update Employees Set Title = 'Account Executive' " & _
    "WHERE Title = 'Sales Representative' "
dbs.Execute strSQL