Deleting a parent record

[WCStarks]

Currently, when I delete a record in a parent form, I must first manually delete all the associated records in the child form. I would like to automate this task. Can someone suggest how I might code a function to accomplish that?

[orange]

Review Cascade Delete and Referential integrity

[kd2017]

I don't know what exactly is best practice but here is my approach. (Maybe best practice is a deleted flag field and not actually deleting the data...)

I prefer to set up all my table relationships WITHOUT cascade delete enabled in order to avoid major accidents. My delete button asks the user TWICE if they're sure they want to delete the record(s) and my code goes like this:

Private Sub cmdDelete_Click()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim qry As String
    
    If MsgBox("This will delete the current record as well as its children. Continue?", vbYesNo + vbQuestion + vbDefaultButton2, "Delete Records") = vbYes Then
        If MsgBox("Last chance, do you want to abort?", vbYesNo + vbQuestion, "Delete Records") = vbNo Then
            Set db = CurrentDb
            
            qry = "DELETE * FROM tblChildren WHERE ParentForeignKey=" & PARENT_KEY
            db.Execute qry, dbFailOnError
            
            qry = "DELETE * FROM tblParents WHERE ParentPrimaryKey=" & PARENT_KEY
            db.Execute qry, dbFailOnError
        End If
    End If
    

ExitHandler:
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description, vbCritical, "Delete Error #" & Err.Number
    Resume ExitHandler
    
End Sub

I like to force a yes answer and a no answer to avoid the user mindlessly clicking through the message boxes.

[June7]

Deleting data should be a rare event. Once it's gone, it's gone. So why are you deleting?

[pbaldy]

I like to force a yes answer and a no answer to avoid the user mindlessly clicking through the message boxes.

I default the yes/no message box to no and only take action on yes, so if they're not paying attention and just hit enter nothing happens.

[kd2017]

I default the yes/no message box to no and only take action on yes, so if they're not paying attention and just hit enter nothing happens.

Ah, thanks for the tip! The msgbox line then becomes:

If MsgBox("This will delete the current record as well as its children. Continue?", vbYesNo + vbQuestion + vbDefaultButton2, "Delete Records") = vbYes Then

[pbaldy]

No problem. I deal with a bunch of "head down" data entry people who aren't necessarily looking at the screen, so i use that a lot.

[WCStarks]

Thank you. This is also helpful. While it is good to know about the Cascade Delete feature, I was a little nervous about using it. For one thing, while I have been developing this db, I have had Forced Integrity turned off on most of the relationships in the interim.

[WCStarks]

Your right, and I don't allow it in most situations. The database needs to preserve the history. When members become inactive, for example, I never delete them, as they are part of the history. There is one situation, however, where I have individuals linked as participants in events. This is the most used relationship. I have sometimes accidentally created a duplicate event, while reconstructing historical data. In this case, it is usually better to delete one or the other event. I have been doing it manually, when needed. This caused me to ask how to automate this task.

This database was developed over 3 decades after the organization was formed. While developing the db to improve management of the organization, I have also gone back and recovered as much history as I could from the existing records. As you can imagine, this has been a major undertaking. I asked this question, initially, just to learn how to do it. As I am nearly finished with reconstructing the history, it may not be that important any more. But, at least I have learned two ways to do it. Thanks, all.

[WCStarks]

Just one question. Where does PARENT_KEY come from? With all caps, is it some kind of system variable?

[kd2017]

No it's just a place holder for that generic code, you'd replace it with a variable that references the record you're trying to delete.

You'll need to modify the rest of that code to suit your situation for that matter. If you want more specific code for your situation you'll need to post more details...

[WCStarks]

That's ok. The all caps in that one case and not in the others confused me. I was wondering if it had something to do with a value obtained from the MsgBox function, which I couldn't see in the definition of the function. I know what to do. Between you and others, I ended up with two good solutions for the task. Thank you all for your contributions.