How-To: Edit a Value

Value editing consists of changing the value held under a column of an existing record or a control of a form. To do this, the user has to first locate the record, then the cell under a column or the control that is displaying the value. In the same way, if you want to programmatically edit a value, you can first use the Recordset to navigate to the column under which the cell is located. After locating the cell, first call the Edit method of the Recordset object, pass the name of the column to the Fields property of the Recordset object and assign it the new value. Finally, call the Update method of the Recordset object.

Imagine you create a table named People like this:

Imagine you have filled it up with a few records as follows:

Imagine you want to change the last name of a person from Hermine Nollen to Hermine Cranston. You can proceed as follows:

Private Sub cmdEditFullName_Click()
    Dim rstPeople As ADODB.Recordset
    Dim fldFullName As Field
    
    ' Get the Recordset
    Set rstPeople = New ADODB.Recordset
    
    ' Open the Recordset
    rstPeople.Open "People", CurrentProject.Connection, _
		   adOpenStatic, adLockOptimistic
    
    With rstPeople
        ' Check each record
        While Not .EOF
            For Each fldFullName In .Fields
                ' Check the value under the FullName column
                If fldFullName.Name = "FullName" Then
                    ' If you find a person whose FullName value is Hermine Nollen
                    If fldFullName.Value = "Hermine Nollen" Then
                        ' Give it a new value
                        .Fields("FullName") = "Hermine Cranston"
                        .Update
                    End If
                End If
            Next
            .MoveNext
        Wend
    End With
    
    rstPeople.Close
    Set rstPeople = Nothing
End Sub