can't trap the "cascading deletes" error

[MattPGA]

I'm using Access 2010 and I can't trap the "cascading deletes" message in the OnError event procedure for a form. I'm using error code 8530 but the OnError event doesn't seem to trigger. Any ideas?

[June7]

Exactly what is the message? Since I don't set relationships for cascade delete, not sure what you are encountering. Possibly this message is a normal procedural warning and is not due to an error.

Deleting records really should be a rare occurrence in a conventional database. Why are you deleting records?

[MattPGA]

Exactly what is the message? Since I don't set relationships for cascade delete, not sure what you are encountering. Possibly this message is a normal procedural warning and is not due to an error.

Deleting records really should be a rare occurrence in a conventional database. Why are you deleting records?

The message is "Relationships that specify cascading deletes are about to cause ____ records to be deleted..." It is a standard message but I have always been able to trap it in the OnError event. In this instance, if the parent record is deleted, I definitely want all of the child records to go away as well.

[June7]

Since I've never set up a database to perform this way, I am not sure. You have been able to trap it before so what is different? Have you step debugged? Post your code or db for analysis. Follow instructions at bottom of my post.

[orange]

There is some info on 8530 here. I've never experienced the error message.

[MattPGA]

There is some info on 8530 here. I've never experienced the error message.

This page made it sound like there is a serious problem with the database crashing. My database is working fine and this is a message that is commonly given when a parent record is about to be deleted that has child records associated. You both are experienced developers, I can tell, so I'm surprised that this is an unfamiliar message for you. Do you really not utilize the cascade delete feature? I use it selectively, but I definitely use it when it is appropriate (ie. if the parent record goes away, I don't want to save the child records of the one-to-many relationship). I'm just trying to understand, not criticize.

I have step debugged, and that is what is confusing. I can't figure out where to trap the message. I have error trapping set up in all of my modules so I know exactly where error or system messages pop up. This is definitely a system message that I have always been able to trap (I've created many databases) in the OnError event of a form module.

[orange]

Can you post a copy of the database -- just a few records and nothing confidential?
Is it really an error or just a notification similar to "You are about to update 4 records...."?

[MattPGA]

I attached the database. Open frmRounds and attempt to delete one of the rounds of golf. If a round is deleted all of the holes involved in the round should also be deleted. I have a personalized message describing what will happen if the round is deleted, but then access throws its two bits in with the cascading deletes warning. Any help would be appreciated. It is probably something simple, but I'm just not seeing it.

[orange]

I think it is just a message.

When I added these SetWarning lines, there was no message.

Private Sub cmdDeleteRound_Click()
On Error GoTo HandleError

DoCmd.SetWarnings False  'Turn warnings OFF

    DoCmd.RunCommand acCmdDeleteRecord
    
DoCmd.SetWarnings True   'Turn warnings ON
ExitHere:
    Exit Sub
HandleError:
    Select Case Err.Number
        Case 2501 'delete canceled
            Resume ExitHere
        Case Else
            dhError ("cmdDeleteRound_Click")
            Resume ExitHere
    End Select
    
End Sub

However, I saw that the code entered the detail_Paint several times, and that Detail_Paint has no code.
I added a debug.print to fsubRounds to see the calls involved.

Private Sub Detail_Paint()
Debug.Print "called detail_paint"

End Sub

I also saw the Detail_Paint being called in frmRounds so added

Private Sub Detail_Paint()
Debug.Print "Call detail_paint"
End Sub

There were several calls

...
called detail_paint
called detail_paint
called detail_paint
called detail_paint
called detail_paint
called detail_paint
Call detail_paint
Call detail_paint
called detail_paint
called detail_paint
called detail_paint
called detail_paint
Call detail_paint
Call detail_paint
Call detail_paint
...

I hope this is useful.

PS: Some very nice code and comments.

[MattPGA]

Excellent! Thank you. I figured it was probably something simple. Thanks for the compliment about the code, though most of the standard modules I use (like the error trapping) are from a book by Ken Getz. I really appreciate the help!

[MattPGA]

Also, I think all of the calls to the detail_paint are because I'm using conditional formatting. Just a guess.

[June7]

I have never used nor seen form Error event. Might be helpful to know the code from dbs where you have done this successfully. Should code be in main form Error event or the subform? The only place you currently have reference to error 8530 is in subform Error event.

Should you prevent deletion by the keyboard Delete key and restrict users to the button?

I have one large db where users are not allowed to delete parent records. Once record is created (and there are checks in that process to make sure they really want to commit record) it cannot be deleted. I have many smaller less complex dbs and users either delete at their own discretion or deletion is prevented. I might have relationships and referential integrity/cascade delete set in some of them but no code to handle messages.

Don't think form needs to be repainted for Conditional Formatting to work.

EDIT: orange's solution with SetWarnings probably simplest way to deal with this message.

[MattPGA]

I tried the code in both the main form and the sub form (just deleted it while testing). I use the form Error event when I get messages that aren't trapped by the object modules (not very often). I hadn't thought about deletion by the Delete key. I'll have to experiment with it. Thanks again for your time and expertise!

[MattPGA]

One last thing, when I use the form Error event, I find the code number for the error (or message) from a list like this:
http://www.fmsinc.com/microsoftacces...ption2010.html.

[orange]

I think the Detail_Paint is executed only because it is present. There is no code of anything custom to your application in the event. Delete the code and it will not execute. That is the nature of an Event procedure--if you have it defined (whether is does anything or not) it will execute. An old trick is to select every event with a debug.print, run your code and then see the order of execution.

Untested with your db but I think you could also use the currentdb.execute sqlstring,dbfailonerror to bypass the warning message also.