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@outlook.com [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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
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