Edit Method

Applies To

Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only–Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Copies the current record from an updatable Recordset object to the copy buffer for subsequent editing.

Syntax

recordset.Edit

The recordset placeholder represents 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.

The current record remains current after you use Edit.

Caution If you edit a record and then perform any operation that moves to another record, but 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 or Connection object, your edited record is discarded without warning.

Using Edit produces an error if:

There is no current record.
The Connection, Database, or Recordset object was opened as read-only.
No fields in the record are updatable.
The Database or Recordset was opened for exclusive use by another user (Microsoft Jet workspace).
Another user has locked the page containing your record (Microsoft Jet workspace).

In a Microsoft Jet workspace, 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 update 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 run-time error if you use OpenRecordset without specifying dbSeeChanges. By default, Microsoft Jet-connected ODBC and installable ISAM databases always use optimistic locking.

In an ODBCDirect workspace, once you edit (and use Update to update) a record's primary key field, you can no longer edit fields in that record until you close the Recordset, and then retrieve the record again in a subsequent query.

Note To add, edit, or delete a record, there must be a unique index on the record in the underlying data source. If not, a "Permission denied" error will occur on the AddNew, Delete, or Edit method call in a Microsoft Jet workspace, or an "Invalid argument" error will occur on the Update call in an ODBCDirect workspace.

See Also

AddNew method, Delete method, LockEdits property.

Example

This example uses the Edit method to replace the current data with the specified name. The EditName procedure is required for this procedure to run.

Sub EditX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim strOldFirst As String
    Dim strOldLast As String
    Dim strFirstName As String
    Dim strLastName As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset)

    ' Store original data.
    strOldFirst = rstEmployees!FirstName
    strOldLast = rstEmployees!LastName

    ' Get new data for record.
    strFirstName = Trim(InputBox("Enter first name:"))
    strLastName = Trim(InputBox("Enter last name:"))

    ' Proceed if the user entered something for both fields.
    If strFirstName <> "" and strLastName <> "" Then
        ' Update record with new data.
        EditName rstEmployees, strFirstName, strLastName

        With rstEmployees
            ' Show old and new data.
            Debug.Print "Old data: " & strOldFirst & " " & strOldLast
            Debug.Print "New data: " & !FirstName & " " & !LastName
            ' Restore original data because this is a
            ' demonstration.
            .Edit
            !FirstName = strOldFirst
            !LastName = strOldLast
            .Update
        End With

    Else
        Debug.Print         "You must input a string for first and last name!"
    End If

    rstEmployees.Close
    dbsNorthwind.Close

End Sub

Sub EditName(rstTemp As Recordset, _
    strFirst As String, strLast As String)

    ' Make changes to record and set the bookmark to keep
    ' the same record current.
    With rstTemp
        .Edit
        !FirstName = strFirst
        !LastName = strLast
        .Update
        .Bookmark = .LastModified
    End With

End Sub

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 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