Solved Rolling back added records - new orders

[debsamguru]

I call my NewOrderF with an acFormAdd to add a new order. This form has a subform NewOrderDetailsSubF. When I open the NewOrderF form, it creates a record on the OrderHeaderT and even if I close the form without adding any items in the subform, the record remains. I have tried putting an acCmdUndo in the Before Update condition of NewOrderF but it never seems to get to that point.

What I really want to do is put a Save button and roll back any updates on the form and subform if it isn't clicked.

What is the best way to do that?

 

[The_Doc_Man]

Because you used acFormAdd, there is a record there whether you needed it or not. Perhaps in the form's BeforeUpdate routine, you can determine whether any child records exist via DCount. If some records exist, do nothing. If no child records exist, you can perhaps try a Me.Undo on the form, which should remove the new record.

 

[debsamguru]

If there are child records, how would I undo those?

I have tried opening the form without the AcFormAdd and it still added the record.

 

[debsamguru]

Thanks. I have done this by deleting the header records if there are no line items. Thanks for your help.

 

[debsamguru]

Sure. So I actually had two events - one if they hit the Save button and there were no lines is the order and a Cancel button.

The Save event is:
Private Sub SaveBtn_Click()
Dim POIDInput As Variant
Dim OrderLinesCount As Integer
Dim Response As Integer
Dim query As String
Dim db As DAO.Database

Set db = CurrentDb

POIDInput = Me.ID
OrderLinesCount = DCount("*", "OrderItemsT", "[POID] = " & POIDInput)

If OrderLinesCount = 0 Then
Response = MsgBox("No Order Lines added. Do you want to cancel this Order?", vbYesNo, "Cancel Order")
If Response = vbYes Then
query = "DELETE * FROM OrderHeaderT WHERE ID = " & POIDInput
db.Execute (query), dbFailOnError
MsgBox "Order Cancelled", , "Cancel Order"
Set db = Nothing
DoCmd.Close
End If
End If

End Sub

And the Cancel button is:
Private Sub CancelBtn_Click()
Dim POIDInput As Variant
Dim OrderLinesCount As Integer
Dim Response As Integer
Dim query As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

POIDInput = Me.ID

Response = MsgBox("This will delete the whole Order. Do you want to cancel this Order?", vbYesNo, "Cancel Order")
If Response = vbYes Then
query = "SELECT * FROM OrderItemsT WHERE POID = " & POIDInput

Set rs = CurrentDb.OpenRecordset(query)

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
While (Not rs.EOF)
rs.Delete
rs.MoveNext
Wend
End If

rs.Close

Set rs = Nothing

query = "DELETE * FROM OrderHeaderT WHERE ID = " & POIDInput
db.Execute (query), dbFailOnError
MsgBox "Order Cancelled", , "Cancel Order"
Set db = Nothing
DoCmd.Close
End If

End Sub

 

[debsamguru]

Will do - what are code tags?

I realise that I have to make sure about closing the form.