Error handling for delete record command

[jobrien4]

It's been 8 years since I've taken a Visual Basic class and I'm a bit rusty. Just starting to use VBA for a db I'm designing.



I have a multiple items form with a tabular layout. When this form is loaded, it filters in only at a few select records.

At the bottom, I added a "Delete Button" which allows the user delete one of the above current records with:

DoCmd.SetWarnings False 'Removes warning box on deletion
DoCmd.RunCommand acCmdDeleteRecord 'Deletes current record
DoCmd.SetWarnings True 'Reinstates warnings before deleting

However, if there is no record to be deleted (either they have already all been deleted or there is no records to begin with) and the user hits the Delete Button, the program flips out. How can I have it catch that, so if there are no records to be deleted a MsgBox says "There are no records to be deleted" and the delete does not run?

[rpeare]

Are you getting this error only on a record that is blank? i.e. you start to enter a record, then hit your ESC key then hitting your delete button and it bombs out? or are you entering a new record and trying to delete it before it's been 'committed' to the database?

There are a few things you can do to mitigate this. Not knowing how you are generating the error you can do an error trap

ON ERROR GOTO ERRHANDLER


then at the bottom of your VB event script have

Exit sub
ERRHANDLER:
debug.print err.number & " " & err.description
end sub


This will give you the error number that's being generated then you can change your error handler 'skip' that error when it occurs by doing something like

Exit sub
ERRHANDER:
if err.number = <returned error number> then
resume next
else
msgbox err.number & " " & err.description
endif
End Sub


Just note that it's not a great idea to do this where you basically allow an error to happen but it's probably the most generic easiest thing to do.

[pdebaets]

Here's some code I use for deleting records from a form. Hope it helps:

    
    Dim strSaveBookmark  as String
    If Me.NewRecord Then
        '* We're on a new record so can't delete
        Beep
    Else
        If MsgBox("Are you sure you want to delete the current record?", vbYesNo, "Delete Confirmation") = vbYes Then
            With Me.RecordsetClone
                .Bookmark = Me.Recordset.Bookmark
                .Move -1
                If .BOF Then
                    strSaveBookmark = ""
                Else
                    strSaveBookmark = .Bookmark
                End If
            End With
            Me.Recordset.Delete
            Me.Requery
            If Trim("" & strSaveBookmark) = "" Then
            Else
                Me.Recordset.Bookmark = strSaveBookmark
            End If
        Else
            'MsgBox "Action cancelled"
        End If
    End If