Require at least 1 child record when parent record data is entered

[jyow32]

I have a Parent table and a child table w/ a one-many relationship. I am using a main form (for parent record data) with a subform (for child record data) for entering data. How do I require that at least one child record is entered on the subform? I've set the child record fields to required, but unless a user clicks on the subform's new record indicator, the parent record is inserted into the parent table without any requirement notification for the child record data.

 

[RuralGuy]

I'm pretty sure all you need to do is look at YourSubFormControlName.FORM.RecordSet.RecordCount for <> 0 in the BeforeUpdate event of the MainForm. Using YourSubFormControlName of course.

 

[Rich]

Isn't the BeforeUpdate of the main form likely to fire as soon as you leave it to enter the subform though?

 

[RuralGuy]

Oops...my bad. You are absolutely correct Rich. Hmm...the MainForm *must* save the record so the SubForm will have a parent and be able to save. Getting a little tricky here. A method will have to be devised to keep the user from closing or moving to a new record until there is at least one record in the SubForm. Referential Integrity says that there can not be a child without a parent but you can still have parents without children. RI does not help us in this case. Actually if the user created the MainForm record by mistake and wants to leave then the MainForm record will need to be deleted in this case. jyow32, what is the effect of have a parent record without a child?

 

[bobburg]

Just wondering if you came up with a solution. I am also having the same problem you are. Would appreciate if you could let me know if you get a reply that works.

 

[gemma-the-husky]

logically, this will be very hard to enforce.

with a one to many relationship (parent to child), the point is that there can be (and has to be) an initial one - to -zero realtionship, when the parent record is first set up.

So you are looking for a situation where, having created a master record, you are not allowed to close the form, or navigate off the record until a child has been created. - or if you do allow this, you delete the parent record to permit it.

Note, that this is the same point that RuralGuy made above, maybe just described a little differently.

[edited

one way around this maybe, is in the afterinsert event for the master record, actually run a sql statement to create an initial child record - although its hard to see the benefit of such an artificial mechanism. - Why is there this need to have a child record?]

 

[WIS]

I have a Parent table and a child table w/ a one-many relationship. I am using a main form (for parent record data) with a subform (for child record data) for entering data. How do I require that at least one child record is entered on the subform? I've set the child record fields to required, but unless a user clicks on the subform's new record indicator, the parent record is inserted into the parent table without any requirement notification for the child record data.

Here is something to consider. I assume that both tbls have ParentID - Long, PK in Parent and FK in Child. If no entry is made into the Child tbl then there will be no ParentID in there matching the ParentID in the Parent tbl.

I would write some code in the module section of the main form that looks for a matching ParentID in the Child tbl (at the appropriate point) and put up a MsgBox that informs and force to a ctrl on the Main form (Setfocus) and keep the user there until a Child record is written. You would force the Setfocus if the user tried to Exit the form or move to a new (Parent) record without creating a child record.

 

[bobburg]

A good example I am working with is a Parent Table recording requests from suppliers such as an Request for Tender, Request for Quoute, Request for Proposal or a Request for information. The Primary Key would be the record number for the type of request. The primary table contains data on the request such as a statement of work, procurement authorization , request type (RFT,RFQ, RFP or RFI) and type of procurement. The child table would contain a record for each supplier the request was sent to. Always a child record is required and depending on the type of procurement could have 7 or 8 records of suppliers. I have found that the users tend to enter the data in the primary table just to get the record number but exits to form before adding data in the child table.

 

[Banana]

One easier way is to re-implement the navigation buttons so you can check that there's at least one record in subform.

Alternatively, if default navigation buttons is preferred, create a variable to act as a "Dirty" flag with broader scope than the default Dirty property of form and check both Current and UnLoad event for that flag, in which case snap back to the original record asking the user to add a child record.