Deleting Data in a Recordset

Deleting a record is as simple as finding the record and then executing the Recordset object's Delete method. Just be careful, because after you delete the record, it's gone. The one trap you need to be aware of is that the current record remains the current record, even after you delete it. To fully commit the delete action and select a valid record, you must execute a Move, Seek, or Find method right after the Delete method.

The Delete method takes the simple form

 recordset.Delete 

First you select the record you want, and then you call the Delete method. It's that simple. Let's use the following procedure to delete the new employee record for Susan Harkins added in the last section:

 Sub DeleteEmployee(emID As Long)   'Delete employee record   Dim rst As ADODB.Recordset   Dim strSearch As String   Dim strName As String   Dim bytResponse As Byte   strSearch = "EmployeeID = " & emID   Set rst = New ADODB.Recordset   With rst     .Open "Employees", CurrentProject.Connection, adOpenDynamic, _      adLockPessimistic     .Find strSearch     If .EOF = True Then       MsgBox "There is no employee record for " _        & emID, vbOKOnly       Exit Sub     Else       strName = rst.Fields("FirstName") & _        " " & rst.Fields("LastName")       bytResponse = MsgBox("Do you want to delete the record for " _        & strName, vbYesNo)       If bytResponse = vbYes Then        .Delete       End If     End If   End With   rst.Close   Set rst = Nothing End Sub 

Enter the procedure in a standard module or use Chapter 17's example module. Then, run the following statement in the Immediate window:

 DeleteEmployee 15 

where 15 is the EmployeeID value for Susan Harkins. After creating and populating the Recordset object, the Find method locates the record with the EmployeeID value of 15. Next, the message shown in Figure 17.5 asks you to confirm the deletion. If you click Yes, the If statement executes the Delete method; click No and the Delete method is skipped. If there's no match for the employee value passed by emid, the procedure displays an appropriate message; click OK to clear the message box.

Figure 17.5. It's smart to confirm a delete action before executing it against the data source.