You can suppress the display of the warning in Form_BeforeDelConfirm with:
Response = acDataErrContinue
Presumably ReleaseTerritories is an action query that updates DealerID to
Null, so you can delete the dealer and have the territories unassigned? If
so, there may be an even better solution.
JET 4 supports Cascade-to-Null, so it is possible to get Access to do this
for you in Access 2000 or later. The benefit is that it happens at the
engine level - independent of the form events - so works regardless of how
the dealer is deleted.
Unfortunately the interface cannot create nor distinguish this kind of
cascade. ADOX code is the only way. Something like this:
Sub CreateKeyAdox()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim ky As New ADOX.Key
Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("tblTerritory")
With ky
.Type = adKeyForeign
.Name = "tblDealertblTerritory"
.RelatedTable = "tblDealer"
.Columns.Append "DealerID"
.Columns("DealerID").RelatedColumn = "DealerID"
.DeleteRule = adRISetNull 'Cascade to Null on delete.
End With
tbl.Keys.Append ky
Set ky = Nothing
Set tbl = Nothing
Set cat = Nothing
Debug.Print "Key created."
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dee" <wo********@comcast.net> wrote in message
news:35**************************@posting.google.c om...
I have a form displaying dealer information.
It contains a subform which displays territories which they are
assigned to this dealer's DealerId number in datasheet view.
I have "delete" disabled on the main form in order to preserve
cascading referential integrity.
I would like to allow deleting the dealer's record, but only if no
territories are assigned to him. This would then preserve referential
integrity.
Using the "before delete" event procedure, I'd like to to display a
message box which asks "This dealer has territories, which will revert
to "unassigned" in the territories table if you proceed!" "Proceed?"
If the answer is "yes" it will run a simple "ReleaseTerritories" query
which I already have.
If the answer is "no" , I simply won't run the "ReleaseTerritories"
query but...
Now comes the tricky part!
I also wish to pass this "No Answer" on to the the "before delete"
event procedure so that it aborts without displaying its yes or no
message query.
Can't seem to get that to work. Anyone have any ideas?
