Re: [MS_AccessPros] Error 3396

 

David-

The label has scope only within the local procedure, so yes, you can use a label like "DelErr" in multiple procedures.

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 

http://www.viescas.com/ 

(Paris, France)

On Mar 31, 2017, at 4:26 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John

Do statement labels have a scope of only the sub the label is contained within?   Can I use the same "DelErr" label in multiple subs within the same form  module? Or do I have to use unique labels?  Most examples I see have labels that include the sub name rather than the simpler label name.

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

David-

You need to add On Error - something like this:

Private Sub cmdDeleteCorporation_Click()

    On Error GoTo Del_Err
    If Me.lstCorporations.ItemsSelected.Count > 0 Then
        Dim strSQL As String
        Dim strMessageBoxMessage As String
        
        strMessageBoxMessage = "Are you sure you want to delete this Corporation?" & vbNewLine & _
                                "Before you can delete a Corporation, all records associated with the corporation must be deleted." & vbNewLine & _
                                "Permission to do this is limited to certain personnel."
        
        If MsgBox(strMessageBoxMessage, vbQuestion + vbYesNo, "Delete Lots of Data?") = vbYes Then
            strSQL = "DELETE FROM lu_tblCorporations WHERE CorporationID =" & lstCorporations.Column(0)
            CurrentDb.Execute strSQL, dbFailOnError
            Me.lstCorporations.Requery
            Me.lstLocations.Requery
            Me.lstSites.Requery
         End If
     Else
            MsgBox "You must choose a Corporation to delete." _
        , vbInformation, gstrAppTitle
    End If

Done:

    Exit Sub

Del_Err:

    ' Try to analyze the error

    Select Case Err

        ' Cancel, invalid property, or field not found (Setting Dirty = False)

        Case errCancel, errCancel2, errPropNotFound, errInvalidPropSetting, errCantFindField

            Resume Save_Exit

        Case errDuplicate  ' Duplicate row - custom error message

            MsgBox "You're trying to add a record that already exists.  " & _

                "Enter a new Customer Name or click Cancel.", vbCritical, gstrAppTitle

        Case errInvalid, errInputMask

            ' Invalid data - custom error and log

            MsgBox "You have entered an invalid value. ", vbCritical, gstrAppTitle

            ErrorLog Me.Name & "_Save", Err, Error

        ' Field validation, Table validation, Custom Validation, End of Search, Spelling Check

        Case errValidation, errTableValidate, errCustomValidate, errSearchEnd, errSpellCheck

            ' Display the error

            ' All validation rules in the tables have custom error messages.

            MsgBox Error, vbCritical, gstrAppTitle

        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

        Case Else

            ' Dunno - log and let error display

            ' Save the error code values because ErrorLog may get additional errors

            lngErr = Err

            strError = Error

            ErrorLog Me.Name & "_Delete", lngErr, strError

            MsgBox "Error attempting to delete: " & lngErr & " " & strError & Chr$(13) & Chr$(10) & "Try again or click Cancel to close without saving.", vbExclamation, gstrAppTitle

    End Select

    Resume Done

End Sub

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 

http://www.viescas.com/ 

(Paris, France)

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

I tried to reply via email but that seemingly didn't work.  If this shows up twice, I apologize.

You said, "It shouldn't do that if you have an error trap set".  That is probably the key, I doubt that I have the error trap set at all since I don't really know how to do that.  I am just realizing that I have added your Form_Error code to the form error event and I added your SAVE function code (which I called Function SaveRecord(). In my cmdSaveAndClose sub I call the SaveRecord function but in my cmdDelete buttons I have done nothing to call SaveRecord().  Instead, I have 

        CurrentDb.execute strSQL, dbFailOnError

It is the dbFail on error that is throwing the system error code.

How do I correct this so that it calls your SaveIt function and provides my custom error message?

I have the following code in the cmdDelete sub:

Private Sub cmdDeleteCorporation_Click()
    If Me.lstCorporations.ItemsSelected.Count > 0 Then
        Dim strSQL As String
        Dim strMessageBoxMessage As String
        
        strMessageBoxMessage = "Are you sure you want to delete this Corporation?" & vbNewLine & _
                                "Before you can delete a Corporation, all records associated with the corporation must be deleted." & vbNewLine & _
                                "Permission to do this is limited to certain personnel."
        
        If MsgBox(strMessageBoxMessage, vbQuestion + vbYesNo, "Delete Lots of Data?") = vbYes Then
            strSQL = "DELETE FROM lu_tblCorporations WHERE CorporationID =" & lstCorporations.Column(0)
            CurrentDb.Execute strSQL, dbFailOnError
            Me.lstCorporations.Requery
            Me.lstLocations.Requery
            Me.lstSites.Requery
         End If
     Else
            MsgBox "You must choose a Corporation to delete." _
        , vbInformation, gstrAppTitle
    End If
End Sub

 

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

David-

Where does the code stop?  It shouldn't do that if you have an error trap set.  Have you looked for entries in the ErrorLog table?

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 

http://www.viescas.com/ 

(Paris, France)

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

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

http://www.viescas.com/

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 

http://www.viescas.com/ 

(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

    rs

(Message over 64 KB, truncated) 

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (17)

---

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.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___