Access Database Design and Programming, Second Edition

Editing Data Using a Recordset

Let us now discuss the methods used to edit, add, or delete data from a table-type or dynaset-type recordset. Snapshot-type recordsets are static, so data in such a recordset cannot be changed. Thus, in this section, the term recordset will refer to table-type or dynaset-type recordsets. Recall that any changes made to a recordset are reflected in the underlying tables or queries.

Editing an Existing Record

Editing an existing record is done in four steps:

1. Make the record the current record.

2. Invoke the Edit method for the recordset.

3. Make the desired changes to the record.

4. Invoke the Update method for the recordset.

It is important to note that if you move the current record pointer before invoking the Update method, any changes to the record will be lost.

The code in Example 16.6 changes all of the titles in a copy of the BOOKS table to uppercase. Before running this code you should use the Copy and Paste menu options (under the Edit menu) to make a copy of BOOKS, called Books Copy. (Select BOOKS in the Database window, choose Edit → Copy, then choose Edit → Paste.)

Example 16-6. Editing Data With Recordset

                     Sub exaRecordsetEdit()

Dim db As DATABASE
Dim rs As Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("Books Copy")

rs.MoveFirst
Do While Not rs.EOF
    rs.Edit
    rs!Title = UCase$(rs!Title)
    rs.UPDATE
    rs.MoveNext
Loop

rs.Close

End Sub

To emphasize an earlier point, you might want to start over with a fresh Books Copy table and run the previous code without ...