If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Have to delete record twice
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. |
#2
|
|||
|
|||
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" wrote: 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. |
#3
|
|||
|
|||
Excellent! Thank you so much- it worked perfectly!
"Brian" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
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! "Stephanie" wrote: Excellent! Thank you so much- it worked perfectly! "Brian" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Inspect record, replace null field on output | RNUSZ@OKDPS | Setting Up & Running Reports | 3 | April 5th, 2005 04:27 PM |
Delete Record Confirmation | Tiago | Using Forms | 1 | March 31st, 2005 12:11 PM |
Yet another beginner that cannot delete record in a half filled fo | Richard Smith | Using Forms | 1 | March 16th, 2005 02:25 PM |
Default message coming up when I try to delete a record in a form | Larnoc | New Users | 4 | August 8th, 2004 11:33 PM |
Deleting Records does not Delete Records | Lucky Man Cree | New Users | 7 | July 6th, 2004 11:33 PM |