Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-21-2019, 05:39 AM   #1
statsman
Newly Registered User
 
statsman's Avatar
 
Join Date: Aug 2004
Location: Toronto ON
Posts: 2,074
Thanks: 4
Thanked 3 Times in 3 Posts
statsman will become famous soon enough
Delete from 2 tables on 1 form

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.

__________________
Dart players always have a point.
statsman is offline   Reply With Quote
Old 07-21-2019, 05:56 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,556
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Delete from 2 tables on 1 form

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
statsman (07-21-2019)
Old 07-21-2019, 06:01 AM   #3
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,334
Thanks: 0
Thanked 735 Times in 720 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Delete from 2 tables on 1 form

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.

Ranman256 is offline   Reply With Quote
The Following User Says Thank You to Ranman256 For This Useful Post:
statsman (07-21-2019)
Old 07-21-2019, 09:37 AM   #4
statsman
Newly Registered User
 
statsman's Avatar
 
Join Date: Aug 2004
Location: Toronto ON
Posts: 2,074
Thanks: 4
Thanked 3 Times in 3 Posts
statsman will become famous soon enough
Re: Delete from 2 tables on 1 form

After reflection I think DBGuy is probably right.
I'll hang onto the records in case a Patron returns.
But thanks to all.
__________________
Dart players always have a point.
statsman is offline   Reply With Quote
Old 07-21-2019, 10:45 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,556
Thanks: 38
Thanked 869 Times in 852 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Delete from 2 tables on 1 form

Quote:
Originally Posted by statsman View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-22-2019, 07:22 PM   #6
statsman
Newly Registered User
 
statsman's Avatar
 
Join Date: Aug 2004
Location: Toronto ON
Posts: 2,074
Thanks: 4
Thanked 3 Times in 3 Posts
statsman will become famous soon enough
Re: Delete from 2 tables on 1 form

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".

__________________
Dart players always have a point.

Last edited by statsman; 07-22-2019 at 07:28 PM.
statsman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete query error could not delete from specified tables sivani_007 Queries 3 03-10-2015 12:00 AM
Delete Query (Specify the tables containing the rows you want to delete) elitehass Queries 3 04-11-2013 02:17 AM
Duplicate Records Delete Query "Could Not Delete From Specified Tables" 1cekream Queries 3 07-21-2011 02:18 AM
Delete Query Problem (Cannot delete from specified tables) bc9999 Queries 3 10-26-2008 09:57 AM
Need to delete multiple tables from one form silentfire Tables 2 03-23-2008 05:18 PM




All times are GMT -8. The time now is 07:00 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World