Deleting Records in Form View: stay on record selected prior to deletion

[Nick Cooper]

With all Access databases I have used, when I select to delete a record in Form view, the next record becomes the background for the message asking if I really want to delete a record. That makes it difficult to confirm that I really did select the record I wanted to get rid of. Can I make it stay on the record selected, prior to deletion?

[zmbd]

A few ways to do this depending on the form.
What I use is the form's before delete events, wherein I use a message-box that shows something about the selected record and asks for confirmation. This works well for single record deletions; however, for multiple records there are a few other ways to trap and confirm.

[zmbd]

As you didn't mention which version of Access you are using, I defaulted back to v2003 when looking at my notes.
You'll find the following to be helpful I hope:
Delete Event - Access 2003 VBA Language Reference

[Seth Schrock]

What I would do would be to create a button that deletes the record. In the On_Click event of the button, it would first display a messagebox asking if you want to delete the record with VbYesNo buttons. If yes, then set the warnings to false and then delete the current record and then set the warnings back to true (so that you don't accidentally leave it with the warnings off). This way, the form doesn't know that you are deleting the record until after you have confirmed that you do want to delete the record.

[ADezii]

@Seth Schrock

1. You must Trap any Error that can occur while attempting to DELETE a Record in order that you can Reset SetWarnings.
2. You should make sure that the User cannot DELETE a Record by any other means except by your Command Button.
3. That being said:
   

   Private Sub Command17_Click()
   On Error GoTo Err_DeleteRecord
   Dim strMsg As String
   Dim intResponse As Integer
   Dim intParams As Integer
    
   intParams = vbCritical + vbYesNo + vbDefaultButton1
   strMsg = "DELETE this Record?" & vbCrLf & vbCrLf & _
            "This process will be irreversible!"
    
   intResponse = MsgBox(strMsg, intParams, "Record Deletion Confirmation")
    
   If intResponse = vbYes Then
     With DoCmd
       .SetWarnings False
         .RunCommand acCmdDeleteRecord
       .SetWarnings True
     End With
   End If
    
   Exit_DeleteRecord:
     Exit Sub
    
   Err_DeleteRecord:
     MsgBox Err.Description, vbExclamation, "Error Deleting Record"
       DoCmd.SetWarnings True      'Must RESET
         Resume Exit_DeleteRecord
   End Sub

@Seth:
My apologies, Seth. Somehow I thought that you were actually asking the question and not the OP. Well, I'll leave the Code intact anyway. Sorry.

[zmbd]

Just as a point of information: The OnDelete and BeforeDelConfirm events trip before any default messages are fired.

By personal preference, I also tend to leave the default program message... that way the user not only has to go thru my prompt but a second prompt as well... makes denying the records, and deleting the same, by accident less likely and the user more liable for the event!

[Seth Schrock]

@ADezii Good point about error trapping. I have just started getting into the habit of using error trapping, but it isn't yet engraved in my mind. Otherwise, that was exactly what I had in mind.

@Z More of a personal preference, but I like removing all the default error messages from Access. However, that is definitely something to consider of putting more responsibility on the user for an accidental deletion. One database that I did I really wanted to make sure that records from a certain table weren't deleted by accident so I had a checkbox that enabled the deleted button. Selecting the checkbox prompted a message asking if you were sure you wanted to enable it. If you said yes, then the delete button was enabled. Then clicking delete prompted another message asking if you wanted to delete the record. Possibly a bit overkill, but deleting records is rare and I was having fun that day thinking about all the things that I could do :)

[NeoPa]

That'll teach me to leave a question and come back to it later :-( Everyone's answered it already.

I would suggest that you design your forms in such a way that deleting records cannot occur unless via the interface you provide. That means adding a Delete button to the form (or any other interface trigger you choose) and disabling the Access provided Remove Record.

Others here have already covered how to disable other, automatic, messages (including DoCmd.SetWarnings), so only what you want to see should ever be shown. Personally, I would not recommend showing a message for the same thing twice, as users get more used to ignoring messages that way, and then things get done that shouldn't be. However, I'd happily recommend Seth's idea of requiring the operator to enable the delete triggering button if you want extra care to be shown. Ingeniously, it does that same job without the user realising it.

[Nick Cooper]

Time differences meant I didn't get to see the later messages until I'd incorporated Seth's initial solution which was simple to implement and is just the job, although the ensuing advice about the general principles on warnings, over-use etc is taken on board. There is no other means of deleting a record on this database other than this button so I think it's watertight.
Thanks to all for the good advice.

[TheSmileyCoder]

Have you tried in your application, while the form is active to hit "Ctrl-A" and then hit Del? Or "Shift-Spacebar" and then hit delete?


Now my usual approach is this:
Make sure that AllowDeletions=False for the form. Then use code behind a delete button like so:

Private Sub btn_Delete_Click()
On Error GoTo ErrHandler:
   If vbYes = MsgBox("Do you wish to delete active record?", vbYesNo + vbExclamation) Then
      Me.AllowDeletions = True
      DoCmd.SetWarnings False
      DoCmd.RunCommand acCmdDeleteRecord
   End If
 
ExitSub:
   DoCmd.SetWarnings True
   Me.AllowDeletions = False
   Exit Sub
 
ErrHandler:
   MsgBox Err.Number & " - " & Err.Description
   Resume ExitSub
End Sub

This basically turns on deletions for when its needed and then removes the ability straight after.

[NeoPa]

@Nick. Good to see such a gracious and intelligent response. I have no doubt you've progressed from the answers submitted here and I'm pleased for you :-)

@Smiley. There are other approaches to this which I was going to go into the details of, but I rather think I prefer your approach. It avoids the need for handling the event fired for deletions completely. Nice approach.