Validate Subrecord Count Before Inserting Master

DerryLyons,

Can you restate your question.?

It is not clear to me what you do, or do not want to display, and when.

Thanks
;-)

JeffCoachman

The form needs to pop up an error message if the user has not entered any subrecords into the subform.
(Sorry for not being clear in the original message...)  :) Derry

Try the Before update event of the main form.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim subreccount As Integer
    subreccount = [frmOrderDetailsSub].Form.RecordsetClone.RecordCount
    If (subreccount = 0) Then
        MsgBox ("No record in subform")
        'Cancel = True
    End If

End Sub

:-)

JeffCoachman

Jeff ~
  Thanks! That's where I started, but when I leave the main record to go to the subform, I get the "no record in subform" message. (Obviously...since they haven't had a chance to add the subrecords)

  I'm wondering if there's a way to trap when we actually move to the next main record. (I was checking for me.newrecord=0, too, but that didn't seem to help.)

There's the root of my question...
...it's not Before/After Insert since we want the main record to be created so we can create subrecords.
...it's not Before/After Update since adding subrecords won't necessarily affect the "dirty-ness" of the main record.

Hmmm....

DerryLyons,

The issue here is that the main form records get saved as soon as you move to a diffrenet record "OR" you click in the subform.

So it may be difficult to find any one event that will work for all possible.

Can you explain again what this system is ultimately doing?

Displaying Parent record with no Child reords?
Storing Parent Records with no child records?
Storing only Parent recodes with child orders?

This is very confusing:
"User enters new master record, chooses the school, then skips entering the test scores on the subform. Since the master record exists, moving to a new record (requery) correctly drops it from the combo box, and the user asks "how come I can't see school xx?"  "

Huh?

;-)

JeffCoachman

Yeah, it's kind of hard to explain, but I'll give it another whirl with more words...

Each of our schools needs to have a series of sub-records added. We start out with a blank master table (containing just the school codes, for tracking), and a blank sub-table that records the school code and test and test score (one-to-many).

For a new record, the user chooses the school from a drop-down combo box, which creates a record in the master table. They can then add the scores in the subform, too.

This combo box is tied to an unmatched query of schools... for every school that gets entered, the list of choices gets smaller. (This helps the user know which schools don't have entries yet.)

By default, the form is opened in "add only" mode, because we don't want the user messing with test scores that have already been posted.

...so...

User is looking a blank screen, chooses a school (which creates the master record). User then decides to add a new master record (which they can do), but skips the subrecords. I would like to error trap so if they try to leave the master record and do not have any subrecords, they get an error and are "stuck" until they choose a school and enter the subrecords.

Does that help paint the picture? If not, let me know. Thanks for your patience! :)

So in other words, ...you want  them to enter at least 1 sub-record for every Master-record created?

Yep - that's it! (Wow... why couldn't I have gotten there with fewer words?)

I think this will work... I wasn't able to open the design, so if you could post the code I'd appreciate it. Thanks for your efforts! :)

OK, I will post it tonight

Here it is
Access-EEQ24089171RequireChildMa.mdb