Order Form Saving Behavior

[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

 

[Pat Hartman]

Once the Main form record is saved, the horses have escaped from the barn so to speak. And as Doc has pointed out, in a relational database, the parent record MUST ALWAYS be saved before you can save the child records. Of course, you can use unbound forms and shadow tables but I would not recommend going there under any conditions at your level of expertise.

If you have a business rule that says that a parent record must have at least ONE child record, then I would add a flag to the parent record that starts out as "incomplete". All your queries EXCEPT for the one used by the maintenance form, ignores all records that are "incomplete" and the one used to find "incomplete" records for cleanup. Then in the AfterInsert event of the subform, you change the flag in the parent form to "complete". Do NOT run an update query. Jus poke the form:

Me.Parent.IncompleteFlg = False

That is all it takes unless you allow deletes of child records. In that case, you have to determine if you are deleting the LAST child record and either prevent the delete or update the parent record to Incomplete again.

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. But with this particular requirement, you ALWAYS are in jeopardy of having orphan parent records unless you actually use unbound forms because this is a cart and horse situation which is NOT the way relational databases are intended to work. That's why I use a flag. I can't prevent the orphans but I can avoid ever trying to use them for anything other than updates.

 

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

 

[Pat Hartman]

If the main form is bound, Access will save the record when focus moves to the subform. Your transaction sits INSIDE the class's transaction so you don't have control over when the bound record gets saved if Access wants to save it. You can stop the save with validation but not by clicking into a subform.

I agree, there are ways around this but you REALLY, REALLY need a VERY GOOD reason because in a relational database the parent record MUST be saved first. Of course, you could use a non-normalized structure and assign a dummy FK to the child records and populate the actual FK after the fact but this is too convoluted for words.

 

[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

 

[Pat Hartman]

Just make sure you don't power off or lose your network connection