manually overwrite a calculated total in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in a form that calculates the total of the three fields above
it:
-rent
-tax
-tenant improvements

....this equals the security deposit

I would like to be able to manually enter the amount of the deposit in those
rare instances when a deposit was negotiated, rather than reflect a total of
the three items above. Help is greatly appreciated!
 
You can't override a field if you've set its control source to a
calculation. What you have to do instead is set the value of the field in
the form's Current event and when the value changes in any of the 3 text
boxes. Typically, I'd create a routine in the form that does this
calculation:

Private Sub CalculatedDeposit()

Me.txtSecurityDeposit = Nz(Me.txtRent, 0) + _
Nz(Me.txtTax, 0) + Nz(Me.txtTenantImprovements, 0)

End Sub

and call that routine from the form's Current event, as well as in the
AfterUpdate event for each of the 3 text boxes.
 
I know some (many) people may shout at me for this suggestion, but <:-)

You could add another field in your table. aaaaarrrrrgggghhhh

Told ya.

Don’t worry, your table will still be normalized

I assume you have fields already called
[rent]
[tax]
[tenantimprovements]
If not then you will need to alter the calculations below

The new field would contain the amount agreed upon in the case of a
negotiated amount. You could call it NegAmount Set the default to 0

Next on your form you "could" if you wanted to amid all the shouting change
the calculation your have at the moment
=([rent]+[tax]+[tenantimprovements])

and you "could" (if you "really" wanted to) use this as you control source
=(IIf([NegAmount]>0,[NegAmount], [rent]+[tax]+[tenantimprovements])

Just an idea - have fun
 
I know some (many) people may shout at me for this suggestion, but <:-)

You could add another field in your table. aaaaarrrrrgggghhhh
Click to expand...

<preparing to shout>
<rereading the message>
Told ya.

Don’t worry, your table will still be normalized
Click to expand...

Sure will. The key is that you want to be able to overwrite the
calculated value.
I assume you have fields already called
[rent]
[tax]
[tenantimprovements]
If not then you will need to alter the calculations below

The new field would contain the amount agreed upon in the case of a
negotiated amount. You could call it NegAmount Set the default to 0

Next on your form you "could" if you wanted to amid all the shouting change
the calculation your have at the moment
=([rent]+[tax]+[tenantimprovements])

and you "could" (if you "really" wanted to) use this as you control source
=(IIf([NegAmount]>0,[NegAmount], [rent]+[tax]+[tenantimprovements])

Just an idea - have fun
Click to expand...

Another idea would be to have the [total] field in the table. In the
Form, put code in the AfterUpdate event of all three fields that
contribute to the total to calculate and "push" the value into the
total field:

Private Sub txtRent_AfterUpdate(Cancel as Integer)
Me!txtTotal = NZ(Me!txtRent) + NZ(Me!txtTax) + _
NZ(Me!txtTenantImprovements)
End Sub

This will load the total textbox with the sum of the three (the NZ()
will treat blank controls as zero); the resulting total can then be
overtyped with the negotiated total.

John W. Vinson[MVP]
 
Back
Top