Refreshing when value is changed after initial entry

[CW]

On my charges calculation form I have a "NetAmount" control, then a combo to
select a "TaxCode" (which looks up in my TaxTypes table to find the
applicable rate), and in the After Update event of this I have a macro that
calculates the "TaxAmount" and finally this is added to "NetAmount" to
provide "Total".

I've got everything working nicely except the scenario when either the
"NetAmount" or "TaxCode" are subsequently changed, after the initial entries
and calculations have been performed.

I know it's something along the lines of Requery or Refresh but I can't work
out quite how to code this....what do I need to add, and where, please?
Many thanks
CW

 

[Jeff Boyce]

I'm not exactly clear on what you have set up, but it sounds like you
already have a function/procedure that does the calculation you need.

If so, then why not just "call" it when there's a change in any of the
controls that are involved? You could convert the function from one that
only runs in the AfterUpdate event of one control to a general function
"behind" the form that gets called from any/every control that is involved.

Or have I misunderstood your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Al Campagna]

CW,
Need more info...

1. Please show/describe the macro used in the AfterUpdate of TaxCode.

Is Total a bound field? Sounds like it from your post.
You shouldn't be saving the calculated amount to your table. Since you
have captured all the elements of the calculation, you should just "display"
the Total in an unbound text control, on any subsequent form, query, or
report.

2. If Total is an unbound calculated field, please show any calculation
you have in the ControlSource.

To clarify using an example...
PRICE would be a bound field
QTY would be a bound field
PreTaxTotal would be a calculated unbound field
= Price * Qty
TAX would be a bound field
Total would be an calculated unbound field
= PreTaxTotal + (PreTaxTotal * TAX)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[CW]

Jeff -
I'm sorry, I didn't describe things correctly. The problem arises when an
entry has been drafted but then needs to be amended, and the "NetAmount"
alone is changed, and the "TaxCode" is not changed. Because the macro is
attached to an event within "TaxCode" and this hasn't been altered, the
calculation isn't triggered so the tax amount isn't re-calculated based on
the new NetAmount.
If I put the macro in OnChange in "NetAmount" it would deal with this issue,
but it won't ever work on the initial input because a TaxCode hasn't yet been
selected.
I'm sorry I'm not explaining all this as clearly as I would like but it's
been a long day here at the office in UK and the grey matter is running out
of oomph.
Thanks again
CW

 

[Al Campagna]

CW,
To add to what Jeff is suggesting...

First, your calculation macro should be run on the AfterUpdate event of
any variable element of the calculation, not the OnChange event.
OnChange fires on every character entered in that field.
AfterUpdate only fires when the field value has been completely entered,
and after the changed data in a control or record is updated.

Call your macro on the AfterUpdate event of NetAmount, and Tax, and any
other field directly involved in the calcualtion.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Albert D. Kallal]

Jeff -
I'm sorry, I didn't describe things correctly. The problem arises when an
entry has been drafted but then needs to be amended, and the "NetAmount"
alone is changed, and the "TaxCode" is not changed. Because the macro is
attached to an event within "TaxCode" and this hasn't been altered, the
calculation isn't triggered so the tax amount isn't re-calculated based on
the new NetAmount.
If I put the macro in OnChange in "NetAmount" it would deal with this
issue,
but it won't ever work on the initial input because a TaxCode hasn't yet
been
selected.

Sure it can work just puts and some code that handle the situation where
neither the fields is yet set.

eg:

if isnull(TaxCode) then exit function

if isnull(NextAmount) then exit function

somecontorl = NetAmount * bla bla bal

 

[CW]

Thanks for your help, Jeff - I think it's OK now
CW

 

[CW]

Al - Many thanks for your help and explanation - looks like we're OK now
CW