|< Day Day Up >|
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
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:
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.
|< Day Day Up >|