Working with Recordsets
So far you've looked at navigating through recordsets, setting and using bookmarks, creating recordset clones, and finding specific records. All this has been done so that you can get to the exact record that you intend to do something with.
So what can you do with recordsets? The following sections answer that question.
Retrieving Field Values
On an open recordset, you return a field value by simply referring to it. There are, of course, several ways to do this.
The first method is to refer to the field by name, as in the following code.
Set rst = dbs.OpenRecordset("tblMyTable") MsgBox rst!CustomerNo 'or MsgBox rst("CustomerNo")
Don't forget that the field name you use depends entirely on the table or query on which the recordset is based. For example, if the customer number is contained in the CustomerNo field, and the recordset gets its data directly from tblCustomers, then rs!CustomerNo would suffice. However, if the recordset gets its data from a query in which the CustomerNo field is renamed (using the As keyword) to CustNo:
SELECT CustomerID, CustomerNo As CustNo, CustName FROM tblCustomers
then you would use rs!CustNo.
You can also refer to a field by the recordset's Field object, as in the following example:
MsgBox rst.Fields!CustomerNo MsgBox rst.Fields("CustomerNo") MsgBox rst.Fields(2)
Adding, Editing, and Deleting Rows
Not all recordsets are editable, and the same can be said about some rows. Snapshot recordsets are never editable, and user permissions and record locks can result in recordsets or individual rows that you cannot edit. In addition, joins in some recordsets that are based on multiple tables can render the entire recordset uneditable.
The procedure for adding rows to a recordset is quite simple: Open the recordset, issue the recordset's AddNew method, make the additions, and then issue the Update method. Here's an example:
'Open the recordset Set rst = dbs.OpenRecordset("tblCustomers", dbOpenynaset) With rst 'Begin the editing session .AddNew 'Make the additions !CustName = "Fred Nurk" !DOB = DateSerial(1956, 11, 5) !LastVisited = Date() ' 'Make other additions if you wish ' 'Commit the changes .Update End With
If using an Autonumber field, there is no need to specify it as Access will automatically calculate and enter it for you. In fact, if you try to specify a value for an Autonumber field, Access will give an error.
The procedure for editing recordset data is quite simple: Move to the row you want to edit, issue the recordset's Edit method, make the changes, and then issue the Update method. The following example demonstrates how:
'Open the recordset Set rst = dbs.OpenRecordset("tblCustomers", dbOpenDynaset) With rst 'Find the record you want to edit .FindFirst "[CustomerNo] = 123" If Not .NoMatch Then 'Begin the editing session .Edit 'Make the change(s) !LastVisited = Date() ' 'Make other changes if you wish ' 'Commit the changes .Update Else MsgBox "Record not found." End If End With
Deleting rows is even simpler; you just move to the row you want to delete and issue the Delete method.
'Open the recordset Set rst = dbs.OpenRecordset("tblCustomers", dbOpenynaset) With rst 'Find the record you want to edit .FindFirst "[CustomerNo] = 123" If Not .NoMatch Then 'Delete the row .Delete Else MsgBox "Record not found." End If End With
An important point to note when deleting rows is that as soon as you delete one, all the rows above it shift down one position. This is of real consequence only if you are moving up through the recordset (toward the end), deleting rows as you go. For example, if you wanted to delete a contiguous set of rows, you could end up deleting every second row. This is because when you delete the current row, the cursor does not move, but the rows above it move down one position to compensate.
The recommended procedure for deleting contiguous rows is to move down (from the end to the beginning) through the rows, rather than up.
rst.MoveLast Do Until rst.BOF rst.Delete rst.MovePrevious Loop
Canceling an Edit
If you change your mind and decide not to continue adding or editing records, you can cancel the update using the CancelUpdate method. You can only the cancel changes between the AddNew...Update or Edit...Update methods. For example:
With rst .AddNew !OrgName = strOrgName !Address = strAddress 'If some criteria is met, update the record If IsFinancial(lngOrgID) Then .Refund = curRefundAmt .Update Else 'If the criteria test fails, cancel the update .CancelUpdate End If End With
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields