Deleting Records

Right, now come two Functions to add your deletion of records:

The DeleteRecord Function accepts two parameters this time, the table from which you wish to delete a record and the number of the record that you wish to delete. The function returns the number of the record that you wanted to delete if it was successful or -1 if the deletion failed.

Here's the code:
Public Function DeleteRecord(rstTable As Recordset, intID As Integer) As Integer
   With rstTable
      .Requery
      .FindFirst "ID = " & intID
      If .NoMatch = True Then
         DeleteRecord = -1
      Else
         .Delete
         DeleteRecord = intID
      End If
   End With
End Function

This code is quite straightforward, it requeries the database as we discussed before. Then it uses the FindFirst Method to locate the record that we want to delete. If the record doesn't exisit then the NoMatch property is flagged as true by Access. We can check this to see if we can delete the record or not.

The function's return value is set and the record is deleted using the Delete method, if appropriate.

So to delete record 4 from rstPeople do the following:
Dim intReturn as Integer
intReturn = DeleteRecord(rstPeople, 4)

You could then check the value of intReturn to make sure it was 4. If it wasn't then you'd know the deletion had failed.

The other function which may be of use is the DeleteAllRecords function. It does what it says on the tin and accepts one parameter - the recordset of the table that you wish to delete. It returns nothing so it's not a function... I was just testing you! Here's the code:

Public Sub DeleteAllRecords(rstTable As Recordset)
   With rstTable
      .Requery

      If .BOF = True And .EOF = True Then Exit Sub

      .MoveFirst
      Do While .EOF = False
         .Delete
         .MoveNext
      Loop
   End With
End Sub

The sub checks to see if there are any records, and if not it exits the sub. Otherwise, it cycles through each record and deletes them one by one.

So, to delete all of the records from rstPeople you could use:
DeleteAllRecords rstPeople