Selasa, 28 Maret 2017

Re: [MS_AccessPros] Error 3396

 

Yes, the ELSE is:

Case Else
            ' Undefined error - log and let error display
            ErrorLog Me.Name & "_Error", DataErr, AccessError(DataErr)
            Response = acDataErrDisplay

What I see is the system error message 3200 instead and the code stops.
 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

Graham's extra info helps, but it doesn't explain why you're not seeing anything trapped in your error trap code.  You should be at least getting the generic message the code generates if it gets no match on the code.  You still have the Else MsgBox code in there, right?

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Mar 28, 2017, at 05:05, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Graham, thank you for that explanation as it explains why I am now getting Error 3200 where I was getting 3396.  The reason is because I had gone back to my tables and noticed the inconsistency just as you described, and changed those tables such that Cascade Delete was not allowed on any of them.  I did not realize that is why my error number changed, but thanks to your explanation I now understand why.

I will have to review all of my tables against your advice, but I think earlier today I pretty much did just what you suggested.  I know I made all of my many to many tables cascade delete.  I made most other relationships to not allow cascade delete. 

However, I know I left my transaction type tables cascade delete.  These are tables where I have something like a report header table and a report details table.  I made these cascade delete.  In your work would you not do that?




---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote :

Hi David

I believe that error 3396 occurs only in the following unusual situation:

TableA is related to TableB with cascade deletes
TableB is related to TableC WITHOUT cascade deletes
You attempt to delete a record from TableA, where related child AND grandchild records exist in TableB and TableC respectively.

Normally, if it is appropriate to have cascading deletes from parent to child, then it would also be appropriate between child and grandchild.

 

Personally, I commonly use cascading deletes on junction tables which usually contain no data other than the representation of a many-to-many relationship.  Otherwise I would use them very rarely, and never (that I can think of) in the three-table scenario described above.

 

Best wishes,

Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 28 March 2017 12:20
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3396

 

 

Yes, I am only seeing only the system message.  I am getting one of two errors - either 3200 or 3396.  I have attempted to trap both of them in the same manner you did with the other errors, but to no avail.  I added both to the globals module and added both to the CASE statement in the FORM ERROR and in the SAVE function.

 

Neither the 3200 nor the 3396 errors are being trapped.  I set error 3396 to errCascadeDelete and error 3200 to errCascadeDelete2.  I added them to the CASE statement as with your other cases.

 

Case errCascadeDelete 'attempting to delete a Parent record with children where cascade delete is not allowed
            MsgBox "You are attempting to delete a record which has related 'child' records.  " & _
                "You must first delete each 'child' record before this record may be deleted.", vbCritical, gstrAppTitle
                Response = acDataErrContinue
        Case errCascadeDelete2 'attempting to delete a Parent record with children where cascade delete is not allowed
            MsgBox "You are attempting to delete a record which has related 'child' records.  " & _
                "You must first delete each 'child' record before this record may be deleted.", vbCritical, gstrAppTitle
                Response = acDataErrContinue

 

The error trapping code works fine for the case of duplicate record errors, so I think the sub and function are in general correct.



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

 

Form_Error or error trapping code in your Save code should see that error.  Are you only seeing the system message?  Maybe it's being triggered with a different error code.

 

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

(Paris, France)

 

 

 

On Mar 27, 2017, at 6:35 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 



Thanks John, I will take the time to understand your code and use it as I am sure you have good reason to do it this way. I also never use cascade update because all my primary keys are autonumber.  However, I did think I should have some of my tables set for cascade delete.  You chose to never to allow that?  I can see where that is the safest choice, but sometimes would be very inconvenient.

 

I did try to add the error 3396 error handling to your form error code and to your error function code.  My custom message and error handling did not appear though.  Is this because this is a class of error which would not be caught by either of these two methods?



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

 

I generally disallow Cascade Update and Cascade Delete.  I avoid that error in the Contacts app because a) all Primary Keys are AutoNumber, so cascade update will never happen, and b) my code checks before allowing a delete and issues a custom message if you would get a cascade error.  For example, here's the code in frmCompanies:

 

Private Sub Form_Delete(Cancel As Integer)

Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset

Dim varRelate As Variant

    ' Check for related child rows

    ' Get a pointer to this database

    Set db = CurrentDb

    ' Open the test query

    Set qd = db.QueryDefs("qryCheckRelateCompany")

    ' Set the company parameter

    qd!CompanyNo = Me.CompanyID

    ' Open a recordset on the related rows

    Set rst = qd.OpenRecordset()

    ' If we got rows, then can't delete

    If Not rst.EOF Then

        varRelate = Null

        ' Loop to build the informative error message

        rst.MoveFirst

        Do Until rst.EOF

            ' Grab all the table names

            varRelate = (varRelate + ", ") & rst!TableName

            rst.MoveNext

        Loop

        MsgBox "You cannot delete this Company because you have related rows in " & _

            varRelate & ".  Delete these records first, and then delete the Company.", _

            vbOKOnly + vbCritical, gstrAppTitle

        ' close all objects

        rst.Close

        qd.Close

        Set rst = Nothing

        Set qd = Nothing

        Set db = Nothing

        ' Cancel the delete

        Cancel = True

        Exit Sub

    End If

    ' No related rows - clean up objects

    rst.Close

    qd.Close

    Set rst = Nothing

    Set qd = Nothing

    Set db = Nothing

    ' No related rows, so OK to ask if they want to delete!

    If vbNo = MsgBox("Are you sure you want to delete Company " & Me.CompanyName & "?", _

        vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then

        Cancel = True

    End If

 

End Sub

 

qryCheckRelateCompany is:

 

PARAMETERS [CompanyNo] Long;

SELECT DISTINCT "Contacts" As TableName, CompanyID

FROM tblCompanyContacts

WHERE tblCompanyContacts.CompanyID = [CompanyNo]

UNION ALL SELECT DISTINCT "Invoices" As TableName, CompanyID

FROM tblInvoices

WHERE tblInvoices.CompanyID = [CompanyNo];

 

tblCompanyContacts and tblInvoices are the two tables that will not allow you to delete a company if related rows exist.

 

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

(Paris, France)

 

 

 

On Mar 27, 2017, at 3:05 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 



John,

I see that your error handling in CONTACTS  does not include handling error 3396, the cascade delete error.

 

How do you handle the cascade delete situation? I have some places where I set my table relations to allow cascade delete and some places where my table relations do not include cascade delete.  Do you make cascade delete extra hard to do?  Two warning messages or something of that nature?



 



 

__._,_.___

Posted by: david.pratt@outlook.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Posting Komentar