How-To: Edit a Record

Consider the following table:

Imagine that you want to edit or add the name of the director of the video titled Congo. There are a few steps you should follow:

1. Before editing a record, you should first locate it
2. Once you get to the record, before editing the value, call the Edit() method of the Recordset object to indicate that you want to edit the record
3. To actually make the necessary change, you can use the Fields property of the Recordset to identify the column that holds the value. After identifying the column, assign the desired value to it
4. After editing the value, call the Update() method of the Recordset object.

This would be done as follows in the Microsoft Access Object Library:

Private Sub cmdMovePosition_Click()
   Dim dbVideoCollection As Object
   Dim rstVideos As Object

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos")

   rstVideos.Move 3

   rstVideos.Edit
   rstVideos("Director").Value = "Frank Marshall"
   rstVideos.Update
End Sub

Here is the DAO version of this code:

Private Sub cmdMovePosition_Click()
   Dim dbVideoCollection As DAO.Database
   Dim rstVideos As DAO.Recordset

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos1")

   rstVideos.Move 3

   rstVideos.Edit
   rstVideos("Director").Value = "Frank Marshall"
   rstVideos.Update
End Sub

In some cases, jumping to a record to edit it could produce unpredictable results. For example, if one or more records are deleted below record number 4, the video titled Congo would not be at that position anymore and if you use the above code to edit the record, you may modify the wrong record. An alternative is to navigate among records looking for a particular value. You can use record navigation to check the value of each column looking for the one that holds the value you want to change. Once a record matches a criterion you have set, edit it using the same above approach. This would be done as follows:

Private Sub cmdMovePosition_Click()
    Dim dbVideoCollection As Object
    Dim rstVideos As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object

    Set dbVideoCollection = CurrentDb
    Set rstVideos = dbVideoCollection.OpenRecordset("Videos")
    Set fldColumns = rstVideos.Fields

    ' Scan the records from beginning to each
    While Not rstVideos.EOF
        ' Check the current column
        For Each fldEnumerator In rstVideos.Fields
            ' If the column is named Title
            If fldEnumerator.Name = "Title" Then
                ' If the title of the current record is "Congo"
                If fldEnumerator.Value = "Congo" Then
                    ' then change its value
                    rstVideos.Edit
                    rstVideos("Director").Value = "Frank Marshall"
                    rstVideos.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstVideos.MoveNext
    Wend
End Sub