Checkbox Calculations - Please Help!!

[gjoneshtfc]

Hello

I have a tabular form which has a checkbox for each record. In the form

footer i have two calculations. One is a total of the values of all the

records which works fine. The second calculation does not work however
so if anyone can help me it will be greatly appreciated:

I need the second calculation to total all the values who's checkbox is

checked and then subtract this from the total in the first calculation.

I am currently doing it with the following formula in the control
source of the textbox:

IIF([Cleared?]=yes,([Total]-[BalanceDue]),[Total])

This works when one checkbox is checked. However, when the second one
is checked it only takes that value from the total. What i need is to
sum all the [BalanceDue] that has a tick and then subtract this from
the total in [Total] but i have no idea how to get it working!

Any help would be great,
Kind regards, Gareth Jones

 

[John Vinson]

I need the second calculation to total all the values who's checkbox is
checked and then subtract this from the total in the first calculation.

I'd suggest putting an additional calculated field in the query upon
which the form is based:

ClearedValue: IIF([Cleared?], [<the field you're summing], 0)

and sum it in the Form footer.

John W. Vinson[MVP]

 

[gjoneshtfc]

Hi John,

Thanks for the reply. That works great - however... When i tick a
checkbox the Sum calculation does not update. I have to close the form
and open it again so it displays the correct value. Any ideas how to
solve this?

Thanks again.
Kind regards, Gareth

 

[John Vinson]

Hi John,

Thanks for the reply. That works great - however... When i tick a
checkbox the Sum calculation does not update. I have to close the form
and open it again so it displays the correct value. Any ideas how to
solve this?

Thanks again.
Kind regards, Gareth

Requery the textbox in the AfterUpdate event of the checkbox:

Private Sub chkMyCheckbox_AfterUpdate()
Me!textboxname.Requery
End Sub

John W. Vinson[MVP]