How to display linked records before deleting them.
Access Archon #147
Introduction
If you have set up relationships between tables with Cascade Delete Related Records unchecked (a good idea, to prevent inadvertently deleting records you might want to save), then you might want a way to check which records are linked before deleting them.
Figure A. A relationship with Cascade Delete Related Records unchecked
If you simply try to delete a record in the “one” side of a one-to-many relationship with Cascade Delete Related Records set to False (for example, tblOrders in the sample database), you will get the message: “The record cannot be deleted or changed because table ‘tblOrderDetails’ includes related records.” But you can still delete a record with linked records, using VBA code. This article shows how to display linked records before deleting a record in the “one” table, with some checking to prevent deleting inappropriate records.
The Order Cleanup Form
The sample database (Confirm Deletion (AA 147).mdb) has a set of tables from the Northwind sample database, given the “tbl” prefix and with their dates updated by ten years. I also added a Returned field (Yes/No data type) to tblOrders. The Order Cleanup form (frmOrderCleanup) is a form with a datasheet subform that displays the most significant fields from orders, for quick review and cleanup. The assumption is that if an order was shipped and not returned, you should have to confirm proceeding to the next stage of deleting linked records.
Figure B. The Order Cleanup form
Note: Of course, deleting records is not the only way of dealing with unshipped or returned orders – you might want to archive them instead (see Access Archon #143), or set a field (say, Inactive) to True, for purposes of filtering.
An unbound textbox in the footer of frmOrderCleanup displays the Order ID of the selected Order Details record in the subform; this is the record that will be deleted when you click the Delete Ordercommand button.
The code in the cmdDeleteOrder Click event procedure first gets the OrderID value from the selected subform record, then checks whether there is a valid date in the DateShipped field and whether the Returned field has a True or False value. If there is a valid shipped date and the order was not returned, a confirmation message is displayed:
Figure C. A confirmation message when deleting a record that was shipped and not returned.
Clicking the Nobutton closes the message and returns to frmOrderCleanup; clicking the Yesbutton opens a form that displays the linked records in tblOrderDetails for the selected record in tblOrders. If you select a record that has not been shipped, or has been returned, this form opens immediately on clicking the DeleteOrdercommand button.
Figure D. A form displaying the linked Order Details records for the selected order
On this form, you can delete a single record by simply selecting it in the datasheet and pressing Delete (you may get a standard Access deletion confirmation message, depending on the database’s settings), or you can delete all the linked records by clicking the Delete order and linked detailsbuttons. After deleting the linked records and the record in tblOrders, the form closes and the OrderID record on frmOrderCleanup displays #Deleted in several fields; after closing and reopening this form, the record will be gone.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch147.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Confirm Deletion (AA 147).mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
