Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

512

When working with datasheets and recordsets only one record is active. In a datasheet the active

record is indicated by a color difference in the row. Recordsets have no such visual aid, so you

must always be aware of which record is current in a recordset.

Fortunately, both ADO and DAO provide a number of ways to keep track of records in a recordset,

and different techniques for moving around within a recordset. It’s also quite easy to learn the field

names in a recordset and to modify the data within each field.

This chapter, and many of the chapters that follow, demonstrate many of the data management tech-

niques available through the VBA language. As an Access developer, you’ll almost certainly learn new

and more effective ways to work with data every time you work on an Access application.

Before you change data in any of the recordset’s fields, however, you need to make sure that you’re

in the record you want to edit. When a recordset opens, the current record is the first record in the

set. If the recordset contains no records, the recordset’s EOF property is True.

Caution
A runtime error occurs if you try to manipulate data in a recordset that contains no records. Be sure to check
the value of the EOF property immediately after opening a recordset:

Set rs = new ADODB.Recordset

rs.Open “tblCustomers”.... etc.

If rs.EOF <> True Then

‘Okay to process records

End If

Errors will occur if the code moves past either EOF (MoveNext) or BOF (MovePrevious). Your code should
always check the EOF and BOF property after executing a move method.

To update a field in the current record of the recordset, in an ADO recordset, you simply assign a

new value to the field. When using DAO, you must execute the Recordset object’s Edit

method before assigning a new value. In the Form_AfterUpdate procedure in Figure 13.15,

you assign the value of txtSaleDate on the frmSales form to the recordset’s LastSaleDate

field.

After you change the record, use the recordset’s Update method to commit the record to the data-

base. The Update method copies the data from the memory buffer to the recordset, overwriting

the original record. The entire record is replaced, not just the updated field(s). Other records in the

recordset, of course, are not affected by the update.

Changes to an ADO recordset are automatically saved when you move to another record or close

the recordset. In addition, the edited record is also saved if you close a recordset or end the proce-

dure that declares the recordset or the parent database. However, you should use the Update

method for better code readability and maintainability.

Use the record’s CancelUpdate method to cancel pending changes to an ADO recordset. If it’s

important to undo changes to a record, you must issue the CancelUpdate method before mov-

ing to another record in an ADO recordset because moving off of a record commits the change and

an undo is no longer available.