Solved Double click the record selector in a continuous subform to delete the record (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 14:10
Joined
Sep 17, 2001
Messages
825
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

Well-known member
Local time
Today, 09:10
Joined
Apr 9, 2015
Messages
3,890
why not just press the existing DELETE button to avoid accidents?
 

Sam Summers

Registered User.
Local time
Today, 14:10
Joined
Sep 17, 2001
Messages
825
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've got your good things, and you've got mine.
Local time
Today, 09:10
Joined
May 21, 2018
Messages
5,110
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.
 
Last edited:

Sam Summers

Registered User.
Local time
Today, 14:10
Joined
Sep 17, 2001
Messages
825
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

Super Moderator
Staff member
Local time
Today, 14:10
Joined
Sep 12, 2006
Messages
14,491
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.
 
Last edited:

Sam Summers

Registered User.
Local time
Today, 14:10
Joined
Sep 17, 2001
Messages
825
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.
 

Users who are viewing this thread

Top Bottom