Delete button - can't undo

[Sketchin]

Hi all,

I have a form with a sub form and a delete button residing on the form.

When pressed, the sub form records are deleted, then i am asked if I want to delete 1 record. If I say no, the form record stays put, but the sub form stuff still gets deleted. I know I just have the code in the wrong place and I'm hoping someone can help me out, as I have limited VBA experience.

Here is my code:

Private Sub cmdCancel_Click()
Dim intID As Integer
If Not IsNull(Me.ReservationID) Then
    intID = Me.ReservationID
Else
Exit Sub
End If
CurrentDb.Execute "DELETE tblReservation_details.* " & _
                "FROM tblReservation_details " & _
                "WHERE reservationID=" & intID
Me.SubfrmReservation_Details.Requery
On Error GoTo cmdcancel_Click_Err
    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
cmdcancel_Click_Exit:
    Exit Sub
cmdcancel_Click_Err:
    MsgBox Error$
    Resume cmdcancel_Click_Exit
 
End Sub

 

[sparks80]

Hi,

Do you want to prompt the user if they want to delete the record, and then either cancel or delete both form and subform records?

 

[Sketchin]

Yes exactly. I would like to cancel the delete on both form and subform records.

Thanks!

 

[sparks80]

Try this, sorry I haven't had time to test it:

Private Sub cmdCancel_Click()
    Dim intID As Integer
    Dim intContinue As Integer

    ' Error Handling
    On Error GoTo cmdcancel_Click_Err

    ' Check for a valid ReservationID. If not found then cancel.
    If Not IsNull(Me.ReservationID) Then
        intID = Me.ReservationID
    Else
        GoTo cmdCancel_Click_Exit
    End If
    
    ' Ask user if they would like to delete reservation
    intContinue = MsgBox("Are you sure you want to delete this reservation?", vbYesNo + vbQuestion, _
        "Delete Reservation?")

    ' If user selects no then exit procedure
    If intContinue = vbNo Then
        GoTo cmdCancel_Click_Exit
    End If


    ' Resume code if an error occurs - this is to handle error caused 
    ' if previous control cannot be selected for some reason

    On Error Resume Next
    DoCmd.GoToControl Screen.PreviousControl.Name
    ' Now clear error
    If Not Err.Number = 0 Then Err.Clear

    ' Check that this an existing record. If so then delete both subform and main form records
    If (Not Form.NewRecord) Then
        CurrentDb.Execute "DELETE tblReservation_details.* " & _
            "FROM tblReservation_details " & _
            "WHERE reservationID=" & intID
        Me.SubfrmReservation_Details.Requery
        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

cmdcancel_Click_Exit:
    Exit Sub
cmdcancel_Click_Err:
    MsgBox Error$
    Resume cmdcancel_Click_Exit
 
End Sub

 

[Sketchin]

Worked like a charm!

Thank you so much for the help!!!

 

[sparks80]

Glad to hear it, and you are most welcome

 

[Pat Hartman]

FYI - it is far safer to define relationships and specify cascade delete than to run queries to delete "child" records. When you do it the right way, you simply delete the parent record and the database engine takes care of the rest. Also, the db engine encloses the whole action in a single transaction so either it all succeeds or it all fails. Granted it is remote but in your situation, you could delete the "child" records and end up not deleting the "parent".

Always enforce RI and Cascade Delete (where appropriate). In a transactional application, there is never a reason not to. You want the database engine doing as much of the heavy lifting as possible. This is even more important if the BE database is used by different applications. If you code your own RI instead of leaving it to the db engine, you must put your code every place updating can be done or you have to prevent updating from some places which also requires code. Do it right, it is actually less work for you.

 

[sparks80]

That is very good advice - in case you didn't know, RI stands for Relational Integrity - not to be confused with ReservationID!

In your example there are presumably two tables that are related by the "ReservationID", with a one-to-one or one-many relationship.

The main form recordset corresponds to the "one" side of the relationship, and the child recordset to other. If the cascade delete option is enabled then deleting the record on the main form will automatically delete the records on the subform.

If you decide to do this, then you can remove the line beginning "Currentdb.Execute...", and the following line.

 

[Sketchin]

Haha, databases problems are so easy to solve when someone gives you the answer.

After reading all of this, I totally had one of those "Well obviously..." moments. Its even worse that I do it the right way on a couple other forms for Bills of Materials and invoices with one-many relationships!

Thanks for the help and advise guys!