Terry,
another thing about access is that it puts a lower priority on updating
calculations in text boxes.
Try using the On Exit event of the subform to calculate the percentage like
this:
--Me.Recalc

The Recalc forces access to calculate, you can look it up in the vba help.

If you still find that the user can click on the main form's nav buttons
before the calculation is made, then I suggest you do away with the default
nav buttons and make your own. That way you can control what happens when
they are clicked.

Another way would be to separate the main form and the subform.
The main form could be used to open the subform as a form instead of a
subform.
The main form is closed or hidden while the user edits/updates the second
form.
This way the user doesn't have any way of closing the second form except
from its close button.
You can put the code to check the % on the close button.


Jeanette Cunningham -- Melbourne Victoria Australia



"TESA0_4" wrote in message
...
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