Use value from one field in another

[Tara]

I have a subform which has several calculated fields. One field (StartAmt)
gets it's initial value from the parent form. The other fields are filled in
by the user with the exception of the field "PayOut" and the field
"Remaining", which are both calculated based on the "StartAmt" and the other
fields filled in by the user. Once the first record has been entered, I no
longer want the "StartAmt" to default to the value on the parent form.
Instead I want it to default to the value in the field "Remaining" from the
last record. What the best/easiest way to accomplish this?

Thanks!

 

[Jeanette Cunningham]

Tara, there a couple of ways at least to do this.
One way would be to calculate the value for Remaining in the BeforeUpdate
event for the subform and store that value in an unbound textbox on the main
form.
If you wanted to, in that before update event, if the update is canceled,
you could undo the change to that textbox on the main form that is storing
the value.
When the subform goes to the next record, it gets its default value from
that textbox on the main form.
Another option is to store the value of Remaining in a single field table in
the front end. Each time the value of Remaining is changed, you could run a
simple update query to change the value in that table, and do a DLookup each
time the subform goes to a new record to get the new default value.

Jeanette Cunningham

 

[Tara]

Thanks Jeanette. I think the first option is the one I'd like to try. I'm a
novice at coding though. Could you give me an idea of how to structure that?

Thanks again!

 

[Jeanette Cunningham]

--You will need code here to check that the fields needed to do the
calculation are filled in and cancel the calculation if some of the fields
are null. I assume you already know how to do that

The code in the before update event of the subform will include something
like the following

If [ValidationFunction] = True Then
Me.txtRemaining = Nz(Me.StartAmt, 0) - Nz(Me.txtPayOut, 0)
'force the form to recalculate
Me.Recalc
'store the value on the main form
Parent.txtStoreRemaining = Me.txtRemaining
Else
Cancel = True
End If

Nore:**Change the above to suit your rule for doing the calculation and
using your field/control names
ValidationFunction is code you write to check if the fields for the
calculation have been completed by the user.
The above code should put the value for Remaining on the main form.
Next the subform record is saved and the user goes to the next record on
the subform - and note they will still be on the same record on the main
form where the value for Remaining is saved.
To get the default value for the start amount for this next record put
code in the Current event for the subform

Me.txtStartAmt.DefaultValue = Parent.txtStoreRemaining

assuming the StartAmt is a number data type

Jeanette Cunningham