Home > How-To Library > Database

Opens a table-type recordset and then moves through the recordset using the .Edit method to edit a field in the table.

****************************************************************
*       © 2007 CodeItBetter http://www.codeitbetter.com        *
*          This notice MUST stay intact for legal use          *
****************************************************************

Sub EditRecordset()
    Dim dbs As Database, rst As Recordset
    Dim strPath As String

    Set dbs = OpenDatabase(strPath)
    Set rst = dbs.OpenRecordset("Employees", dbOpenTable)

    With rst
        ' Set current index.
        .Index = "LastName"
        ' Locate first record.
        .MoveFirst
        ' Begin loop.
        Do Until .EOF
            If !Title = "Manager" Then
                ' Enable editing.
                .Edit
                ' Change title.
                !Title = "Executive"
                ' Save changes.
                .Update
            End If
            ' Locate next record.
            .MoveNext
            ' End of loop.
        Loop
        ' Close recordset.
        .Close
    End With
    dbs.Close
    Set dbs = Nothing
End Sub

If you would like to submit your code here please us. Do not forget to mention your name. We are always thankful to each and everyone of you who submitted their code here.