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!
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
ASKER
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
(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.
If (subreccount = 0) Then
MsgBox ("No record in subform")
'Cancel = True
End If
End Sub
:-)
JeffCoachman
Something like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim subreccount As Integer
subreccount = [frmOrderDetailsSub].Form.
If (subreccount = 0) Then
MsgBox ("No record in subform")
'Cancel = True
End If
End Sub
:-)
JeffCoachman
ASKER
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....
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
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
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! :)
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?
ASKER
Yep - that's it! (Wow... why couldn't I have gotten there with fewer words?)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Can you restate your question.?
It is not clear to me what you do, or do not want to display, and when.
Thanks
;-)
JeffCoachman