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, join in some recordsets that are based on multiple tables can render the entire recordet 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", dbOpenynaset) 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

Else

MsgBox "Record not found." End If

End With

Deleting Rows

Deleting rows is even simpler; you simply move to the row you want to delete, and then 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 upwards through the recordset (toward the end), deleting rows as you go. For example, if you wanted to delete a contiguous set of rows, you would 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. So, as in Figure 6-4, if you were on row 6 when you delete it, the cursor hasn't changed position, but you will then be on row 7.

Figure 6-4

The recommended procedure for deleting contiguous rows is to move downwards (from the end to the beginning) through the rows, rather than upwards.

rst.MoveLast Do Until rst.BOF rst.Delete

Loop

Cancelling an Edit

If you change your mind and decide not to continue adding, editing, or deleting 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

0 0

Post a comment