Avatar of DerryLyons
DerryLyons asked on

Validate Subrecord Count Before Inserting Master

This is probably straightforward, but I must be missing something.

Scenario:
 * Master form (one record per school) to enter test data (bound to a school location code)
 * Combo drop-down box shows schools without scores (OnCurrent runs a Requery to repopulate the combo box after each new record)
 * After choosing school, Sub form to enter test types/scores

Problem:
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?"  (Sometimes error trapping is better than constant reminders.)

THE QUESTION... which form action is appropriate to validate that there are sub-records before moving to the next master record? (Code is below, and logic works, just can't get the order right...) Since the user can bounce between master and subform, I really want to catch it when they move to a new master record.

Thanks!

Dim subreccount As Integer
    subreccount = Forms!Workspace![Workspace subform].Form.RecordsetClone.RecordCount
    If (subreccount = 0) Then
      MsgBox ("Please choose a different school or paste the P223 data")
      Me.Undo
    End If

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Jeffrey Coachman

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
ASKER
DerryLyons

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
Jeffrey Coachman

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
DerryLyons

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....
Jeffrey Coachman

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
ASKER
DerryLyons

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! :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

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

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
DerryLyons

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! :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jeffrey Coachman

OK, I will post it tonight
Jeffrey Coachman