Refresh field

[Annette]

I have a form that lists client names, amount charged, balance paid
(checkbox) and balance. If the user double clicks the balance field,
they can enter a partial payment. If the balance was paid in full, the
user would click balance paid. Once balance paid is checked, a record
is written to another table containing a key and the amount charged
field as the paid amount.

This form is based on a query record source and the form is in
datasheet view. I tried having the balance field part of the record
source, but when I do, I can't check the balance paid field as it
tells me the datasource is not updateable. So the balance field has a
control source of a formula with a dlookup.

Ultimately, I would like the form to be filled with clients with
outstanding blances. The user would then check the balance paid field
when payment has been received (98% of our payments are paid in one
fell swoop and the other 2% are partial payments). When they check the
box, I would like the balance field to be refreshed and show zero or
if a partial payment was made, show the remaining balance.

How can I do that?