Cascading Deletes

[TryTryAgain]

I have a database that has primary table 1, related to table 2, which is related to table 3, and 3 other tables related to table number 3. Table one has a one to many relationship to table 2, table 2 has a one to many relationship to table 3, and table 3 has a one to many relationship to its related tables. All have an Auto key (indexed - no duplicates), in addition to some other keys that are indexed (duplicates allowed).I have the relationships set for cascading deletes.

I'm using a command button (code generated by the Wizard)with some additional code by me added to ensure I get the right record to delete records. When I delete a record in any of the subordinat tables to table 3, everything is fine. When I delete a record in table 3, it cascades the deletes up - it deletes all other records in table 3, plus the related record in table 2, and the related record in table 1. Does anyone know what I have set up incorrectly? I can find nothing in the Help or manuals that discuss this problem.

I appreciate anyone who will take pity......

 

[CharlieT302]

My first guess is the code you added manually. The "Cascading Delete Related Records" option only deletes foreign records, not primary. Is it possible that you mistakingly have the delete button pointed to the primary table?

Out of curiosity, what type of joins do you have? When you double-click the join in the the "Relationship Window," is the number 1, 2, or 3 selected?

 

[TryTryAgain]

My join type is a "1". The other curious thing is that the word "#deleted" appears in all the fields on the forms when this happens. The only code I inserted was to set up the key fields to ensure the correct record is deleted.

 

[TryTryAgain]

Correction - I'm not setting up the key, I had code inseted to display a warning message, giving the user a change to change his/her mind. The delete code is what the wizard set up:

Private Sub CmdDelLO_Click()
On Error GoTo Err_CmdDelLO_Click
Dim ans1
ans1 = InputBox("CAUTION! This will delete all associated Acquisition Estimate Records, Encroachment Rcords and Permanent Road Access Records. Enter Y to proceed, N to Cancel")
If UCase(ans1) <> "Y" Then
MsgBox ("Delete Cancelled")
Exit Sub
End If
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "Delete Completed"
Exit_CmdDelLO_Click:
Exit Sub

Err_CmdDelLO_Click:
MsgBox Err.Description
Resume Exit_CmdDelLO_Click

End Sub