Have to delete record twice

[Stephanie]

Hello. I have a form based on Contacts. Contacts are assigned (or not) to
Chapters. Here's the design:

Contacts
ContactID
ContactLastName...

ChapterMember
CapterMemberID
ContactID
ChapterID

Chapter
ChapterID
ChapterName

Since Contacts doesn't contain ChapterMember info, I created a subform:
SELECT ChapterMembers.ChapterMemberID, ChapterMembers.ContactID,
ChapterMembers.ChapterID, Chapter.ChapterName
FROM Chapter INNER JOIN ChapterMembers ON Chapter.ChapterID =
ChapterMembers.ChapterID;

And linked the ChapterMember subform to Contacts on ContactID. All looks
good.

However, when I try to delete a Contact that has an assigned Chapter, first
I have to click in the ChapterMember subform and click delete. Only then can
I click anywhere on the Contacts form and click delete.

So I tried to make Chapter a combo box on Contacts, but since Contacts isn't
a field in ChapterMember there is nothing to bind it to. Then I tried adding
Chapter and ChapterMember to the RecordSource of the Contacts form and was
able to build the combo box, but that limits the Contacts to those that has
an assigned Chapter.

Serves me right for trying to delete! Any suggestions? Thanks.

 

[Brian]

Set up a Cascade Delete relationship between Contacts.ContactID (one) to
ChapterMember.ContactID (many). Then, when you delete a Contact from the
Contacts table, all of that Contact's ChapterMember entries will be
automatically deleted.

 

[Stephanie]

Excellent! Thank you so much- it worked perfectly!

 

[Brian]

Just be careful about where you use CascadeDelete. It is VERY useful when
appropriate, but Imagine what would happen if you set it up in a relationship
between a Customers table & a CustomerInvoices table. When you delete a
customer, all that customer's invoices would be gone!