Prevent form save if subform empty

Use the ReordsetClone of the (sub)subform:

If Me!NameOfSubformControl.Form.RecordsetClone.RecordCount = 0 Then
    ' No child records.
End If

If Me!NameOfSubformControl.Form!NameOfSubSubformControl.Form.RecordsetClone.RecordCount = 0 Then
    ' No grandchild records.
End If

Select allOpen in new window

Thanks, Gustav.  So, where am I suppose to place this code?  Thanks

Could be in the BeforeUpdate event:

Cancel = Not CBool(Me!NameOfSubformControl.Form.RecordsetClone.RecordCount)

If Cancel = True then
    ' No child records. Pop message?
End If

Select allOpen in new window

Sorry, Gustav.  I cannot connect the dots here.  I'm not sure what goes where.  My apologies.

I'm a little confused by the question but If you are saying that you don't want to save the "parent" record if there are no "child" records, that can't happen automatically because the "parent" record MUST be created prior to any "child" records (we saw the problem that created in your other thread).  To do what you want will require a custom process involving temp tables.  You will have to create the form/subform and bind them to temp tables.  The main form will need a "done" button or code placed in the unload event of the parent form.  Your code would then need to check to see if subform records have been created if they have, then you will use DAO to insert the mainform record into the real table.  This allows you to obtain the autonumber.  Then you can use an append query that copies the subform records and appends them to the real child table while using the variable autonumber as the foreign key.  Then the code needs to delete the parent record in the temp table and assuming you have created relationships and enabled cascade delete, Access will also delete the child rows.

Don't engage in this endeavor lightly.  It is complicated and I didn't cover all the moving parts.  I just wanted to give you a sense of how you would need to do this.

An alternative which will end up being less work is to silently delete childless parent records in the unload event of the form.  This is also not foolproof so you should probably also run periodic scans to find childless records and deal with them.  Deleting the parent seems OK since you really can't force people to enter the data.  This may have been what Gus was recommending but I'm not certain since he didn't say anything about actually deleting the parent record.

Sorry for the confusion, Pat.  I'm trying to minimize the creation of parent records that lack child records,  As you have said before, you can't have a child w/o first establishing a parent record.  I get that.  I just thought there be an easier way to delete the parent if no child records were created in a form / sub form scenario.  The more I learn about Access, the more I realized I don't know.  Can you offer any guidance into the deletion of a childless parent record via unload?

Thanks

Sorry, I don't have any sample code.  I have never created a parent record with nothing but a PK so I've never had a need for this and I still think you don't either but without seeing the whole app, I can't be sure.  I can see that the parent table contains other data fields.  Where are they coming from and why is that process not creating the record are just a few questions.

Before you commit to this method, please check  in the unload event if you still have access to the pk field.  Put a debug.print line that prints the PK field and see what happens.  You should but I don't know for sure.    If you do, you can use a DCount() to determine if there are child records and if the count is 0, just run a query to delete that specific record.

You can always have  DELETE query that deletes all the entries of parent record that are childless.
If for example you had the following query

SELECT ParentFormTable.ID, Count([SubFormTable.ID]) AS Expr1
FROM ParentFormTable LEFT JOIN SubFormTable ON ParentForm.ID = SubFormTable.ID
GROUP BY ParentFormTable .ID;

Select allOpen in new window

You would have the No of Child Records for each Parent Record...
So then its rather easy to construct a DELETE query that deletes the "offending" parents

DELETE *
FROM ParentFormTable 
WHERE ID  IN (SELECT ParentFormTable .ID
FROM ParentFormTable LEFT JOIN SubFormTable ON ParentFormTable .ID = SubFormTable .ID
GROUP BY ParentFormTable .ID
HAVING (((Count(SubFormTable .ID))=0)));

Select allOpen in new window

Substitute ParentFormTable  & SubFormTable  with the Tables you use for Parent/Subform Recordsource along with the key fields (ID) and you would be good to go...
Remember to use this BEFORE opening any form.

I cannot connect the dots here.

You asked: Prevent form save if subform empty

That means if no record exists in the subform, cancel an update.
This expression counts the child records:

Me!NameOfSubformControl.Form.RecordsetClone.RecordCount

Select allOpen in new window

Thus, if no records exist, prevent the form save by setting Cancel to True in the BeforeUpdate event of the form.

Gus, you have to save the parent form before you can even attempt to add records to the subform so checking in the beforeUpdate event for child records doesn't solve the problem

True Pat. I realise now, that I have no idea what this question is about.

It happens to me more and more lately :)