Solved Deleting Records Warning

[MattBaldry]

Hi all,

Is there a ready made code for letting you know which cascading records would be deleted if you delete from a one-to-many table?

Let's say I have a Product that is used on lots of BOMs. If I try to delete that record, I would like it list the BOMs this product appears on.

I did have a search, but I must be using the wrong terms as I couldn't find anything that looked right.

Or is it a case of having a Delete form that opens up and either shows a list of all BOMs it is used on, and no delete button. Or a blank list and a delete button.

~Matt

 

[June7]

Nothing ready made.

Yes, use a form to display related records that will be deleted.

 

[ebs17]

Use a select query.

SELECT
   *
FROM
   ChildTable
WHERE
   ParentID = YourProductID

 

[Gasman]

Use something like SimpleCSV to show all the BOMs affected.

Code Snippets

 

[MattBaldry]

Thanks All,

I thought this may have been the case so already started on the form, just in case.

I shall close this down.

~Matt

 

[Pat Hartman]

Why would you want to delete an item that is used on other BOMS? I would not allow cascade delete in this situation. Cascade delete should only be specified in a hierarchical relationship where the child records have no meaning without the parent record such as Order/Order details. But I would never allow cascade delete under any conditions between product and order details which is the other side of the order details m-m. So, perhaps there is a business reason for allowing an individual order to be deleted and so you might enable cascade delete on that side of the relationship but never from the product side of the m-m.

 

[MattBaldry]

Why would you want to delete an item that is used on other BOMS? I would not allow cascade delete in this situation. Cascade delete should only be specified in a hierarchical relationship where the child records have no meaning without the parent record such as Order/Order details. But I would never allow cascade delete under any conditions between product and order details which is the other side of the order details m-m. So, perhaps there is a business reason for allowing an individual order to be deleted and so you might enable cascade delete on that side of the relationship but never from the product side of the m-m.

Hi Pat,

I don't want to delete them as such. I have turned off cascading deletes, but what I did want was a list of the BOMs that would be affected if I did want to delete them. I try to avoid any deletions at all, but I have been requested by the powers above me to allow deletion of products.

So what I wanted from this was a way of people not deleting anything that has related records (turn off cascading deletes) but then tell them why they cannot delete.

I am with you in not deleting stuff.

~Matt

 

[GPGeorge]

Hi Pat,

I don't want to delete them as such. I have turned off cascading deletes, but what I did want was a list of the BOMs that would be affected if I did want to delete them. I try to avoid any deletions at all, but I have been requested by the powers above me to allow deletion of products.

So what I wanted from this was a way of people not deleting anything that has related records (turn off cascading deletes) but then tell them why they cannot delete.

I am with you in not deleting stuff.

~Matt

Do you need to specify all of the the possible BOMs that would be impacted by deleting a product, or do you need to tell the user they can't delete the product because it is used "in one or more BOMs"?

The latter is relatively straightforward. The former could be a lot of additional work.

Do the powers above you want to invest the money (in terms of your salary to do the work) to generate that complete list? If so, it's possible to run a query against the existing records and return a list of all of those BOMs in a form. But unless there is some value to showing the list just to confirm that the product can't be deleted, that seems like a poor ROI on your development time.

Using the form's Error event, you can capture the data error raised when the database engine refuses to delete a record due to a referential integrity constraint, and replace it with a message like "You can't delete Product X because it is already used in one or more existing BOM's"

I would ask the powers that be to confirm they will sign off on a couple of extra hours of work for what seems to me, at any rate, to be minor additional value in the form of a list of existing BOMs.

 

[Pat Hartman]

Just FYI, Cascade Delete will not delete any items that have other dependencies.

but what I did want was a list of the BOMs that would be affected if I did want to delete them.

@ebs17 provided the query necessary to find the related items. Do NOT display the list as a query. Display it on a form or report. WHY? Queries display editable data unless you have taken steps to ensure that the query is not updateable. A simple way is to make the query a "totals" query by using group by for each column even though no grouping will actually occur.

 

[DickyP]

To me the logical way to do this would be to iterate through the Relations collection and gather up the .Table, and .ForeignTable properties.. This at least would identify all your dependencies.

However, if you want more info then it is somewhat more complex and the documentation about relationships is not only sparse but somewhat arcane, and the ideas above would prevail.