I started over and created a simple db design and see how in the form if you try to delete a record w/RI established a msg box appears. When I take this logic back to this large form with many subforms, tabs & multiple queries it doesn't happen
MainTable: FN, LN, Address, etc
DepositTable: Date, type, Amount using MainID to lookup to Main Tabl
PatientHistory:...UnableToSpeak, DateOfDeath, etc
I've set RI so I can't delete a Main record that has deposit or patientHistory connected to it
But when I go in the central form (following is the SQL), it does allow me to delete the MainID record
At this point, I've made the form property as Deletions - NO, but I'm confused as to why it doesn't handle it as expected and what else I might have done to impact it
Thank you
SELECT [TBL Main Table].MainID, [TBL Main Table].TypeID, [TBL Main Table].Prefix, [TBL Main Table].LastName, [TBL Main Table].FirstName, [TBL Main Table].MiddleInt, [TBL Main Table].Suffix, [TBL Main Table].ProfDesig, [TBL Main Table].NickName, [TBL Main Table].Birthdate, [TBL Main Table].PriRelationshipID, [TBL Main Table].JobTitle, [TBL Main Table].CompanyName, [TBL Main Table].Address, [TBL Main Table].LocationID, [TBL Counties].CountyName, [TBL Main Table].PrimaryPhoneNumber, [TBL Main Table].PrimaryPhoneType, [TBL Main Table].FaxNumber, [TBL Main Table].AltPhoneNumber, [TBL Main Table].AltPhoneType, [TBL Main Table].EMail, [TBL Main Table].SecContactID, [TBL Main Table].Addressee, [TBL Main Table].AckSalutation, [TBL Main Table].PatientID, [TBL Main Table].RecordStatus, [TBL Main Table].InactiveDate, [TBL Main Table].InactiveNotes, [TBL Main Table].BusAddress, [TBL Main Table].BusLocationID, [TBL Main Table].BusPhone, [TBL Main Table].BusExt, [TBL Main Table].BusFax, [TBL Main Table].BusEmail, [TBL Main Table].PreferredMailing, [TBL Main Table].ReferralFromID, [TBL Location_1].State, [TBL Location].State, [TBL Location].RegionID, [TBL Location].CountyID, [TBL SecContacts].SecContactFN, [TBL SecContacts].SecContactRelationshipID, [TBL SecContacts].SecContactMI, [TBL SecContacts].SecContactLN, [TBL SecContacts].SecContactPhone, [TBL SecContacts].SecPhoneType, [TBL SecContacts].SecFaxNumber, [TBL SecContacts].SecPrefix, [TBL SecContacts].SecContactFN, [TBL SecContacts].SecContactLN, [TBL SecContacts].SecProfDesig, [TBL SecContacts].SecSuffix, [TBL SecContacts].SecNickName, [TBL Main Table].DateRecordEntered, [TBL Main Table].DateRecordModified, [TBL Main Table].ReceiveNewsletter, [TBL Main Table].MiscNotes, [TBL Main Table].PreferredVendor, [TBL Main Table].BusMatchGift, [TBL Main Table].BusCoDist, [TBL Main Table].BusAZHomeOffice, [TBL SecContacts].SecContactCoMatchGift, [TBL SecContacts].SecContactCoDist, [TBL SecContacts].SecContactCoAZHomeOffice, [TBL Region].Region, [TBL Main Table].BusOtherContact, [TBL Main Table].RemoveFromMailingList, [TBL Main Table].DonorDevID, [TBL SecContacts].SecPrefMailing, [TBL Main Table].BusGivingCyclePref, [TBL SecContacts].SecConGiftCyclePref, [TBL Main Table].SpecialHandling, [TBL Main Table].BusWebSite, [TBL Main Table].NextOfKinID, [TBL Main Table].NextOfKinPriOrSec, [TBL Main Table].NextOfKinRelationship, [TBL NextOfKin].Address, IIf([Tbl Main Table]![NextOfKinPriOrSec]=1 And [Tbl NextOfKin]![PreferredMailing]="Business",[TBL NextOfKin]![BusAddress],IIf([TBL Main Table]![NextOfKinPriOrSec]=2 And [Tbl NOKSecContact]![secPrefMailing]="Business",[TBL NOKSecContact]![SecContactBusAddress],[TBL NextOfKin]![Address])) AS PrefAddress, IIf([Tbl Main Table]![NextOfKinPriOrSec]=1 And [Tbl NextOfKin]![PreferredMailing]="Business",[NOKBusLoc]![City] & " " & [NOKBusLoc]![State] & " " & [NOKBusLoc]![Zip],IIf([TBL Main Table]![NextOfKinPriOrSec]=2 And [Tbl NOKSecContact]![secPrefMailing]="Business",[NOKSecBusLoc]![City] & " " & [NOKSecBusLoc]![State] & " " & [NOKSecBusLoc]![Zip],[TBL NOKLocation]![City] & ", " & [TBL NOKLocation]![STate] & " " & [TBL NOKLocation]![Zip])) AS NextOfKinLocation, [TBL NextOfKin].PrimaryPhoneNumber, [Tbl NOKSecContact]!SecContactFN & " " & [TBL NOKSEcContact]!SecContactLN AS NOKSecContact, [TBL Main Table].pALSContactRelationship, [TBL Main Table].pALSContact, [TBL Main Table].SupportGroupInterest, [TBL PatientHistory].DateOfDeath, [TBL Main Table].RecordSource, [TBL Main Table].TestRecord, [TBL Main Table].Verified, [TBL Main Table].ALSConnectionID, [TBL Main Table].StartDate, [TBL Main Table].EndDate, [TBL Main Table].EmpStatusID, [TBL Main Table].EventNotes, [TBL Main Table].Caregiver, [TBL Main Table].CGPriSec, [TBL Main Table].CGStatus, [TBL PatientHistory].PatientUnableToSpea
FROM (([TBL Counties] RIGHT JOIN (((([TBL SecContacts] AS [TBL NOKSecContact] RIGHT JOIN [TBL Main Table] AS [TBL NextOfKin] ON [TBL NOKSecContact].SecContactID = [TBL NextOfKin].SecContactID) LEFT JOIN [TBL Location] AS [TBL NOKLocation] ON [TBL NextOfKin].LocationID = [TBL NOKLocation].LocationID) RIGHT JOIN ([TBL Region] RIGHT JOIN ([TBL Location] AS [TBL Location_1] RIGHT JOIN ([TBL SecContacts] RIGHT JOIN ([TBL Location] RIGHT JOIN [TBL Main Table] ON [TBL Location].LocationID = [TBL Main Table].LocationID) ON [TBL SecContacts].SecContactID = [TBL Main Table].SecContactID) ON [TBL Location_1].LocationID = [TBL Main Table].BusLocationID) ON [TBL Region].RegionID = [TBL Location].RegionID) ON [TBL NextOfKin].MainID = [TBL Main Table].NextOfKinID) LEFT JOIN [TBL PatientHistory] ON [TBL Main Table].MainID = [TBL PatientHistory].MainID) ON [TBL Counties].CountyID = [TBL Location].CountyID) LEFT JOIN [TBL Location] AS NOKSecBusLoc ON [TBL NOKSecContact].LocationID = NOKSecBusLoc.LocationID) LEFT JOIN [TBL Location] AS NOKBusLoc ON [TBL NextOfKin].BusLocationID = NOKBusLoc.LocationID
WHERE ((([TBL Main Table].TestRecord)=False))
ORDER BY [TBL Main Table].LastName, [TBL Main Table].FirstName, [TBL Main Table].CompanyName;
