Order of Events on Main Form To Verify Subform Values

[Joe Williams]

I have a main form with a subform embedded that lists percentages for each
main form record. The total of all the percentages in the subform records
for each main record MUST equal 100%.

I have put a footer in the subform that totals the percentage fields. No
problem. Now where I am struggling is in where to check and trap the error
if the total of all the percentages is < or > 100%.

I have tried several subform events (On Change, AfterUpdate) to check the
total of the percentages field and make sure it is 100%. It works most of
the time but there seems to be different ways the user can make a mistake
and not be caught by the different events I am checking it on. (For
instance, I can change percentage values in the subform and then click back
to the main form without either the On Change or AfterUpdate event firing to
check the total from the subform.) So the possiblity exists that you could
move on to antoher main form record with the subform record percentages
being incorrect.

My thought process was maybe I should just do the check before I leave each
main record rather than trying to check everytime a user enters values in
the subform. Would this be the most efficient way to do this?

If so, what main form event can I use to check the percentage totals from
the subform that would prevent the user from moving off of the current
record unless the subform values are correct? How would I go about coding
it?

Thanks

Joe

 

[Brian]

I have a main form with a subform embedded that lists percentages for each
main form record. The total of all the percentages in the subform records
for each main record MUST equal 100%.

I have put a footer in the subform that totals the percentage fields. No
problem. Now where I am struggling is in where to check and trap the error
if the total of all the percentages is < or > 100%.

I have tried several subform events (On Change, AfterUpdate) to check the
total of the percentages field and make sure it is 100%. It works most of
the time but there seems to be different ways the user can make a mistake
and not be caught by the different events I am checking it on. (For
instance, I can change percentage values in the subform and then click back
to the main form without either the On Change or AfterUpdate event firing to
check the total from the subform.) So the possiblity exists that you could
move on to antoher main form record with the subform record percentages
being incorrect.

My thought process was maybe I should just do the check before I leave each
main record rather than trying to check everytime a user enters values in
the subform. Would this be the most efficient way to do this?

If so, what main form event can I use to check the percentage totals from
the subform that would prevent the user from moving off of the current
record unless the subform values are correct? How would I go about coding
it?

Thanks

Joe

Actually, with straightforward bound forms you simply cannot win with a
requirement like this.

Assuming that the subform records get added one at a time, then obviously
they will not add up to100% until they have all been entered. So, you can't
put the validation in the subform, unless all you are trying to achieve is a
warning, rather than actually enforcing the 100% validation.

You can't put the validation in the main form either. Even assuming Access
had an "AboutToLeaveRecord" event (which it doesn't), there would be nothing
to stop the computer crashing (or simply being switched off!) after the
subform records had been entered but before the validation was performed.

If you absolutely must do this, then you will have to EITHER use unbound
forms (some kind of wizard-type functionality may be indicated) OR use forms
that are bound to local "interim" tables. Either way, the idea is to gather
all the data (main record and ALL sub records) before using VBA code to
perform the validation and commit the data to the "live" tables as a single
transaction.

Note that the second approach (local tables) will only work if you have a
split (front end/back end) database so each user has his/her own copy.

However, whatever you do I suspect you will find that you have a further
nightmare on your hands when it comes to subsequent amendments to the data,
unless you can restrict your users to simply deleting the entire transaction
and entering the whole thing again.

 

[Joe Williams]

Thanks Brian,

I will probably just try to play with "warnings" and just make it difficult
for the user to leave teh current record.

What is the closet thing that Access has to an "AboutToLeaveRecord" event?
(Something that fires before a record is saved, when the form is closed, and
if they move to a different record.)

Thanks again.

Joe

 

[Brian]

Thanks Brian,

I will probably just try to play with "warnings" and just make it difficult
for the user to leave teh current record.

What is the closet thing that Access has to an "AboutToLeaveRecord" event?
(Something that fires before a record is saved, when the form is closed, and
if they move to a different record.)

Thanks again.

Joe

The BeforeUpdate event (for the form, not the individual controls) fires
before a record is saved, regardless of why/how the record is being saved.

The Unload and Close events fire when a form is being closed.

There is no event that fires when you leave a record. The Current event
fires when you arrive at a record, but by then it's too late to do anything
with the previous record.

If you are going to rely on warnings, you might want to consider
incorporating some kind of regular integrity check (say, when the system is
opened) that tests for transactions where the sub records don't add up to
100% of the master record, and warns the user, or you, or someone!

 

[Ken Ismert]

Joe,

May I gently suggest that form events aren't the way to solve this
problem? Try an end run:

* First, constrain your main form. Set it's Default view to Single
Form, and set Navigation Buttons to No. Set Cycle to Current Record.
Leave the user with no built-in way to navigate.
* Add your own custom "Previous" and "Next" buttons. Use
DoCmd.GoToRecord to implement their main functions.

Now, when the user wants to move to the Next record, they have to click
your button, and you get the click event BEFORE the move to the Next
record. You can then check the subform and warn as appropriate.

Lastly, if the user doesn't want to (or can't) fix the subform records
at that time, let them exit the record. Simply insert a new "error
correction" record in the subform that makes the total 100%. Set a
Yes/No field in the underlying table that identifies this as an error.
This way, you can easily find record groups with errors.

This isn't a fool-proof system. You have the mouse scroll-wheel to
contend with (there is a simple fix for this, too), and the system can
still crash, leaving incomplete subrecords.

In this case, by ignoring the 'solution paths' provided by Access, you
can go a long way towards what you want.

-Ken