Solved Rolling back added records - new orders (1 Viewer)

[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.

 

[Pat Hartman]

Once focus moves from the main form to the subform, the main form record is saved. Once that happens, "roll back" is no longer an option. your only option is to delete the main form record.

There are two ways to ensure that at least one detail item is added for an order. The easy way and the hard way.

The easy way is to use dcount() to determine if there are any child records when the main form closes. If there are no child records, give the user the chance to discard the mainform record by deleting it. This isn't a surefire method though since if the user says to keep the record, and you stop the form from closing, he's back where he can enter a detail item but it will eventually be possible to close the form without actually adding a detail record.

A slight addition to the above makes the process better. You add a CompleteYN flag to the main form. That way you can use that field to ignore orders that are not complete. You can run queries each week to identify incomplete orders and bother the people who created them to complete or delete.

The hard way is to create a set of shadow tables and build the order there. Then when the order is complete, the user pushes a button and you move the order and the details inside a transaction so that both recordsets get updated or neither does. The downside, and there are several is that you end up with separate code for add than for update. You can organize the code so that you call common procedures rather than write the validation code twice. Then you have to decide whether you want to even allow updates in the main tables or do you want to shuffle the data back to the shadow tables for maintenance or edit them in the main tables.

Using the shadow table method, you still have to run queries every week to find partially complete orders and do something with them.

The easiest way of all is to ignore orders with no detail items when you are doing other processes.

 

[debsamguru]

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

 

[Pat Hartman]

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

You're welcome, for the sake of others finding this thread later, please post the event (including headers) where you put the code. Thanks

 

[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

 

[Pat Hartman]

Thanks. Please use the code tags when posting code to retain indentation making the code easier to read.

What happens if the user just closes the form without pressing save or cancel?
Do you make any attempt later to find orders with no line items and ask again to delete them?

 

[debsamguru]

Will do - what are code tags?

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

 

[Pat Hartman]

use the </> option on the ribbon of the text box you put your questions in. It opens a code window. Just paste your code in there instead of directly in the text window.