Delete and requery (Access 2000)

Topic

New Reply

Order does not disappear

I have a list boc called LstStockreceipt. When i click the order, the order is deleted indeed, but it stays on the list and does not disappear.I have to close the form,and
open it again, and then the order is not seen .Is it possible to make the deleted order disappear immediately ?

Dim strSQL As String
Dim frm As Form
Set frm = Forms![Frep]
‘*******************************************
‘2. delete the order
DoCmd.SetWarnings False
strSQL = “DELETE * FROM Orders WHERE orderID = ” & frm!LstStockReceipt & “;”
DoCmd.RunSQL strSQL
strSQL = “DELETE * FROM [order details] WHERE orderID = ” & frm!LstStockReceipt & “;”
DoCmd.RunSQL strSQL
Application.Echo True
DoCmd.SetWarnings True
frm.requery
frm![LstStockReceipt].Requery
frm![current].Requery

Reply | Quote

Replies

(Edited by D Willett on 01-Sep-03 10:30. )

Dim strSQL As String
Dim frm As Form
Set frm = Forms![Frep]
‘*******************************************
‘2. delete the order
DoCmd.SetWarnings False
strSQL = “DELETE * FROM Orders WHERE orderID = ” & frm!LstStockReceipt & “;”
DoCmd.RunSQL strSQL
strSQL = “DELETE * FROM [order details] WHERE orderID = ” & frm!LstStockReceipt & “;”
DoCmd.RunSQL strSQL
Application.Echo True
DoCmd.SetWarnings True
frm.requery
Me.LstStockReceipt.Requery

This may solve your problem.
Try it without the Setwarnings to see what happens, you can always set them afterwards when you’re haapy with the result.
Perhaps the two instances of “DoCmd.RunSQL strSQL” are conflicting.

You could try:

Dim strSQL As String
Dim frm As Form
Set frm = Forms![Frep]
‘*******************************************
‘2. delete the order
DoCmd.SetWarnings False
strSQL = “DELETE * FROM Orders WHERE orderID = ” & frm!LstStockReceipt & “;”
strSQL2 = “DELETE * FROM [order details] WHERE orderID = ” & frm!LstStockReceipt & “;”
DoCmd.RunSQL strSQL
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunSQL strSQL2
DoCmd.RunCommand acCmdSaveRecord
Application.Echo True
DoCmd.SetWarnings True
frm.requery
Me.LstStockReceipt.Requery

Reply | Quote

(Edited by D Willett on 01-Sep-03 10:30. )

Dim strSQL As String
Dim frm As Form
Set frm = Forms![Frep]
‘*******************************************
‘2. delete the order
DoCmd.SetWarnings False
strSQL = “DELETE * FROM Orders WHERE orderID = ” & frm!LstStockReceipt & “;”
DoCmd.RunSQL strSQL
strSQL = “DELETE * FROM [order details] WHERE orderID = ” & frm!LstStockReceipt & “;”
DoCmd.RunSQL strSQL
Application.Echo True
DoCmd.SetWarnings True
frm.requery
Me.LstStockReceipt.Requery

This may solve your problem.
Try it without the Setwarnings to see what happens, you can always set them afterwards when you’re haapy with the result.
Perhaps the two instances of “DoCmd.RunSQL strSQL” are conflicting.

You could try:

Dim strSQL As String
Dim frm As Form
Set frm = Forms![Frep]
‘*******************************************
‘2. delete the order
DoCmd.SetWarnings False
strSQL = “DELETE * FROM Orders WHERE orderID = ” & frm!LstStockReceipt & “;”
strSQL2 = “DELETE * FROM [order details] WHERE orderID = ” & frm!LstStockReceipt & “;”
DoCmd.RunSQL strSQL
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunSQL strSQL2
DoCmd.RunCommand acCmdSaveRecord
Application.Echo True
DoCmd.SetWarnings True
frm.requery
Me.LstStockReceipt.Requery

Reply | Quote

If you have referential integrity set for the relationship between Orders and Order Details (and you should, otherwise you run the risk of inconsistencies), there are two possiblities:

• You have set cascading deletes for the relationship. In that case, deleting a record from Orders will automatically delete related records from Order Details, so the second DELETE is superfluous.
• You have not set cascading deletes. In that case, the first DELETE will fail because there are related records in Order Details. You should delete the records from Order Details first and then the records from Orders.
  [/list]I don’t know if this will solve the problem, but you should get this right first.

Reply | Quote

This is exactly so, as you have written !!! I have followed your advice and i solved the problem !

Thank you !!!!!!!!

Reply | Quote

This is exactly so, as you have written !!! I have followed your advice and i solved the problem !

Thank you !!!!!!!!

Reply | Quote

If you have referential integrity set for the relationship between Orders and Order Details (and you should, otherwise you run the risk of inconsistencies), there are two possiblities:

• You have set cascading deletes for the relationship. In that case, deleting a record from Orders will automatically delete related records from Order Details, so the second DELETE is superfluous.
• You have not set cascading deletes. In that case, the first DELETE will fail because there are related records in Order Details. You should delete the records from Order Details first and then the records from Orders.
  [/list]I don’t know if this will solve the problem, but you should get this right first.

Reply | Quote