delete record on continuous form (1 Viewer)

[eugzl]

Hi All.
To delete record on continuous form I created button with code Click event:

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

 

[theDBguy]

Hi. Where exactly did you put the button?

 

[eugzl]

Hi. Where exactly did you put the button?

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

 

[GinaWhipp]

Is the button in the Form Header or on each record?

 

[theDBguy]

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

I was hoping you'd tell us exactly where you placed the button on the form. Can you post a screenshot instead?

 

[eugzl]

Is the button in the Form Header or on each record?

The subform and button locating in the Form Detail.
Thanks

 

[eugzl]

I was hoping you'd tell us exactly where you placed the button on the form. Can you post a screenshot instead?

That is screenshot of fInventory form

 

[arnelgp]

you must Setfocust on the subform first:

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

 

[eugzl]

you must Setfocust on the subform first:

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

Hi arnelgp. Thanks for reply.
It works. Thanks a lot.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

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.

 

[eugzl]

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.

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:

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

How to fix the problem?
Thanks

 

[theDBguy]

Can you post a sample db with test data?

 

[eugzl]

Can you post a sample db with test data?

Hi theDBguy. Thanks for reply.
The attached file is last version of DB related to locations. When you unziped file run fInventory form.
Thanks

 

[arnelgp]

add Error Handler, like:

On Error Resume Next

 

[eugzl]

add Error Handler, like:

On Error Resume Next

Could you show in a code what need to do? Thanks

 

[MajP]

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

 

[eugzl]

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

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.

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.

 

[arnelgp]

On Error Resume Next

 

[eugzl]

On Error Resume Next

I inserted On Error Resume Next in the 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