MS Access record automatically saved when click into subform-CodePudding

I have a typical Order data entry form/subform setup in MS Access / SQL Server 2019. There are 2 buttons within the form: Save / Cancel.

This is the code for the buttons:

Private Sub cmdSave_Click()

    On Error Resume Next
    DoCmd.Close acForm, "frmDEPurchaseOrder", acSaveYes
    RefreshForm ("frmPurchaseOrderDetails")
    RequeryForm ("frmPurchaseOrderList")

End Sub

Private Sub cmdCancel_Click()

    On Error Resume Next
    If MsgBox("Změny budou zrušeny. Chcete pokračovat?", vbYesNoCancel   vbExclamation, "Upozornění") <> vbYes Then Exit Sub
    Me.Undo
    DoCmd.Close acForm, "frmDEPurchaseOrder", acSaveYes
    RefreshForm ("frmPurchaseOrderDetails")
    RequeryForm ("frmPurchaseOrderList")

End Sub

"Save" button works as expected, however when I'm adding a new Order, fill the basic order information and then click into the subform to add items, a record for the order is created at this moment in time.

The problem is that when I then click on "Cancel", the order is still saved in SQL Server 2019.

Is there any simple way to not save / delete the record if I click cancel at any time during this process?

CodePudding user response：

On the Subform GetFoucs event you can use the following code

Sub YourSubForm_GetFoucs()
   Me.Parent.Refresh
End Sub

CodePudding user response：

As long as you are working with bound forms a record will be created as soon as your input focus moves from that record to something else. You have two options. The clean one is to create new orders on separate tables and only transfer them to the main tables on "Save". The easier but dirtier one is to clean up by deleting any created records on "Cancel". Either way you are going to have to manage the records with your code, you will not be able to get away with just a parameter on how you close the form.

If your order items sub-table are set up to cascade delete when the entry on the main order table is deleted it is relatively easy to go with option two.