Deleting a record from a main-form and sub-form simultaneously

[Mbri]

What we have is two separate tables. On one table is a list of functional locations for motors in the mill that we're working at. On the other table is a history that is related to each functional location. The problem is that there are multiple histories for each functional location and when we want to delete an entire functional location out of the database as the mill changes, the histories are contained in a sub-form and cause this problem... When a functional location is deleted from the first table, the coinciding history pertaining to that functional location remains in the sub-form's table.

The only possible way that we have found to deal with this is to delete all of the history records prior to deleting the functional location record. What we would like is to use one delete button on the main form to delete all of the history records and the functional location record with one click.

 

[scriverb]

If you setup your relationships correctly between the two tables with the option of cascading deletes then a delete of the Main table will automatically delete records from the secondary table that link according to the established relationships.

Open the Relationships window from your menubar(Tools/relationships) and click on the Join line between the two tables. Now right mouse button click and select Edit Relationship. You should now see the relationship fields between the two tables. The Enforce Referential Integrity should be selected and the Cascade Delete Related Records should be selected.

With this setup when you delete a record from the main table all historical records linked by the fields in the relationship will be deleted automatically.

Post back with questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???