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". With rst |
, dbOpenynaset) | |
|
'Begin the editing session |
'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 |
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.
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. So, as Figure 6-8 shows, if you were on row 6 when you deleted it, the cursor hasn't changed position, but you will then be on row 7. Recordset rows Remaining rows moved down Deleted record <- Figure 6-8 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:
|
Post a comment