Delete Record (1 Viewer)

[Lgvalencia]

I am having the issue of my delete button saying the command or action DeleteRecord isn't available.

See attached file

Thank you
Lesley

 

[bastanu]

Try to replace Me.Refresh with Me.Dirty=False to force a save of the record.

Cheers,

 

[Solo712]

I am having the issue of my delete button saying the command or action DeleteRecord isn't available.

See attached file

Thank you
Lesley

Me.Refresh will not help. Use Me.Requery after the Delete command.

Jiri

 

[LarryE]

Open to the recordsource and look to see that records can be added or deleted.

 

[Lgvalencia]

I am still getting the isn't available message

 

[Lgvalencia]

Private Sub BDELETE_Click()
On Error GoTo Err_BDELETE_Click

Dim Response As String
Dim Cancel As Integer

Response = acDataErrContinue
If MsgBox("Delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox(" Are you SURE you want to delte this Record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
Me.Requery

If Me.NewRecord Then Exit Sub
DoCmd.RunCommand acCmdDeleteRecord

End If
End If

Exit_BDELETE_Click:
Exit Sub

Err_BDELETE_Click:
MsgBox Error$
Resume Exit_BDELETE_Click

End sub

 

[bastanu]

If you requery before the Delete happens you will always delete whatever is the first record, not the current one you actually want to delete.... So maybe it is better that it didn't work for you yet.
In post #4 Larry asked you if you can delete directly from the form's record source? Is it a query or a table? Have you tried to add Me.Dirty =False as I suggested (instead of the current Me.Requery)?

Cheers,

 

[Lgvalencia]

Yes, this function use to work and its stopped working.

I tried, Me.Dirty=False and got same error message.

 

[Pat Hartman]

The code you posted would never have worked. Do you still have the original code?

 

[Lgvalencia]

This is the original code.

Private Sub BDELETE_Click()
On Error GoTo Err_BDELETE_Click

Dim Response As String
Dim Cancel As Integer

Response = acDataErrContinue

If MsgBox("Delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
    If MsgBox(" Are you SURE you want to delte this Record?" & vbCrLf & _
        "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
        If Me.NewRecord Then Exit Sub
        DoCmd.RunCommand acCmdDeleteRecord
    End If
End If
    
Exit_BDELETE_Click:
    Exit Sub

Err_BDELETE_Click:

    MsgBox Error$
    Resume Exit_BDELETE_Click
End Sub

 

[Pat Hartman]

Are you sure this code ever worked? I reformatted it because it was impossible to read as it was. It still has formatting issues. For example, concatenating actions so that a single line of code executes multiple actions is really poor practice because it is hard for humans to work that out as they as scanning the code. The second If Msgbox() also has what looks like a formatting error.

Try this:

Private Sub BDELETE_Click()
On Error GoTo Err_BDELETE_Click

Dim Response As String
Dim Cancel As Integer
Dim strMsg As String

If Me.NewRecord Then
    Exit Sub
End If
Response = acDataErrContinue

If MsgBox("Delete this record?", vbYesNo, "Delete Confirmation",vbYesNo) = vbYes Then
    strMsg = " Are you SURE you want to delete this Record?" & vbCrLf
    strMsg = strMsg & "This will permanently delete the record." & vbCrLf
    strMsg = strMsg & "2nd Delete Confirmation"
    If MsgBox( strMsg, vbYesNo ) = vbYes Then
        DoCmd.RunCommand acCmdDeleteRecord
    End If
End If
   
Exit_BDELETE_Click:
    Exit Sub

Err_BDELETE_Click:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & " -- " & Err.Description
    End Select
    Resume Exit_BDELETE_Click
End Sub

 

[arnelgp]

here is you code to test
btw, Response is Integer not string.
also Response and Cancel has nothing to do with your code, so i comment it.

Private Sub BDELETE_Click()
On Error GoTo Err_BDELETE_Click

'Dim Response As String
'Dim Cancel As Integer

'Response = acDataErrContinue

If MsgBox("Delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
    If MsgBox(" Are you SURE you want to delte this Record?" & vbCrLf & _
    "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
        'arnelgp
        'don't Requery it will get new record therefore
        'you will loose Focus of the curren record
        'Me.Requery
       
        If Me.NewRecord Then Exit Sub
        With DoCmd
            .SetWarnings False
            .RunCommand acCmdDeleteRecord
            .SetWarnings True
        End With
    End If
End If

Exit_BDELETE_Click:
Exit Sub

Err_BDELETE_Click:
MsgBox Error$
Resume Exit_BDELETE_Click

End Sub

 

[Lgvalencia]

here is you code to test
btw, Response is Integer not string.
also Response and Cancel has nothing to do with your code, so i comment it.

Private Sub BDELETE_Click()
On Error GoTo Err_BDELETE_Click

'Dim Response As String
'Dim Cancel As Integer

'Response = acDataErrContinue

If MsgBox("Delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
    If MsgBox(" Are you SURE you want to delte this Record?" & vbCrLf & _
    "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
        'arnelgp
        'don't Requery it will get new record therefore
        'you will loose Focus of the curren record
        'Me.Requery
     
        If Me.NewRecord Then Exit Sub
        With DoCmd
            .SetWarnings False
            .RunCommand acCmdDeleteRecord
            .SetWarnings True
        End With
    End If
End If

Exit_BDELETE_Click:
Exit Sub

Err_BDELETE_Click:
MsgBox Error$
Resume Exit_BDELETE_Click

End Sub

This is great. I tried it and got this error message

 

[The_Doc_Man]

The two primary reasons for this are (a) the recordset is not updateable or (b) the form's .Allowxxxx flags prevent your action. From earlier comments, (a) seems less likely. Check the form's properties.