Senin, 27 Maret 2017

Re: [MS_AccessPros] Error 3396

 

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 (3)

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