Code to test is a delete is possible before deleting

[Robyn_P]

Hello. I have a form with a button that allows the user to delete the current record from a table (tblEquipment). This table has many relationships and cascade delete is not set on them for good reason, so if the Equipment is used anywhere the delete will fail. Currently I just handle this by turning setWarnings to False before the delete attempt and turning them back on afterwards, which is fine, but I would like to be able to output a custom error message explaining why the delete failed and for this I need a way of knowing the delete is going to fail. Is there a way to test whether a record delete will be successful in VBA before executing it?

[orange]

Can you tell us a little more about the application?
If you set up referential integrity, Access will not let you create an orphan record. That is, you will get an error if you attempt to delete a parent record that has child/children.

Many applications do not use physical deletes. Instead they use a flag/indicator such as IsDeletedYN. The flag by default is No, but if the record is deleted the flag is set to Yes. Queries etc can use the value of the IsDeletedYN flag in calculations/reports...

Also, instead of setting/unsetting Warnings, you can use the CurrentDB.Execute construct which avoids the setting/unsetting, and will present an error if an error condition is encountered.

[Minty]

The answer is yes - What are the criteria to determine if a delete is possible?

[Robyn_P]

Currently simply the referential integrity

[Robyn_P]

Orange, yes it's the referential integrity that prevents the delete. Other parts of the database use an IsDeleted Flag, currently not considered for here, going for a very simple "If the equipment isn't used anywhere else allow it to be deleted, if it is and they really want to get rid of it tell them to contact admin." I know it's a bit hacky but it should be good enough for now.

[kd2017]

It sounds to me like your simply looking for an event that fires when referential integrity is activated to present the user with a custom error message?

Take a look at the following code and play with it. When it tries to delete a record and fails it will cause an error, the error handling will spit out a message w/ the error code and error description. In your case, an error caused by trying to break integrity would look like "Error #3200 - The record cannot be deleted or changed because table 'RELATED TABLE NAME' includes related records."

You can use Err.Number to test for error # 3200 and give the user your custom message.

Private Sub cmdDelete_Click()

On Error GoTo ErrHandler
    CurrentDb.Execute "DELETE FROM Table1 WHERE ID=" & Me!ID, dbFailOnError
    Me.Requery

ExitHandler:
    Exit Sub
    
ErrHandler:
    MsgBox "Error #" & Err.Number & " - " & Err.Description
    Resume ExitHandler
    
End Sub

Note: You asked for a way to test if it's going to fail before executing the code. This does not do that and only activates after the code has failed. If you really need to predict if it will fail or not then you can just a dlookup or dcount on each of the related tables to find related records. If dlookup or dcount returns records you know the delete will fail. This could obviously be tedious to code if you have a ton of related tables to check.

[Robyn_P]

That sounds perfect, not sure why I didn't think of that myself. Thanks for the error number