Solved Double click the record selector in a continuous subform to delete the record

[Sam Summers]

Hi everyone, i have tried so many ways to do this seemingly simple task but so far no success.

I finally created a button with this code automatically generated which works:

On Error Resume Next
DoCmd.GoToControl Screen.PreviousControl.Name
Err.Clear
If (Not Form.NewRecord) Then
DoCmd.RunCommand acCmdDeleteRecord
End If
If (Form.NewRecord And Not Form.Dirty) Then
Beep
End If
If (Form.NewRecord And Form.Dirty) Then
DoCmd.RunCommand acCmdUndo
End If
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If

-----------------------------------------------------------------------------------------------------

However when i place it all together (as below) on the doubleclick event of the form it does nothing?

On Error Resume Next
Dim Msg, Style, Title, Response, MyString
DoCmd.Beep
Msg = "Are you sure you want to delete this item?"
Style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "Confirm Delete" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
'DoCmd.RunCommand acCmdDeleteRecord
On Error Resume Next
DoCmd.GoToControl Screen.PreviousControl.Name
Err.Clear
If (Not Form.NewRecord) Then
DoCmd.RunCommand acCmdDeleteRecord
End If
If (Form.NewRecord And Not Form.Dirty) Then
Beep
End If
If (Form.NewRecord And Form.Dirty) Then
DoCmd.RunCommand acCmdUndo
End If
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
'Call DoCmd.RunCommand(Command:=acCmdDeleteRecord)
Me.Requery
'Me.Refresh
Else ' User chose No.
End If

 

[Ranman256]

why not just press the existing DELETE button to avoid accidents?

 

[Sam Summers]

why not just press the existing DELETE button to avoid accidents?

Hi Ranman, just trying to make it slicker. I have done this in the past but only in continuous forms not in a subform continuous form. If i have to i will just use the button

 

[MajP]

You should be able to do this no problem. The issue may be the double click event. The form double click event will never happen if there are other controls on the form. You may simply need to change the event. Here is an easy check.
In the procedure put
msgbox "Double Click Fired"

If the message does not show you prove the code is not called. This may only fire if clicked on record selector or maybe a blank area, but many times it will not.

When I do an event like this, I normally have it fire on a double click of any control as well. If you have a lot of controls then build this in a function. Select all controls in design view. On the double click event type
=YourFunctionName()

now all controls will handle the function without a lot of event procedures.

 

[Sam Summers]

You should be able to do this no problem. The issue may be the double click event. The form double click event will never happen if there are other controls on the form. You may simply need to change the event. Here is an easy check.
In the procedure put
msgbox "Double Click Fired"

If the message does not show you prove the code is not called.

I tried that at various parts of the code and it came up at all stages. I do have a button on the same form so the problem must be what you said

 

[gemma-the-husky]

so it may Hi Ranman, just trying to make it slicker. I have done this in the past but only in continuous forms not in a subform continuous form. If i have to i will just use the button

I think @Ranman256 might have meant "press the DEL button on the keyboard", not a programmed button. You can toggle this with the form's "allow deletions" y/n setting.

The only issue with using the del button, is that there is no way of running an "after delete" process to tidy up anything - so if that is important, the best way is to turn allow deletions off, and use your button.

Users can also delete multiple rows with the DEL key. It can be slightly dangerous, as there is no "undelete" option, if they make a mistake.

A word of caution: Note that if you have related records in another table, and you have set RI - relational integrity - then either the delete will fail because of the related records, or the delete will cascade and delete all the related records as well.

 

[Sam Summers]

I think @Ranman256 might have meant "press the DEL button on the keyboard", not a programmed button. You can toggle this with the form's "allow deletions" y/n setting.

The only issue with using the del button, is that there is no way of running an "after delete" process to tidy up anything - so if that is important, the best way is to turn allow deletions off, and use your button.

Users can also delete multiple rows with the DEL key. It can be slightly dangerous, as there is no "undelete" option, if they make a mistake.

A word of caution: Note that if you have related records in another table, and you have set RI - relational integrity - then either the delete will fail because of the related records, or the delete will cascade and delete all the related records as well.

Hi Dave, ok thank you all for your pointers. Think i'll maybe just live with the delete button which works. I was just puzzled as to why the doubleclick code which was identical to the button code wouldn't work but it must be due to the other button on the subform as MajP said.