Delete reocrd in subform and show own message inspite of system msg.

[noorani]

Hi,

I want to delete records in my subform "which is bound to a table name as OrderDetails" using the Del key.
i have writen code for on delete event for my sub form where my code will check the product code and orderID in the table name as as "Reserved", if record found then, it will be deleted form both tables ("reserve table & OrderDetails table&quot, else record will only be delete from the Ordertable. this code is running fine but the problem with the system messages, i have generated my own message but after my message system message occurs , and user have to choose the same thing twice. how i can handle this situation..

Here is the code.

Private Sub Form_Delete(Cancel As Integer)
Dim title As String
Dim dbs As Database
Dim wsp As Workspace
Dim Z As Recordset
Dim in_tr As Boolean
Dim in_dbs As Boolean
title = "BackOffice"
in_tr = False
in_dbs = False
Me.Refresh
If MsgBox("Record will be deleted. Do you want to delete?", vbYesNo, title) = vbNo Then Exit Sub

Set dbs = CurrentDb
in_dbs = True
Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans
in_tr = True

'looking for the same product code and Order number in the reserve table , which have been reserved earlier
.

Set Z = dbs.OpenRecordset("select * from Reserve where (OrderID=" & Parent!OrderID & &quotand(pronumber=" & Me.productid.Column(0) & &quot", dbOpenDynaset)
If Z.RecordCount <= 0 Then
MsgBox (&quot;No record found in Reserve.&quot, vbCritical, title
GoTo err_prih
End If

Z.delete

wsp.CommitTrans
in_tr = False
Z.Close
dbs.Close
in_dbs = False
wsp.Close
MsgBox (&quot;Record have been deleted &quot, vbExclamation, title

Exit Sub

err_prih:
If in_tr = True Then
wsp.Rollback
dbs.Close
in_dbs = False
wsp.Close
in_tr = False
End If
If in_dbs = True Then
dbs.Close
in_dbs = False
End If
End Sub


another thing i wanted to ask what will happen if the user will choose the multiple record for deletion at a time using the combination keys like shift and arrow and then if press the Del key.


Thanks in Advance..

 

[BillPower]

Use:

Docmd.Setwarnings False

Before deletion then

Docmd.Setwarnings True

After deletion.

 

[noorani]

Thanks it worked perfectly.