Delete from 2 tables on 1 form (1 Viewer)

statsman

Active member
Local time
Today, 14:58
Joined
Aug 22, 2004
Messages
2,088
I am currently working on a charitable event database in Access 07.
I have two tables. Patron contains the data for people who have donated in the past (name/address etc.). Donations contains the data regarding their donations (year, how much, type of donation etc.)
After 3 years with no support, I wish to delete the Patron and their Donation records.
In my form I have the info from Patron which is included to ensure the correct patron is deleted and on a sub-form I have their records for Donations.
The two tables are linked in the query by the Patron ID which is included in the Donation.
All fields on the form and sub-form are set to Enabled-No, Locked-Yes.
When I click the Delete button on the form, the Donation records are deleted but the Patron record is not.
All suggestions on deleting the records from both tables gratefully accepted.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:58
Joined
Oct 29, 2018
Messages
21,358
Hi. We usually tend to recommend against deleting records. What happens if the patron decides to come back later on? You would have lost all their historical data. In any case, one way to delete a record with all its related records is to use cascade delete when you enforce referential integrity in the table relationship.
 

Ranman256

Well-known member
Local time
Today, 14:58
Joined
Apr 9, 2015
Messages
4,339
run 2 queries:
delete the recs from the child table,
then delete the rec from the master table.

use a text box on the form:
delete * from table where clientID = forms!myForm!txtBox

if the tables are setup with cascade delete you don't need the 2 queries, you only need delete the master record and all the child recs will go too.
 

statsman

Active member
Local time
Today, 14:58
Joined
Aug 22, 2004
Messages
2,088
After reflection I think DBGuy is probably right.
I'll hang onto the records in case a Patron returns.
But thanks to all.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:58
Joined
Oct 29, 2018
Messages
21,358
After reflection I think DBGuy is probably right.
I'll hang onto the records in case a Patron returns.
But thanks to all.
Hi. If you don't want to see old data, you could add a date field to the Patrons table to indicate the date when you would have deleted it. On your form, you can add a filter (a criteria in the query source) to exclude those patrons with a date in this field. And if you're displaying the related records in a subform, then by simply not showing the patron on the main form, the related records won't show up either. In effect, it would appear those records were deleted, but they're really not.
 

statsman

Active member
Local time
Today, 14:58
Joined
Aug 22, 2004
Messages
2,088
It's an annual event and I have categories for patrons.
"A" indicates they made a contribution last year.
"B" indicates they have made contributions in the past, but not last year.
"C" indicates they have not made a contribution for 2 plus years.
I think I can accomplish the same thing by specifying the category.
The category is updated each year and what I wanted to do was prevent sending letters to "C" categories as it's unlikely to get a response.
It occurred to me to just not print those letters, restricting them to "A" and "B".
 
Last edited:

Users who are viewing this thread

Top Bottom