Subform entry check

[hotplate]

Is there any way that I can make sure that the time entries added
together in a subform with a one to many relationship do not exceed a
number in the parent record when added together?

For example, in the parent record I have a field that records the
available time. The subform is used to track how many minutes of that
time is used per each task.

The best validation would be to make sure that all the time entries in
the subform add up to the available time in the parent record, but I
don't know if that is possible.

Any ideas?

 

[Tom van Stiphout]

On Fri, 13 Nov 2009 04:29:30 -0800 (PST), hotplate

Yes. In the subform's Form_BeforeUpdate event you can perform the
math, and set Cancel=True if you're unhappy with the result.

To get the total of the subform rows can be done various ways, for
example with a Totals query, with a DSum call, or - my favority - with
a hidden textbox in the subform's footer with its ControlSource set
to:
=Sum(myTextBox)
(of course you replace myObjectNames with yours)
Then you could write:
if Me.mySumTextBox > Me.Parent.myTotalTextBox then
msgbox "Aaaarrrccchhh!"
Cancel = True
end if

-Tom.
Microsoft Access MVP