Errors on Refresh or Save

[CW]

I have a form (subform actually) based on a table, with the following controls:
Net Value - entered directly here by the user
Tax Type - this is a combo based on a TaxTypes table holding Types and Rates
and when the type is selected there's an After Update event (using If
statements) that populates the next control with the appropriate Tax Rate
Tax Rate - read only, driven by the above
Tax Value - a calculated control that multiplies the Net Value by the Tax Rate
Gross Value - a calculated control, Net Value + Tax Value

Problem: I have tried all manner of combinations of AfterUpdate events on
controls and on the form, but cannot get the Tax Value control to display the
calculated amount automatically, or even when I did, it wouldn't then change
automatically if the Tax Rate was subsequently changed. And it's vital that
it does, because (against all best practice) this value has to be stored in
the table, as it is then picked up by the import process of our accounting
system. I know you will grimace uo reading that, but in this case there's no
way round it.
What happens is that if a record has been created with the standard tax rate
all is well and is saved properly to the table. But if the user then realises
that tax is not applicable, and changes the TaxType to Zero, the TaxRate
changes to 0 but the TaxValue control doesn't re-calculate.

And the Save button, created via the wizard for Save Record, produces an
error: "The DoMenuItem action was canceled" (N.B. mispelt, should have two
lls in it!!)

So I tried a Refresh the Record button instead. That does force the correct
calculation, but it throws the message: "The |action was canceled".

Then I tried a Refresh the Form button - that produces "Runtime error 2501 -
the | action was canceled".

The only way I have found to force the calculation cleanly is to use
Records>Refresh on the top Access menu/dropdown.

What's going wrong? And since it works, is there any way to reproduce the
code used by the Records>Refresh menu item?

Looking forward to your advice - many thanks
CW

 

[Klatuu]

How does the import process of your accounting system get to the data in the
Access mdb?

What you are experiencing is exactly why you hear us preach about storing
calculated values. (you are feeling the pain)

My point is, if we can satisfy the import process without storing the value,
you will be miles ahead.

The other question I would have to help resolve this issue is how to you
record whether the item should or should not be taxed?

 

[CW]

Dave -
Thanks for your queries in an effort to help on this, answers as follows:
1. There's a third-party DAO process that gets the data from my
tblInvoiceLines and presents it to Sage, the UK accounting package that we
use. I used to export the data by csv but had a few formatting problems and
this alternative is working much better. But the tax value has to be a
pre-calculated standalone piece of data in its own right residing in the
table from which the link pulls the data, that's why unfortunately I have to
store that calculated amount.
I don't believe the transfer process could handle a calculation along the
way, or even if it could, it would require a re-write which we really don't
want to get into, cost-wise.

2. The basis for the net amount being taxable or not is somewhat complex and
I have no idea if/how it could be written into the system. At present it is
determined by the user from their experience and their knowledge of the
various circumstances of each order. They use a combo (TaxCode) to select
from 4 possible tax options/rates, and the AfterUpdate of this combo feeds
the next control TaxRate. That should then multiply automatically against the
NetValue, and does do so first time round - it's only if an error is made and
the tax situation needs to be changed, that's when it goes wrong. The revised
TaxRate is populated, but the re-calculation of TaxValue does not take place
unless I do a Refresh, and that's when the error message appears.
Hope that clarifies it!
Thanks again
CW

 

[Klatuu]

Well, this is the first documented case I have encountered where a calculated
value actually should be stored in a table.

I think this is the problem you are having. Doing a Refresh or a Requery
will not cure the problem. This issue here is that a Calculated control will
only recalculate when I user enters a value in one of the controls involved
in the calculation. A programatic changes will not trigger a recalc.

I think that if you recalc the form in the After Update event of the Tax
Rate combo, it will fix the problem.

Me.Recalc

The recalc method causes all calculated controls on a form to recalculate.
It has to be done at the form level, it does not apply at the control level.

Let me know if that works or not, please.

 

[Larry Linson]

In most cases, DAO can Open a Recordset on either a Table or a Query. If
you have control of _that_, e.g., you call the DAO process and pass it the
Data Source for it to use, then you could create a Query containing the
Calculated Field.

On the other hand, interface specifications between Access and other
processes can, as Dave said, override our natural inclinations to adhere to
relational database design principles.

Larry Linson
Microsoft Office Access MVP

 

[CW]

Dave -
Very grateful for your suggestion but sad to say, it does not resolve the
problem. As a matter of fact I have tried chucking Me.Recalc at a whole range
of events but no good. It remains that the only successful method is via
Records>Refresh on the Access menu. It's a workaround, but not ideal, so if
you have any other thoughts I would really appreciate them. But you've
already spent a while helping me with this so don't worry if the inspiration
is running dry now!
Thanks again
CW