Related Records in SubForm Block Delete Record

[Guest]

I have a form with 2 subforms on it. The main form has a one to many
relationship with each of the subforms with its primary key. I am trying to
create a button on the main form that will delete the entire record that is
linked by this primary key (hope I'm saying the correctly) Problem is, I get
an error message that states "The record cannot be deleted or changed because
"Subform1" includes related records."

Any one know how to write the code to accomplish this? (I know you do! -
someone always comes to my rescue - and I really appreciate it)

 

[Allen Browne]

Would it make sense to enable Cascading Deletes between these 2 tables?
That's something you want to do sparingly, but if it is suitable it will
solve the problem without code.

1. Open the Relationships window (Tools menu).

2. Double-click the line joining the 2 tables.

3. Check the box for Cascading Deletes.


The alternative would be something like this:

If Me.Dirty Then
Me.Undo
End If
If Me.NewRecord Then
Beep
Else
strSQL = "DELETE FROM MySubTable WHERE MyForeignKey = " & Me.ID &
";"
dbEngine(0)(0).Execute strSql, dbFailOnError
RunCommand acCmdDeleteRecord
End If

 

[Guest]

Thanks Allan - Cascading Deletes was the answer in this case!

 

[Guest]

Oops - one more question on this, the delete record works now, but the user
gets 3 dialog boxes confirmating the delete. Is there anyway to replace
these 3 Access generated message boxes with one that asks for confirmation of
the delete?

 

[Allen Browne]

Again, you will want to do this sparingly, but your options include:

1. Turn SetWarnings off:
DoCmd.SetWarnings False

2. Use code in the BeforeUpdate event of the form to suppress the message:
Response = acDataErrContinue

3. Change the options under:
Tools | Options | Edit/Find | Confirm

 

[Guest]

Thanks - you've been a great help!