By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,625 Members | 1,283 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,625 IT Pros & Developers. It's quick & easy.

How to fill a bound textbox automatically?

P: n/a
Hi there

Does anyone know how to fill a bound textbox automaticlly.

In an unbound textbox I would put in the control source =Sum(price, tax) (or
some such function) and access updates it automaticlly.

Is there a way to do the same thing on a bound textbox so that it is
automaticly updated?

Thank you kindly for your efforts
Have a great day!
John Sheppard
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Use the AfterUpdate event procedure of the controls it depends on. For
example:

Private Sub price_AfterUpdate
Me.[SomeControl] = Me.price + Me.tax
End Sub

If what you are trying to do is as simple as that example, there is a better
way yet. Remove the calcuation from the table, and put it in a query. In the
query, enter a calculated field like this into the Field row of the grid:
Amount:[price]+[tax]
Now use the query as the RecordSource for your form. The Amount field takes
care of updating itself.

The best part is that since the calculation is done when needed, you never
have to worry about whether the Amount field in your table could be wrong.
This principle - not storing dependant data - is one of the basic rules of
data normalization.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"John Sheppard" <jt************@nobosospam.com.au> wrote in message
news:3f**********************@news.optusnet.com.au ...
Hi there

Does anyone know how to fill a bound textbox automaticlly.

In an unbound textbox I would put in the control source =Sum(price, tax) (or some such function) and access updates it automaticlly.

Is there a way to do the same thing on a bound textbox so that it is
automaticly updated?

Thank you kindly for your efforts
Have a great day!
John Sheppard

Nov 12 '05 #2

P: n/a
"John Sheppard" <jt************@nobosospam.com.au> wrote in message
news:3f**********************@news.optusnet.com.au ...
Hi there

Does anyone know how to fill a bound textbox automaticlly.

In an unbound textbox I would put in the control source =Sum(price, tax) (or
some such function) and access updates it automaticlly.

Is there a way to do the same thing on a bound textbox so that it is
automaticly updated?

Thank you kindly for your efforts
Have a great day!
John Sheppard


Sounds like you're attempting to store a calculated value. There is seldom a good
reason to do this. Just use an expression on your forms, reports, and queries to do
the calculation on-the-fly and remove the field from your table.
Nov 12 '05 #3

P: n/a
ab***************@bigpond.net.au (Allen Browne) wrote in
<Sn*******************@news-server.bigpond.net.au>:
Use the AfterUpdate event procedure of the controls it depends on.
For example:

Private Sub price_AfterUpdate
Me.[SomeControl] = Me.price + Me.tax
End Sub
Another alternative way of doing the same thing is:

Me!SomeControl = Me!price + Me!tax

I recommend against using the dot operator for anything other than
form properties and methods, and using the ! operator for controls
and fields in the underlying recordset.
If what you are trying to do is as simple as that example, there
is a better way yet. Remove the calcuation from the table, and put
it in a query. In the query, enter a calculated field like this
into the Field row of the grid:
Amount:[price]+[tax]
Now use the query as the RecordSource for your form. The Amount
field takes care of updating itself.

The best part is that since the calculation is done when needed,
you never have to worry about whether the Amount field in your
table could be wrong. This principle - not storing dependant data
- is one of the basic rules of data normalization.


In regard to tax, I think it's always a good idea to store the tax
amount in the record, as opposed to the tax *rate*, because when
you calculate the tax amount from the rate, you may have rounding
errors that will have to be addressed every time you calculate the
total. If you store the tax *amount*, you do the rounding only
once.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #4

P: n/a
"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.78...
In regard to tax, I think it's always a good idea to store the tax
amount in the record, as opposed to the tax *rate*, because when
you calculate the tax amount from the rate, you may have rounding
errors that will have to be addressed every time you calculate the
total. If you store the tax *amount*, you do the rounding only
once.


I've always store the amount rather than the rate, and for the same reasons.
Recently I've begun to question whether this is still justified.

Strictly, we are denomalizing when we store the amount of tax, as it is
dependant on the amount of the transaction. As processor power increases,
the number of cases where denormalization makes sense decreases. What we did
in dBase III on a PC with no hard disk and 128*K* of RAM is not always
appropriate today.

Storing the tax rate and calcuating on the fly means:
1. Store the rate per row, since countries have specific items that are
tax-ex.
2. Use a query as the source for forms/reports, to include the calculated
field (tax amount).
3. The calculated field must be explicitly typecast to Currency. (*No*
calculated field can be trusted without typecasting.)
4. Currency cannot be Null, so the calcuation must involve Nz().
5. The row must be rounded to prevent apparent addition errors if the client
wants to see the tax-inc amount on each row.

Result:
TaxAmount: Round(CCur(Nz([Quantity] * [UnitPrice] * [TaxRate],0)), 2)

The question is whether processor speed now justifies performing such a
calcuation at every row of the invoice in preference to storing the
denomalized tax amount. Guess we should run some timing trials to find out.
Variables would include processor type/speed, local verses networked, JET
verses MSDE.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Nov 12 '05 #5

P: n/a
"Allen Browne" <ab***************@bigpond.net.au> wrote in
news:js*******************@news-server.bigpond.net.au:
"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.78...
In regard to tax, I think it's always a good idea to store
the tax amount in the record, as opposed to the tax *rate*,
because when you calculate the tax amount from the rate, you
may have rounding errors that will have to be addressed every
time you calculate the total. If you store the tax *amount*,
you do the rounding only once.
I've always store the amount rather than the rate, and for the
same reasons. Recently I've begun to question whether this is
still justified.


Strictly, we are denomalizing when we store the amount of tax,
as it is dependant on the amount of the transaction. As
processor power increases, the number of cases where
denormalization makes sense decreases. What we did in dBase
III on a PC with no hard disk and 128*K* of RAM is not always
appropriate today.
Since the rate may vary with date, category of merchandize, as well
as other exemptions you are not denormalizing until you store all
of the variables in the calculation as well as the result. Whether
you store the rate, and calculate the value, or calculate then
store the value, you still require one field. If you store the
value you can determine the rate.

Storing the tax rate and calcuating on the fly means:
1. Store the rate per row, since countries have specific items
that are tax-ex.
2. Use a query as the source for forms/reports, to include the
calculated field (tax amount).
3. The calculated field must be explicitly typecast to
Currency. (*No* calculated field can be trusted without
typecasting.) 4. Currency cannot be Null, so the calcuation
must involve Nz(). 5. The row must be rounded to prevent
apparent addition errors if the client wants to see the
tax-inc amount on each row.

Result:
TaxAmount: Round(CCur(Nz([Quantity] * [UnitPrice] *
[TaxRate],0)), 2)

The question is whether processor speed now justifies
performing such a calcuation at every row of the invoice in
preference to storing the denomalized tax amount. Guess we
should run some timing trials to find out. Variables would
include processor type/speed, local verses networked, JET
verses MSDE.

I don't feel its worth the effort to save essentially 0 bytes of
disk space, since you need to store the rate per row in order to
make the calculation or the result of the calculation. .
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.