Taking Control of Delete 2

[Cads]

Is it possible to access the info available to a default confirmation message that appear in Access?

To explain, with the specific instance I'm considering, how can I produce my own version of the message "Relationships that specify cascading deletes...." "Are you sure....".

What I want to do is identify the records in a related table that will deleted when the specific record in this table is deleted and then give the user the opportunity of rescinding their decision. The records being directly deleted are in a subform so it's not a question of running or not running a VBA line of code instructing the delete.

I realise that I'll need to put some code into the OnDelete event but am not sure how I prevent the deletion from happening when in that procedure. Thanks

Steve House

http://www.icaew.co.uk

 

[tomschafer]

You just set the cancel parameter equal to true....


Private Sub Form_Delete(Cancel As Integer)

If MsgBox("Do You Really Want To Delete?", vbOKCancel) = vbCancel Then
Cancel = True
End If

End Sub

 

[p27br]

If I understand well, you want to check in the table on the "many" side of a one-to-many relationship for any related records to the record you want to delete in the table on the "one" side of the relationship.
I will suppose you have a textbox on the main form called CustomerID. In the subform you have (one or several)Orders related to the CustomerId. The orders have a foreign key called CustRef.
Here's how I would implement :

Set Rst = Dbs.Openrecordset("Orders",dbOpenrecordset)
Criteria = "CustRef = " & CustomerId
Rst.Findfirst Criteria
if not Rst.NoMatch then
msgbox "Are you sure you want to delete this customer and all his orders ?"
Docmd.RunSql ("Delete * from Customers where CustomerId = " & CustomerId )