calculation on form

  • Thread starter Thread starter Meg
  • Start date Start date
M

Meg

I have a form I am entering data into which populates a
form. I have a field "Nettransaction" that is calculated
and it NEEDS to go into the table. That number is a
historic record of what the value was at that time and
HAS to be recorded.

The calculation for Nettransaction is Transactionamount -
commission - outgoingcommission.

The commission is usually 6% but may change so I already
have the following code in the AfterUpdate event
procedures for both commissionrate and transactionamount:

If IsNull(Me.CommissionAmount.Value) Then
'no commission amount entered yet,
'so generate default value
Me.CommissionAmount.Value = Me.PickupAmount.Value *
Me.CommissionRate.Value
End If

I've tried several things which didn't work. What do I
need to do to get the calculation for Nettransaction and
have it enter into the table?

Thanks in advance!
 
Hi Meg

i would personally use the OnExit or OnChange event rather than the
AfterUpdate and i would add
me.CommissionAmount.requery
before the end if

let us know how you go

Cheers
JulieD
 
Julie:

Thanks for responding. When I did what you suggested, my
commission calculation quit working, so I switched it
back. However, that still doesn't solve my question
about the "nettransaction" field. Any ideas on that one?

Thanks again.

Meg
 
Hi Meg

sorry just read your original post again and i know what's up but i need to
get the sequence clear in my head
when & how do you calculate Nettransaction?

cheers
JulieD
 
Julie

Thanks so much for sticking with me.

I am opening a form from a switchboard which is created only for adding a new record. The five main fields are transactionamoun
commissionrat
commissionamoun
outgoingcommissio
nettransactio

I enter the transactionamoun
I have a default for the commissionrate but I may change the percentag
Commissionamount automatically calculates and I can change it if a special deal was made (ie flat $5000 instead of 6%
There are occasionally outgoing commissions paid to another company but not always so I enter that amoun
Nettransaction is transactionamount - commissionamount - outgoingcommissio
This nettransaction may be adjusted later and that is handled elsewhere, but for legal reasons, we have to capture what this initial nettransaction amount is and store it in the table I'm building

The code I I already have in the AfterUpdate event
procedures for both commissionrate and transactionamount is working fine and it is

If IsNull(Me.CommissionAmount.Value) The
'no commission amount entered yet
'so generate default valu
Me.CommissionAmount.Value = Me.TransactionAmount.Value *
Me.CommissionRate.Valu
End I

What I am looking for is what I need for the Nettransaction

Hopefully that wasn't too confusing

Thanks again so much
 
Hi Meg

okay, nettransaction therefore depends on
transactionamount
commissionamount - which in turn depends on commissionrate and
transactionamount
outgoingcommission

so you will need to add this code to the AfterUpdate (or whichever event you
choose to use) event of all of these fields so that if any of them change
the new figure is reflected in the nettransaction field in the table:

Me.nettransaction = me.transactionamount - me.commissionamount -
me.outgoingcommission
Me.nettransaction.requery

you will need to test a change in each of the fields and ensure that this
works as you want - i have a slight concern regarding the commissionamount
field (as it is a calculated field).

let me know how you go

Cheers
JulieD


Meg said:
Julie:

Thanks so much for sticking with me.

I am opening a form from a switchboard which is created only for adding a
Click to expand...
new record. The five main fields are transactionamount
commissionrate
commissionamount
outgoingcommission
nettransaction

I enter the transactionamount
I have a default for the commissionrate but I may change the percentage
Commissionamount automatically calculates and I can change it if a special
Click to expand...
deal was made (ie flat $5000 instead of 6%)
There are occasionally outgoing commissions paid to another company but
Click to expand...
not always so I enter that amount
Nettransaction is transactionamount - commissionamount - outgoingcommission
This nettransaction may be adjusted later and that is handled elsewhere,
Click to expand...
but for legal reasons, we have to capture what this initial nettransaction
amount is and store it in the table I'm building.
 
Julie

Good news/Bad news.
It works fine unless I want to change the commission
value from the calculation. Remember, that is the normal
amount of commission, however it may occassionally change
depending on the agreement.

Any ideas?

Thanks,

Meg




-----Original Message-----
Hi Meg

okay, nettransaction therefore depends on
transactionamount
commissionamount - which in turn depends on commissionrate and
transactionamount
outgoingcommission

so you will need to add this code to the AfterUpdate (or whichever event you
choose to use) event of all of these fields so that if any of them change
the new figure is reflected in the nettransaction field in the table:

Me.nettransaction = me.transactionamount - me.commissionamount -
me.outgoingcommission
Me.nettransaction.requery

you will need to test a change in each of the fields and ensure that this
works as you want - i have a slight concern regarding the commissionamount
field (as it is a calculated field).

let me know how you go

Cheers
JulieD


created only for adding a
new record. The five main fields are transactionamount change it if a special
deal was made (ie flat $5000 instead of 6%) another company but
not always so I enter that amount commissionamount -
outgoingcommission handled elsewhere,
but for legal reasons, we have to capture what this initial nettransaction
amount is and store it in the table I'm building.
Click to expand...
transactionamount is working fine
 
Hi Meg

yes, i think you'll have to repeat the calculation of commissionamount in
the
Me.nettransaction = me.transactionamount - me.commissionamount -
me.outgoingcommission
line of code
e.g.
Me.nettransaction = me.transactionamount - (Me.TransactionAmount *
Me.CommissionRate) - me.outgoingcommission

Let us know how you go.

Cheers
JulieD
 
Julie:
It seemed to me that if I repeated the calculation then I
wouldn't be able to override the calculated amount when I
need to. However, I resolved this by just putting a
calculation box in the form with the estimated commission
which multiplies the commission rate by the transaction
amount. Then I either enter that amount in the
commission amount or any negotiated amount and that is
working fine. Thanks for all your help!
 
Back
Top