MS-Access / Getting Started

---

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.

Adding Rows

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.

Editing Rows

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

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