Validate text boxes based on subform entries

[kickergirl]

I'm not sure my title actually describes my problem, but here it goes. I am creating a form to track account information for participants. Basically, a single participant can be offered up to $3,000 for the first year and up to an additional $3,000 for the second year.

The main form tracks the dates the offers were made. And I have incorporated a subform to track the disbursements, whether for the first year allotment or second year allotment.

I have 3 hidden text boxes in the footer of the subform to calculate 1) total disbursements to date for the first year 2) total disbursement to date for the second year and 3) total disbursements to date regardless of the allotment year. I then have 3 text boxes on the main form that use the 3 hidden text boxes as their control source.

My question relates to alerting the user that the disbursements total to more than the allotments. I have attempted If/Then statements in the hidden subform controls, the main form controls, and the subform control that captures the individual disbursements. Nothing works. Any suggestions?

[nico5038]

Didn't you try to drop these hidden fields and use the DSUM() function like:

IF DSUM("AllotAmount","tblX","UserID=" & [UserID]) > 3000 then
...
 

Thus you can calculate (also for a period by using the WHERE part) each value needed "dynamically".

Nic;o)