Trapping negative values error in subform

[forcefield via AccessMonster.com]

I have a subform pulled from the table tblSub and a main form pulled from
tblMain. Both tables are linked by child and master relationship (using the
field items). My main table consists of items and type of wares. While my
subform (in datasheet view )consists items, date, purchased qty and consumed
qty and a unbound field called balance qty(running). How to ensure that user
do not enter a value in consumed qty such that the sum of ConsumedQty is less
that sum of PurchasedQty or else the balance qty is negative. I have tried to
code using before update in the subform but it did not work .

The datasheet subforms look like this

Date Purchased Qty Consumed Qty Balance Qty
12/03/08 20 10 10
15/03/08 0 2 8
17/03/08 6 0 14


Effectively the resulting Balance Qty = Sum of purchased qty – Sum of
Consumed Qty
That is the result is = 26-12 (14)


Please help.

 

[Beetle]

I would use a continuous form, format it to look like a datasheet, put
the Balance Qty control in the footer (not on each row), then in the
Before Update event of the Consumed Qty control verify that the
user has not entered a value that is more than the current Balance Qty.

JMO

 

[forcefield via AccessMonster.com]

Thanks. I will give it a try. Meawhile I am opened to any further suggestions.

I would use a continuous form, format it to look like a datasheet, put
the Balance Qty control in the footer (not on each row), then in the
Before Update event of the Consumed Qty control verify that the
user has not entered a value that is more than the current Balance Qty.

JMO

I have a subform pulled from the table tblSub and a main form pulled from
tblMain. Both tables are linked by child and master relationship (using the

[quoted text clipped - 17 lines]

Please help.