data fragments

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What's the best way to make sure that deleted data is deleted and there isn't
any bits left, especially when it comes to deleting across multiple tables?
I've tried several different methods, 'delete' buttons and the like but I
still get remnents of info left on my tables. Any ideas? Thanks.
 
Is your process deleting data from a field or is it deleting the records?

What kind of data is in the 'remnents of info' left in your tables?
 
Hi Freehal04,

The best way is to have your table relationships set to cascade deletes,
that way you delete an item from the main table and all related data also
disappears.

Damian.
 
I have a form that is based on a query. This query gets data from two
tables. I set up a delete button to delete the entire record being displayed
on the form. What happens is the delete button only deletes info from one of
the tables. If asked around but the suggestions have come up nill. I'm at a
loss.
 
Yes I tried that, but what happened was it deleted everything from my tables,
not just the one record being displayed on the form. I couldn't figure it
out, so now, the delete button deletes data from only one table. I've
reviewed my table relationships and everything seems in order. It's a
mystery.
 
Yes I tried that, but what happened was it deleted everything from my tables,
not just the one record being displayed on the form. I couldn't figure it
out, so now, the delete button deletes data from only one table. I've
reviewed my table relationships and everything seems in order. It's a
mystery.
Click to expand...

Deleting a record on a Form based on a two-table query will delete the
record *on the Many side of the relationship*. Let's say you have a
table of CDs related one-to-many to a table of Tracks. If your form is
based on a query relating the two tables, you'll see one CD and only
one Track; deleting "the record" will delete that track, but it won't
delete the CD, because the CD has (or could have) other tracks which
would be left "orphaned".

You could have code which runs a Delete Query using a criterion from
the Form to only delete that one CD; if you have cascade deletes set,
doing so will first delete ALL the tracks (displayed or not), and then
the CD. The criterion would be something like

=[Forms]![mainformname]![ID]

where ID is the unique id from the "one" side table.

It may make it easier and less ambiguous if you do the standard thing
of using a Form for the "one" side table and a Subform for the "many".
Then you can explicitly delete from the subform - deleting a child
record, leaving the parent alone - or from the Form, deleting the
parent and all its children (again, with cascade deletes set).

John W. Vinson [MVP]
 
Thanks John, I think it's starting to make sense. Unfortunately, I used the
table wizard to set up my tables and I don't think it is set up exactly right
to do the subform.
What I mean is the two tables represent information for reports. The first
table is basic info like report number, title (basically stuff to identify
it). The second table is more detailed info, like due dates, comments,
status. Having a subform I could delete would just delete the details but
not the basic details of the report.

Maybe my concept of deleting a record is wrong. I wanted to be able to
delete a record if it was incorrect or obsolete. Would it just be better to
get rid of the delete button and find a different way to control quality
assurance?



John W. Vinson said:
Yes I tried that, but what happened was it deleted everything from my tables,
not just the one record being displayed on the form. I couldn't figure it
out, so now, the delete button deletes data from only one table. I've
reviewed my table relationships and everything seems in order. It's a
mystery.
Click to expand...

Deleting a record on a Form based on a two-table query will delete the
record *on the Many side of the relationship*. Let's say you have a
table of CDs related one-to-many to a table of Tracks. If your form is
based on a query relating the two tables, you'll see one CD and only
one Track; deleting "the record" will delete that track, but it won't
delete the CD, because the CD has (or could have) other tracks which
would be left "orphaned".

You could have code which runs a Delete Query using a criterion from
the Form to only delete that one CD; if you have cascade deletes set,
doing so will first delete ALL the tracks (displayed or not), and then
the CD. The criterion would be something like

=[Forms]![mainformname]![ID]

where ID is the unique id from the "one" side table.

It may make it easier and less ambiguous if you do the standard thing
of using a Form for the "one" side table and a Subform for the "many".
Then you can explicitly delete from the subform - deleting a child
record, leaving the parent alone - or from the Form, deleting the
parent and all its children (again, with cascade deletes set).

John W. Vinson [MVP]
Click to expand...
 
Thanks John, I think it's starting to make sense. Unfortunately, I used the
table wizard to set up my tables and I don't think it is set up exactly right
to do the subform.
What I mean is the two tables represent information for reports. The first
table is basic info like report number, title (basically stuff to identify
it). The second table is more detailed info, like due dates, comments,
status. Having a subform I could delete would just delete the details but
not the basic details of the report.
Click to expand...

I think the wizard was off track here. How are these two tables
related? What are the fields in them? You may need MORE than two
tables. If a Report has multiple Due Dates (for different checkpoints,
or approvals, or whatever) then you should probably have a table with
a record for each due date - it would contain the ReportID as a link
to the main reports table, a field indicating what is due, and a date
field indicating when it's due. Similarly, if there are multiple
comments, you would need a one-to-many relationship to a Comments
table; ReportID, a Memo field for the comment, a comment date field,
and maybe a field for who made the comment.
Maybe my concept of deleting a record is wrong. I wanted to be able to
delete a record if it was incorrect or obsolete. Would it just be better to
get rid of the delete button and find a different way to control quality
assurance?
Click to expand...

That depends entirely on the nature of the data and your business
rules. Deleting an entire report and all its associated data in
response to a single typo sounds like throwing the baby out with the
bathwater though!

John W. Vinson [MVP]
 
Back
Top