Auto form to table calculation

  • Thread starter Thread starter Jerry Caldwell
  • Start date Start date
J

Jerry Caldwell

I have an form in which I created the following
control source for balance:

=[SEDeposit]+[HQDeposit]-[SEExpense].

The problem is that the calculation works
in the form, but won't update in my table and I need a
historical record and do not want to calculate the result.
I realize that to correct or change any one of those
values stored the result would be incorrect but I need a
history of the origional calculation in the origional
table.
Thank you
 
I have an form in which I created the following
control source for balance:

=[SEDeposit]+[HQDeposit]-[SEExpense].

The problem is that the calculation works
in the form, but won't update in my table and I need a
historical record and do not want to calculate the result.
I realize that to correct or change any one of those
values stored the result would be incorrect but I need a
history of the origional calculation in the origional
table.
Click to expand...

You'll need to "push" the value into the table field using VBA code.
For instance, in the Form's BeforeUpdate event you could copy the
value in this expression into a textbox bound to the table field.
Let's say you have two textboxes, txtShowBalance with the above
expression as its control source, and txtStoreBalance bound to the
field where you want the balance stored. Use code like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any validation code for your form goes here>
Me!txtStoreBalance = Me!txtShowBalance
End Sub

You can set the Visible property of txtShowBalance False if you don't
want to see it, or True if you want to show the balance existing in
the table when you bring up a record. Hopefully the two textboxes will
show the same value though!
 
Back
Top