delete record on continuous form

E

eugzl

Member
Local time
Today, 13:57
Joined
Oct 26, 2021
Messages
127
Hi All.
To delete record on continuous form I created button with code Click event:
Code: 
Private Sub cmdDelDelivery_Click()
   
    If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
       If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
        "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
            DoCmd.RunCommand acCmdSelectRecord
            DoCmd.RunCommand acCmdDeleteRecord
            Form_fInventory!fReceiveSubForm.Requery
       End If
    End If
End Sub
When I click button I'm getting error message:
Rune-time error '2046':
The command or action 'DeleteRecord' isn't available now.
on line DoCmd.RunCommand acCmdDeleteRecord.
The property of continuous form Allow Deletions set Yes.
How to fix the problem?
Thanks
 
Hi. Where exactly did you put the button?
 
theDBguy said:
Hi. Where exactly did you put the button?
Click to expand...
Hi theDBguy. Thanks for reply.
I have fInventoty form with Tab Control. In page Delivery of Tab Control I putted fReceiveSubForm that is continuous form and Delete button.
Thanks
 
Is the button in the Form Header or on each record?
 
eugzl said:
Hi theDBguy. Thanks for reply.
I have fInventoty form with Tab Control. In page Delivery of Tab Control I putted fReceiveSubForm that is continuous form and Delete button.
Thanks
Click to expand...
I was hoping you'd tell us exactly where you placed the button on the form. Can you post a screenshot instead?
 
theDBguy said:
I was hoping you'd tell us exactly where you placed the button on the form. Can you post a screenshot instead?
Click to expand...
That is screenshot of fInventory form
DeleteBtnProblem.png
 
you must Setfocust on the subform first:
Code: 
Private Sub cmdDelDelivery_Click()
  
    If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
       If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
        "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
            With DoCmd
                .GotoControl "fReceiveSubForm"
                .RunCommand acCmdSelectRecord
                .RunCommand acCmdDeleteRecord
            End With
            Form_fInventory!fReceiveSubForm.Requery
       End If
    End If
End Sub
 
arnelgp said:
you must Setfocust on the subform first:
Code: 
Private Sub cmdDelDelivery_Click()
 
    If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
       If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
        "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
            With DoCmd
                .GotoControl "fReceiveSubForm"
                .RunCommand acCmdSelectRecord
                .RunCommand acCmdDeleteRecord
            End With
            Form_fInventory!fReceiveSubForm.Requery
       End If
    End If
End Sub
Click to expand...
Hi arnelgp. Thanks for reply.
It works. Thanks a lot.
 
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
This method scares me. it is too easy to delete the wrong record if you don't know what you are doing. The safest technique is to have the button on the subform itself so there is no question what record will be deleted. The user presses the button on the row of the record he wants to delete. If the subform is in DS view, it can't show buttons so you could do this with just an unbound control that has a control source of ="Delete" You can format it to stand out from the other controls using colors. Then run the delete code in the double-click event of this unbound control.

If you want to leave the button on a different form, then create a hidden unbound field on that form. In the Current event of the subform, copy the PK of the record to the unbound control on the parent form. Then Instead of using the access methods, run a delete query that deletes the record with the ID from the unbound control. You can display good error messages that identify the specific record which will be deleted so the user can confirm that he is deleting record ABC rather than XYZ.
 
Hi All.
I met a new problem during delete record event. When I selected record in continuous SubForm and click Delete button I'm receiving one by one two confirmation to delete a record. The continuous SubForm and Delete button located in a page of Tab Control in the Detail Section.
Code: 
Private Sub cmdDelReceive_Click()
        If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
           If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
            "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
                With DoCmd
                    .GoToControl "fReceiveSubForm"
                    .RunCommand acCmdSelectRecord
                    .RunCommand acCmdDeleteRecord
                End With
                
                Form_fInventory!fReceiveSubForm.Requery
           End If
        End If
End Sub
If in both cases I clicked YES then I'm getting 3rd confirmation from MS Access:
DeleRec3rdConfirmation.png

In case when I clicked YES I don't have any problem and record will be deleted. But if I clicked NO I'm getting error message
DeleRec3rdConfirmationEr.png

How to fix the problem?
Thanks
 
Can you post a sample db with test data?
 
add Error Handler, like:

On Error Resume Next
 
Code: 
Private Sub cmdDelReceive_Click()
    On Error GoTo cmdDelReceive_Click_Error

        If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
           If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
            "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
                With DoCmd
                    .GoToControl "fReceiveSubForm"
                    .RunCommand acCmdSelectRecord
                    .RunCommand acCmdDeleteRecord
                End With
                
                Form_fInventory!fReceiveSubForm.Requery
           End If
        End If
    
  
    Exit Sub

cmdDelReceive_Click_Error:
  
  If Err.Number <> 2501 Then
      MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdDelReceive_Click, line " & Erl & "."
    End If

End Sub
 
MajP said:
Code: 
Private Sub cmdDelReceive_Click()
    On Error GoTo cmdDelReceive_Click_Error

        If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
           If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
            "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
                With DoCmd
                    .GoToControl "fReceiveSubForm"
                    .RunCommand acCmdSelectRecord
                    .RunCommand acCmdDeleteRecord
                End With
               
                Form_fInventory!fReceiveSubForm.Requery
           End If
        End If
   
 
    Exit Sub

cmdDelReceive_Click_Error:
 
  If Err.Number <> 2501 Then
      MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdDelReceive_Click, line " & Erl & "."
    End If

End Sub
Click to expand...
Hi MajP. Thanks for reply.
Now, when I click the DELETE button and then in the first or second confirmation message I click button No, I get a popup like this.
DeleRecConfirmationErr.png

But if I clicked in the first and second confirmation the button YES to delete record, and in the third confirmation I click NO, no popup messages appear.
 
On Error Resume Next
 
arnelgp said:
On Error Resume Next
Click to expand...
I inserted On Error Resume Next in the code
Code: 
Private Sub cmdDelLocation_Click()
    On Error GoTo cmdDelLocation_Click_Error
  
    Dim rsL As Recordset

    Set rsL = Me.fLocationSubForm.Form.RecordsetClone
  
    If rsL.BOF And rsL.EOF Then
        Me.cmdDelLocation.Visible = False
    Else
        Me.cmdDelLocation.Visible = True
        If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
           If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
            "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
                With DoCmd
                    .GoToControl "fLocationSubForm"
                    .RunCommand acCmdSelectRecord
                    .RunCommand acCmdDeleteRecord
                End With
              
                Form_fInventory!fLocationSubForm.Requery
           End If
        End If
    End If
  
    On Error Resume Next
  
cmdDelLocation_Click_Error:
    If Err.Number <> 2501 Then
      MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdDelLocation_Click, line " & Erl & "."
    End If
End Sub
But it didn't help. When I clicked in the first or second confirmation message the button NO, I get a popup message:
Error 0 () in procedure cmdDelLocation_Click, line 0.
How to fix the problem?
Thanks
 
Last edited:
You must log in or register to reply here.

Similar threads

S
Recordset.Requery still showing deleted record
Replies
6
Views
492
dalski
D
J
VBA to print Word file and close is
Replies
8
Views
476
DickyP
DickyP
S
Should my code embrace more of the "Single Responsibility Principle"?
Replies
6
Views
976
jwcolby54
jwcolby54
C
Delete blank record
Replies
3
Views
419
pbaldy
pbaldy
A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
1,196
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom