Order Form Saving Behavior (1 Viewer)

[accessonly11]

Dears,

My order form have order details subform. When i open the order form, i set customer bydefault value so it can generate new order id automatically, (because i want that before entering the customer name, user firstly enter products in order details, then user can change customer name).

but there is problem with saving, it is saving while order details records is blank,
i want to prevent it. means if order details have no records, then new order id should be undo/ no save.

 

[Ranman256]

You MUST save the master record 1st. This generates the autonum ID.
THEN you can enter subform data.

 

[accessonly11]

You MUST save the master record 1st. This generates the autonum ID.
THEN you can enter subform data.

master record is saving first on my default values. but i want to unsave/undo if subform is null

 

[The_Doc_Man]

Let's try that again. You will ALWAYS save the master record at a time when there is (as yet) no child record. This is actually REQUIRED (indirectly) by having relational integrity. I.e. you cannot even DEFINE a child record until the parent record exists. Therefore, your subform will ALWAYS start out null. It is unavoidable. If you then try to undo creation of the parent because it is childless, and you were using an autonumber scheme, you have now irrevocably consumed an autonumber and you will as a result get gaps in your numbering.

If that is OK then fine, no biggie, you will get non-contiguous numbering. The main question is going to be "WHEN do you know that the parent record WILL end up with no child records?" Because remember that you will always START with no child records.

As to "saving with default values" - the SAVE action most likely occurs when you prepare to start the first detail (child) record because that SAVE is triggered by focus moving from the parent record to the child record. That, too, is pretty much unavoidable.

 

[CJ_London]

There are two possible ways

1. have some code to delete the master record if no line items are entered.
2. investigate using begintrans/commitrans method

Workspace.BeginTrans method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

edit: found this example for begintrans/commitrans

How to use transactions in Microsoft Access VBA

How to use transactions in Microsoft Access VBA when executing multiple queries with VBA code.

codekabinett.com

 

[CJ_London]

I'm sure that CJ's code would be fine assuming that no system outage happens after the parent record gets saved but before the form closes and runs the delete code.

That is why I suggested the begin/commit trans option - in the event no line records are available to be saved, the parent save can be is aborted.

It is a technique I developed a few years ago for a client, it gets quite complicated, but does work. Because it is complicated its not easy to extract into an example app. Perhaps if I get some time I can do so as it is quite a common question.

 

[arnelgp]

add code to your Main form (Orders form), Close Event:

Private Sub Form_Close()
CurrentDb.Execute "Delete A.* from Orders As A " & _
    "Left Join OrderDetails As B On A.OrderID = B.OrderID " & _
    "Where ((B.OrderDetailID) Is Null);"
End Sub