Related records do not delete

  • Thread starter Thread starter BrianS
  • Start date Start date
B

BrianS

I have a simple relationship created using referential
integrity and cascading both updated fields and delete
records. I have two tables joined using the primary key
on the "1" side and the same field name as a forign key on
the "many" side. I am using fields from both tables in a
single form. The problem I'm having is when I delete a
record, only the field from the "many" side delete and the
field data from the "1" side does not. Anyone have an
idea why this would happen if I have the two tables
related in a one-to-many relationship? Thanks!
 
BrianS said:
I have a simple relationship created using referential
integrity and cascading both updated fields and delete
records. I have two tables joined using the primary key
on the "1" side and the same field name as a forign key on
the "many" side. I am using fields from both tables in a
single form. The problem I'm having is when I delete a
record, only the field from the "many" side delete and the
field data from the "1" side does not. Anyone have an
idea why this would happen if I have the two tables
related in a one-to-many relationship? Thanks!
Click to expand...

If your form is based on a query that joins the two tables, only the
record from the table on the "many" side can be deleted. Consider:
when you delete a record from such a join, Access can't know that you
intend to delete the "one"-side record. Suppose that there are several
records on the "many" side related to this same record on the "one"
side. Then a join between the tables will return multiple records, each
containing the same data from the "one"-side table, and different data
from the "many"-side table. Now you delete one of those records. The
"one"-side record must still be left intact to serve as the parent of
the remaining "many"-side records. *Maybe* you want to delete the
parent and all its children, but how could Access possibly know that?

The only way you could make this work without programming is to use a
form/subform arrangement, with the main form based on the "one" side
table and the subform based on the "many" side table. Then deleting the
main-form record will, via cascading deletes, delete the related
records. A programming alternative would be to use your current form
and put a command button on it with code that runs a delete query to
delete the one-side record.
 
Back
Top