At least one child record required

[spikepl]

Form/subform setup, and at least one child is required (e.g., in this setup, you cannot have an order without order lines). So the parent has at least one child or the parent must not exist.

Anyone with an elegant solution to this and willing to share?

A number of solutions float around the web, but I haven't found anything breathtaking really.

I do have a requirement of at least one child or else, so please do not debate this need but rather focus on the solution.

 

[spikepl]

I read your presentation. I am sorry but it seems to me to be way too complex for my needs - simply to ensure that if a parent exists then it has at least one child. I want something as simple as possible.

 

[jdraw]

spike, --for consideration - untested --

I haven't done this, but just thinking of what Tony has said, and some ideas, I think if you used transaction processing to add a Parent and Child in the same logical transaction you would guarantee that the parent had 1 or more child records.
It seems, if you identify the child that goes with the Parent, then

Begin Trans
Add Parent to tblParent, get pk value
Add Child to tblChild, set the fk value to link Parent and Child
End Trans

Either the whole transaction works, or nothing works.

If you start to delete child records for whatever reason, you would need to ensure there was always 1 child record for that parent record. Again this may best be handled via a transaction (or a UDF)

Begin Trans
If Dcount("*", "tblChild","ParentFk = " & tblParent.ParentID) (unsure of syntax but the point is to get records in tblChild that match this ParentID) >1 then
Proceed with the Delete
if DCount(..) = 1 then bypass the Delete and give a message that only 1 child records exists.
OR if Dcount(...) = 1 then you may want to delete the Parent and the Child to ensure no orphans
End Trans

If you decided to delete the Parent record, you could have Cascade Delete on to remove all related Child records.

Since we don't know your application, this may not be appropriate. However, it is an interesting question.

Good luck.

 

[spikepl]

I'll have a think again.

Transactions do seem an obvious thing, but cease to be, when you consider having a parent form and child subform, and all the hoops presented to the user. I mucked about with this for days, taking off form Galaxiom's transacted bound form (somewhere here in the forum - search for those keywords) , but it became way too complex.

I'll have a second think on UncleGizmo's thingy mañana.

The issue can be solved by brute force: threaten to delete parent and do so, if no child is created or the last child is deleted, but I was curious about other approaches.

 

[gemma-the-husky]

Form/subform setup, and at least one child is required (e.g., in this setup, you cannot have an order without order lines). So the parent has at least one child or the parent must not exist.

Anyone with an elegant solution to this and willing to share?

A number of solutions float around the web, but I haven't found anything breathtaking really.

I do have a requirement of at least one child or else, so please do not debate this need but rather focus on the solution.

I do not see how you can enforce this normally ....

what I would do is enter the order/lines into temporary tables, and just process a genuine completed order.

If you don't have any lines, just discard the temporary header.

(indeed this is what I do do, as on some systems I have done a "complete order" has to pass certain validation checks)
When you are happy with the temporary order, just append the header, and the lines to the normal tables.