Discontiniue delete-On Delete Event ...Before Delete Confirmation

[Zirak]

Hi,
Before I delete a record on from a form, I want to check and see if that ID exists in another table, If so I want to discouninue the delete procedure, here is my code:
(I'm using a SQL server backend)

Dim CSet As ADODB.Recordset
Dim strSQL

strSQL = "Exec dbo.SP_FindCompanyOnDelete " & Me!CompanyID

Set CSet = New ADODB.Recordset
CSet.ActiveConnection = CurrentProject.Connection
CSet.Open strSQL
If CSet.EOF Then
CSet.Close
Exit Sub
Else
CSet.Close
MsgBox "There are Contacts assigned to this company." _
& Chr(13) & "Delete the company from those contacts first and then delete the Company.", vbCritical, "Delete Responses First"
End If

 

[AskMan]

And ?
I don't understand your problem ...
If you look for the good event, you should put your code into the BeforeDelConfirm event


Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

Dim CSet As ADODB.Recordset
Dim strSQL

strSQL = "Exec dbo.SP_FindCompanyOnDelete " & Me!CompanyID

Set CSet = New ADODB.Recordset
CSet.ActiveConnection = CurrentProject.Connection
CSet.Open strSQL

If not(CSet.EOF) Then
MsgBox "There are Contacts assigned to this company." _
& Chr(13) & "Delete the company from those contacts first and then delete the Company.", vbCritical, "Delete Responses First"
cancel = true 'to cancel deleting
End If

CSet.Close
set Cset = nothing

End Sub

Hope that helps

SEB

 

[Zirak]

I got it.
i used the cancel event

Thanks