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.

 

[Uncle Gizmo]

I wrote code for creating a "Check List" this is a presentation explaining how it works:-

List To Data

Instead of adding a list of records, just add one record.

The "Multiple Check List" example could be used as well, giving the ability to add a single record to "All" your sub-forms if required.

 

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

 

[Uncle Gizmo]

Thanks for the feedback I know what you're getting at with the complexity! It's actually a very simple idea.

I think the complexity you perceive is more about my presentation than the actual fundamental things that are happening.

I think you're thinking about it from the wrong angle; it's no good thinking "I've got the parent" now where's the child?" That's too late... You need to create the child when you create the parent, otherwise what other event will give you what you want? You could possibly use the on current event or have the user press a button but there's nothing better than the after update event of the text box where you are adding the parent detail.

Now for the details of the record you want to add, where would you store it? The best place is in a table. I guess you already have the master table (Called "tblMaster" in the presentation) the table containing the "Parent" details and you probably have the "Child" table (Called "tblList" in the presentation) to show the child details, so for this to work all you need extra is a table (Called "tblData" in the presentation) and a couple of functions which fill the child table with the record from "tblData".

I'd be happy to have a look at your database to see if I could work out how to fit it in. I'd probably want to do a video so others could benefit, so it would be best to have sample data in it.

I realise it may not be as simple as that! Whether it will fit in easily, will depend a lot on how complicated your table structures are. And of course it may well not be suitable at all, but I don't see complexity as the issue its more about my presentation being overly complex.

 

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