How to cancel On Delete Event 1

[noorani]

i have writen this code for On Delete event of my form, which is a child continous form, and this code suppose to delete the record when the del key is pressed.

well problem is when i press " NO " at the moment when the message appears and ask the confirmation for deletion then this code don't stop it still delte the record , how can i solve it , i tried using " cancel = -1 " before the exit sub, And every thing worked out but i found that my other buttons stop functioning. and take lone time to execute and do nothing. just hanged.

,one more thing i have used set warnning to true and false in the before and after deletion event. to avoid displaying the system messages.

here is the code.


On Delete Event
==============

Dim title As String
Dim dbs As Database
Dim D As Recordset
Dim Z As Recordset
title = "BackOffice 1.0.3"

Set dbs = CurrentDb

' here is the problem, when i choose " No " in this message then this event should stop and exit from this sub . but it goes further and delete the record.


If MsgBox("Record will be deleted. Continue(y/n)?", vbYesNo, "BackOffice&quot = vbNo Then Exit Sub



Set D = dbs.OpenRecordset("select * from Invoice_main where Invoice_ref=" & Parent!OrderID, dbOpenDynaset)

If D.RecordCount > 0 Then

MsgBox ("There is a record found in Invoice table. Deletion is not possible",vbCritical, title

D.Close
dbs.Close
GoTo err_prih
End If


' checking the records in the reserve table.
i have set this record set to compare the records in Reserve table, if it found then it will be deleted from the Reserve table first then should be delted from my child form, which is bound to another table.


Set Z = dbs.OpenRecordset("select * from Reserve where (OrderID=" & Parent!OrderID & &quot AND (pronumber=" & Me.productid.Column(0) & &quot", dbOpenDynaset)

If Z.RecordCount <= 0 Then

MsgBox (&quot;No record found in Reserve.&quot, vbCritical, title

' here record should be delted from the Reserve table then after it should be deleted from my child from.

Else
Z.delete
Z.Close
dbs.Close

End If

MsgBox (&quot;Record have been deleted&quot, vbExclamation, title

Exit Sub
err_prih:
Exit Sub

End sub



After Del Confirm
===============

Private Sub Form_AfterDelConfirm(status As Integer)
DoCmd.SetWarnings True
End Sub




Before Del Confirm
================

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
DoCmd.SetWarnings False
End Sub


Please correct me what i'm doing wrong.

thank in advance.

 

[SBendBuckeye]

In Form_BeforeDelConfirm code do the following:

Set Cancel = True

Good LucK!

 

[noorani]

Thanks for your reply,

i have set cancel = true in the form_beforeConfirm event. and now it can not delete atall even if i press Yes to delete. one good thing that it stop deleting the recrods when i press No, but very strange that it's not deleting even if i choose yes. may be i have to do some more work with above mentioned evetns. please any idea?

 

[noorani]

any other suggestion, please give me some advise. the previous solution didn't worked out.

Thank in advance.

 

[danvlas]

The Delete event occurs for each record individually.

The BeforeDelConfirm event occurs after all selected records records have been deleted and placed in the 'undo' buffer. You need to set Cancel to True or false on the basis of a variable passed from the Delete event.


When you select more than one record to delete, you have the following 3 options when an error occurs:

1. Cancel deletion of that particular record and continue deleting the remaining records. this is done through the Cancel argument (Cancel = True)

2. Cancel deletion of that record and further deletion. In this case you need a static variable in the general section of form's procedure:
Static CancelFurther As Boolean

In the Form_Delete procedure, inspect the value of CancelFurther:

If CancelFurther Then
Cancel = True
Exit Sub

'code to detect the 'normal cancellation'
If Condition then
Cancel = True
'set the static variable to True
CancelFurther=True
End If

3. Cancel entire deletion batch.
It does not matter whether you Cancel or not the deletion within the OnDelete procedure. Set Cancel to True in the BeforeDelConfirm procedure, but you need a variable declared in the General Declarations section of your form's module:
Dim CancelAll As Boolean
and set its value to True at the same time with CancelFurther described above.

In the AfterDelConfirm, reset the variables CancelAll and CancelFurther to False

Good luck


Daniel Vlas
Systems Consultant

 

[noorani]

Thank you friends, i have solved my problem by doing the following.

i have declear static variable name as zeehold as Boolean

and in my before delete event i have set the cancel value to zeehold.

like Cancel = zeehold

then in my on Delete event i perfomd the following steps.

when the user get the Deletion confirmation promt.

Case 1

in case of choosing No , code jump to the Errr Label i have defiend at the end of my code, and setting the value of zeehold = True

Case 2

in case of choosing Yes i'm setting the value of

Zeehold = False. right after the prompted message.


and every thing worked out.



thank again for your help..