Deleting an Existing Record

You can delete an existing record in a table- or dynaset-type Recordset object by using the Delete method. You can’t delete records from a snapshot- or forward-only-type Recordset object. The following example deletes all orders prior to 10-1-94 from the Orders table in the NorthwindTables database. In this example, strDbPath is the path to the database:

Dim dbs As Database, rst As Recordset
Dim strCriteria As String

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
strCriteria = "OrderDate < #10-1-94#"
With rst
	.FindFirst strCriteria
	Do While Not .NoMatch
		.Delete
		.FindNext strCriteria
	Loop
	.Close
End With

When you use the Delete method, Microsoft Jet immediately deletes the current record without any warning or prompting. Deleting a record doesn’t automatically cause the next record to become the current record; to move to the next record, you must use the MoveNext method. Keep in mind, however, that after you’ve moved off the deleted record, you cannot move back to it.

If you try to access a record after deleting it from a table-type Recordset object, you’ll get error 3167, “Record is deleted.” On a dynaset you’ll get error 3021, “No current record.”

If you have a Recordset object clone positioned at the deleted record and try to read its value, you’ll get error 3167 regardless of the type of Recordset object. Trying to use a bookmark to move to a deleted record will also result in error 3167.