Refresh causes unbound fields to be cleared

[TESA0_4]

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

 

[Jeanette Cunningham]

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]

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]

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]

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]

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]

Jeanette,

Success!

Throughout this whole discussion I have only been trying to achieve the 100%
test using a custom command button. Based on your most recent reply I
inserted:
tblPackageZonessubform.Form.Recalc
at the start of my command button code. It forced the upadate of the text
filed on the subform causing the percentage check to a valid test.

Thanks for your persistence and assistance. There seem to be SOOOO... many
methods and options with Access.

Regards,

Terry