delete current records & related records together

[WyvsEyeView]

Upon clicking Delete to delete the current record on frmTopics, I want several things to happen:

1) Display a custom message rather than Access's standard "You are about to delete n records" one.
2) Delete the record.
3) Delete all related attribute records.
4) Go to a new record.

Here is my code:

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

DoCmd.SetWarnings (False)

Dim strMsg As String
strMsg = "Are you sure you want to delete this topic? You cannot undo a deletion."
If MsgBox(strMsg, vbOKCancel) = vbOK Then
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.GoToRecord , , acNewRec
    
    Dim strSQL As String
    strSQL = "DELETE * FROM tblTopicAttributes" & "WHERE tblTopicAttributes.top_id=" & Forms!frmTopics!nbrTopID & ";"
    CurrentDb.Execute strSQL, dbFailOnError
Else
    Me.Undo
End If

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
DoCmd.SetWarnings (True)
    
End Sub

The record is successfully deleted, and Access's standard message suppressed (is there a better way?) but there is the following problem:

When there are related attribute records that could be deleted, I get a "syntax error in FROM clause error" and the line CurrentDb.Execu te strSQL, dbFailOnError is highlighted in my VBA code. I tried this out as a plain query, which worked, and then dropped it into this "formula." Maybe I just missed a quote or something, although I have tweaked it every way I can think of. This issue is somewhat related to my other thread about creating a new record and related records--the concatenation principle--which I now understand in theory, although apparently not in practice :)

[missinglinq]

Maybe I'm reading this wrong, but it looks like you're deleting a record, then trying to delete related records by using the nbrTopID field from the now deleted, non-existing record in the Where statement.

Maybe delete the related records then the main record?

Linq ;0)>

[ADezii]

You can DELETE a single Topic, and all related Topic Attributes, in a single Executable Statement, with a few simple modifications to your logic and code:

1. Assuming you have a valid Join between the Primary Key Field of your Topics Table {1} and the Foreign Key Field of the Topic Attributes Table {MANY}:
   • Check Enforce Referential Integrity on the Properties for the Join.
   • Check Cascade Delete Related Records on the Properties for the Join.
2. Run the following code wherever appropriate:
   

   Dim strMsg As String
   Dim strSQL As String
   
   strMsg = "Are you sure you want to delete this Topic along with " & _
            "all related Attributes? You cannot undo a deletion."
            
   If MsgBox(strMsg, vbYesNo) = vbYes Then
     strSQL = "DELETE * FROM tblTopicAttributes WHERE tblTopicAttributes.top_id=" & _
               Forms!frmTopics!nbrTopID & ";"
     CurrentDb.Execute strSQL, dbFailOnError
     DoCmd.GoToRecord , , acNewRec
   Else
     'do absolutely nothing
   End If

3. The Topic Record, as indicated by the nbrTopID, as well as all related Records, if any, will now be DELETED. You will then advance to a New Record in your Main Form.
4. Any questions, please feel free to ask.

[WyvsEyeView]

Thanks to both of you! Linq, you were right in that I was deleting the main record, then trying to delete the related records. Duh. ADezii, I'm always nervous about cascading deletes because it's so easy for a user to delete more than they mean to, and frankly (though this may be lazy of me) too intricate for me to then programmaticall y make sure they don't. So I didn't make that change but I did take your other suggestions and now have the code working perfectly. Thanks again!

[ADezii]

Thanks to both of you! Linq, you were right in that I was deleting the main record, then trying to delete the related records. Duh. ADezii, I'm always nervous about cascading deletes because it's so easy for a user to delete more than they mean to, and frankly (though this may be lazy of me) too intricate for me to then programmaticall y make sure they don't. So I didn't make that change but I did take your other suggestions and now have the code working perfectly. Thanks again!

I'm always nervous about cascading deletes because it's so easy for a user to delete more than they mean to

Understood, but aren't you currently in a situation where you will be leaving Orphaned Records?

[WyvsEyeView]

Before making these changes, I was leaving orphaned records, which is why I implemented this approach. Unless I have missed something, my current Delete button code will delete the main record and any related records, thus avoiding orphaned records. All my tests seem to indicate that is what is happening.