Cascade Delete when Its not set?

[Perceptus]

I have a table which has a relationship setup to use a foreign key. Most of the time. when you delete a record from the table, the record comes out clean. No issues. But sometimes, it deletes the foreign key in the foreign key table. I have checked the relationships for the table and I dont see anything marked for cascade delete.

Thoughts?

[June7]

Would have to review db.

Deleting records should be a rare occurrence.

[Perceptus]

Yes it is rare that the record is deleted. So the even more rare occurrence of this happening is so hard to catch. The deletion usually occurs to fix an accidental duplicate entry of a record by the user.

[June7]

Should be a way to catch the duplication before record is committed to table.

For one thing, users should not work directly with tables and queries.

[Perceptus]

I attached a report of the objects used in the databases for this occurence which has occured again. Could you see a problem from it?

[June7]

Sorry, that doesn't help me. Don't want to read through 36 pages.

[Perceptus]

Well, no. but theres relationship diagrams that cover how they work.

[orange]

I don't think many readers want to read the full M$oft documentation of your database.
Perhaps you could describe/summarize the issue showing records before and after,
And tell us exactly what you think should have happened compared to what you observed.

[Perceptus]

I would have just scrolled to the section concerning the relationships.

[June7]

Scanned. Doesn't help me understand nor explain the issue. I've never spent much time examining the printed documentation generated by Access. Kills too many trees.

[Perceptus]

Yes it is 99% junk IMO. Was just hoping it would help in some light.

Thank you for your time.

[Perceptus]

I have a Form called Workorders has a datasource of

SELECT tblWorkOrders.*, tblWorkOrderStatus.SortOrder FROM tblWorkOrders INNER JOIN tblWorkOrderStatus ON tblWorkOrders.Status = tblWorkOrderStatus.id WHERE (((tblWorkOrders.OnOverview)=True) AND ((tblWorkOrderStatus.Status)<>"Non-Prod"));

I am using it in Datasheet Mode.

When I choose to Delete(I press the delete key) a record from this Form that runs on the above recordsource. It deletes the Status from tblWorkOrderStatus as well as the record you choose to delete.

[June7]

For one thing, don't use INNER JOIN. You include tblWorkOrderStatus table in RecordSource so can filter on descriptive Status? If you save ID into tblWorkOrders, use ID value for filter parameter, not descriptive Status. Then don't need tblWorkOrderStatus.

However, INNER JOIN should not cause record to delete from tblWorkOrderStatus.

[Perceptus]

I agree with all of your post. I will update accordingly.

[orange]

I just set up a test. I had Book2, Author and BookAuthor and added a new book #10 and a new author #8 and did add the record into junction table BookAuthor. I did have referential integrity set up via relationships with Cascade delete set (as I found out). I have attached a jpg of the notification related to the cascade delete.(below) (I cancelled the delete)

I then closed all tables, adjusted relationship to not set cascade delete. I went through the process to delete Book#10, can not do it because there is a related record in BookAuthor. I then tried to delete the Author #8, same message --the record can not be deleted or changed because table BookAuthor includes related records.
I then deleted the record in BookAuthor that had Book10 author 8 and it deleted fine, but did not change anything in Book or Author table.

So with explicit Cascade delete option set, it gave definite warning message.
Without explicit Cascade delete, it would not allow deletion of records from tables involved in a junction, and did notify that a junction record exists. But it did allow deletion of record from the junction, but did not remove the related book record or author record.

All that to say I don't know why it did the cascade delete.