form with empty sub form

[bilalo]

I have a form and a sub form. I want to prevent saving records in the form if the sub form is empty.


My current solution is the form_close event, I check the RecordsetClone.RecordCount of the subform, if it is equal to zero then I delete the record on the form using a DoCmd.RunSQL command.
It is working most of the time, but I don't know why I am still finding (rarely) some records with empty sub form.
What could be the problem? Is there a better solution?

[June7]

So you only want a parent record if there are dependent records? Need to provide code for analysis.

[Micron]

When you edit a main form record and move to the subform, you've just committed the record change in the main.
When you close a form, the subform closes first, so where is DoCmd.RunSQL? If on the main, too late. If you post the code, both close events might be needed for review.

[bilalo]

This is the code I am using:

Private Sub Form_Close()
If Nz(Me.frmSubOrder.Form.RecordsetClone.RecordCount, 0) = 0 Then
DoCmd.RunSQL "Delete from Orders where OrderNo=" & OrderNo
End If
End Sub

Does it make a difference if I use this code on the subform_close() instead of form_close() ?

[June7]

I just did a test.

Main form Close event runs before subform Close.

Can you identify conditions when it doesn't work? Have you step debugged?

If you want to try in the subform:

If Nz(Me.RecordsetClone.RecordCount, 0) = 0 Then
DoCmd.RunSQL "Delete from Orders where OrderNo=" & Me.Parent.OrderNo
End If

[Micron]

Main form Close event runs before subform Close.

Sorry, I meant to say unload. Subform unloads first. I suspect a record save occurs in the unload event, not the close event, which is why I tested that event. I reported on the close event by mistake. You could try moving code to unload event as I think the record is potentially saved before close event.

[orange]

bilalo,

Can you tell us more about the data involved? Usually form/subform construct represents tables in a 1 to M relationship. eg an Order has 1 or Many Items; a Project has 1 or Many Tasks...
Seems your situation is if I don't have child records, don't record anything???
Just looking for a little clarification.

[bilalo]

bilalo,

Can you tell us more about the data involved? Usually form/subform construct represents tables in a 1 to M relationship. eg an Order has 1 or Many Items; a Project has 1 or Many Tasks...
Seems your situation is if I don't have child records, don't record anything???
Just looking for a little clarification.

Yes that is exactly my case, I need the order to be cancelled if it doesn't contain any item