How to delete main form record within the subforms record (1 Viewer)

MK1999

New member
Local time
Today, 16:20
Joined
Mar 30, 2022
Messages
24
i have a control tab that include main form and subforum. what i want to do is when the user click on delete button it delete all the records from the forum and subforums and not the main forum only.
 

Ranman256

Well-known member
Local time
Today, 09:20
Joined
Apr 9, 2015
Messages
4,339
Run a delete query on the subTable using the master key as criteria.

docmd.openquery “qdDelSubTbl”

like: delete *from table where key =forms!fMyForm!txtKey
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:20
Joined
Jul 9, 2003
Messages
16,245
A main form with a subform indicates that you have a parent-child relationship in your data. If you delete the parent record, you could be damaging your data structure...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:20
Joined
Feb 19, 2002
Messages
42,981
A main form with a subform indicates that you have a parent-child relationship in your data. If you delete the parent record, you could be damaging your data structure...
Only if you didn't bother with defining relationships and checking the enforce RI box.

If you want the delete of a parent record to delete the child records, you can check the "Cascade Delete" box on your relationship. You did create relationships AND enforce RI, didn't you?

Cascade delete is of course a dangerous feature but that shouldn't stop you from using it provided you use it appropriately. Setting relationships and checking the enforce RI option will prevent you from deleting a parent record if it has any child records. That is the standard setting. But, if the tables have a hierarchical relationship, then the child records have no meaning without the parent so, if your business rules allow you to delete the parent record, checking the Cascade Delete option allows the database engine to cascade the delete to the child records.

The operative phrase here is "business rules". Many applications don't allow the parent records to be deleted ever or perhaps only under certain circumstances. The Cascade Delete makes it easier to make a mistake. Just be sure you understand completely what will happen with that setting checked.

An example of when Cascade Delete is appropriate is for Students -->Classes. If your business rules allow students to be deleted, the cascade delete will also delete any classes they took. NOT the classes themselves, just the junction table rows that implement the m-m between students and classes. Without the cascade delete, you would have to delete the Studentclasses records and then you could delete the Student. Another is Orders--> Items. If you delete an order, the items on that order have no meaning by themselves and so should go. Again, this is another m-m example. Deleting the items from an order, deletes the rows in the junction table, NOT the products themselves.

An example of when Cascade Delete is NOT appropriate is for State -->Customer. It is not likely you would ever delete a state but if you did, you would surely not want to delete customers in that state also:) Or Department --> Employee. Maybe you will be laying off all the employees of a department if you are getting rid of it but surely you wouldn't want to just delete the employees that worked there also.

If you consider the examples, you will see that the distinction is - do the child records have meaning outside of the relationship? Junction tables NEVER have meaning outside of their relationship. However, you might allow cascade delete from one direction but not from the other. Maybe it makes sense to be able to delete the items when you delete an order but also, you probably would not want to be able to delete items from all orders if you were to delete a product. So by not enabling cascade delete on the Product--> items half of the relationship, you can have RI automatically prevent deleting a Product if you've ever created any orders for it. Otherwise, the delete if the product is fine since it was never used for anything.

Books are written on schema design and relationships. Do read some to get a full understanding of how relationships work and how they help you to manage referential integrity.
 
Last edited:

Users who are viewing this thread

Top Bottom