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:
Make the record the current record.
Invoke the Edit method for the recordset.
Make the desired changes to the record.
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.CloseEnd Sub
To emphasize an earlier point, you might want to start over with a fresh Books Copy table and run the previous code without ...
Get Access Database Design and Programming, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.