Hi Jeanette,
Thanks for your persistence!
Yes you have the gist of what I am trying to achieve. Yes I have introduced
a command button to go to the next record.
What I observe from testing is that if, after entering/updating a percentage
value on the subform, the cursor is moved directly to the Next Record command
button on the Parent form, the command button code seems to runs before any
other event triggered code has an effect. This means that the total
percentage field has not updated to reflect the last input/edit and therefore
the command button total percentage test is invalid. If the cursor is clicked
in any irrelevant field before clicking the Next Record command button the
process works fine becuase the total percentage field has opportunity to
update.
I find that coding a Refresh for either the Parent or Subform as part of the
command button routine causes the total percentage field to go blank until
the command button routine is complete (which invalidates the percentage
check).
My dilemma seems to focus on a way of getting the total percentage field to
update/refresh before the command button code tests the value in the field.
If you've had enough of trying to nut out my problem I'll understand!!

Regards,

Terry

"Jeanette Cunningham" wrote:

With main form - subform setup in access, the subform record is saved as
soon as the user clicks on the main form.
If I am understanding your setup correctly, you want to prevent the main
form moving to a new record or closing if the subform control isn't up to
100%.
You could probably do this by using the On Exit event of the subform to
check the value of the subform control and show a message asking the user to
go back and correct the percentages.

You could probably also do it by running code on the main form to check the
value of the subform control and show a message asking the user to go back
and correct the percentages. You would need to code the unload event if the
main form was closing, but if user just going to another record you would
need to catch it before the form goes to a new record - not sure how would
do that if you are using the standard nav buttons. You would probably need
to make your own button for going to another record and code its on click
event to check the value of the subform control and show a message asking
the user to go back and correct the percentages.




"TESA0_4" wrote in message
...
Hi Jeanette,
Maybe I'm missing something in your explanation but it seems to me that
there is a problem.
The Before Update event triggers when the cursor moves to the parent form
and when the focus moves from one record to another in the subform. This
means the 100% check is being applied before the user has had opportunity
to
enter the various percentage estimates.
Also, the percentage fields on the subform are being summed to a text box
on
the footer of the subform (that is not visible because the subform is in
datasheet view). It is this field that is use to test for 100%. I note
that
this field is not updated until and 'programing code' associated with any
triggers has been executed. Forcing the summing text box to update seems
to
be the key to getting the 100% test to work correctly.
If you have any other thoughts I'd be most appreciative?

Regards,

Tesa

"Jeanette Cunningham" wrote:

Hi,
there is one place to put this type of code.
It is the Before Update event of the form.
When a user moves the cursor off the subform, access saves the record.
If your code in the Before Update event finds that the text box for
allocations is not 100%, you can put the line
-- Cancel = True

The above line, when used in the form's Before Update event will stop the
form from saving the record.
You can add in a msgbox and you also code the msgbox function to give the
user a choice of retrying or canceling.


Jeanette Cunningham -- Melbourne Victoria Australia




"TESA0_4" wrote in message
...
I have a subform where a user is required to roughly estimate cost
allocations between several possible work categories. eg if there are 3
applicable work categories they might allocate a percentage split of
10%:70%:20%. I have an unbound field that sums the percentage to assist
the
user with correct summing of the percentage allocations to 100%. A
button
(on
the parent form) that allows the user to move to the next record is
coded
to
ensure the total percentage allocation on the current record equals
100%
before allowing a move to the next record.
If the cursor is moved straight from a Percent field on the datasheet
subform to the Next Record button the unbound PercentSum field has not
updated so the percentage check on the command button is invalid.
Inserting a
Refresh in the command button code results in the unbound field being
cleared
until after the full subroutine has run.
How can I force a valid check of the percentage sum on the current
record
before moving to the next record?
Thanks in anticipation of any advice!!

My current code:
Me.Refresh
If tblPackageZonessubform.Form!PercentSum.Value 100 Then
MsgBox ("The Percentage split does not total 100")
Exit Sub
End If
DoCmd.GoToRecord , , acNext