How to fill a bound textbox automatically?

[John Sheppard]

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

[Allen Browne]

Re: How to fill a bound textbox automatically?

Use the AfterUpdate event procedure of the controls it depends on. For
example:

Private Sub price_AfterUpda te
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" <jtsheppardHome @nobosospam.com .au> wrote in message
news:3f5fde2b$0 $4189$afc38c87@ news.optusnet.c om.au...[color=blue]
> 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)[/color]
(or[color=blue]
> 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
>
>[/color]

[Rick Brandt]

Re: How to fill a bound textbox automatically?

"John Sheppard" <jtsheppardHome @nobosospam.com .au> wrote in message
news:3f5fde2b$0 $4189$afc38c87@ news.optusnet.c om.au...[color=blue]
> 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[/color]

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.

[David W. Fenton]

Re: How to fill a bound textbox automatically?

abrowne1_SpamTr ap@bigpond.net. au (Allen Browne) wrote in
<SnU7b.94060$bo 1.61799@news-server.bigpond. net.au>:
[color=blue]
>Use the AfterUpdate event procedure of the controls it depends on.
>For example:
>
>Private Sub price_AfterUpda te
> Me.[SomeControl] = Me.price + Me.tax
>End Sub[/color]

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.
[color=blue]
>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.[/color]

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

[Allen Browne]

Re: How to fill a bound textbox automatically?

"David W. Fenton" <dXXXfenton@bwa y.net> wrote in message
news:93F39BAB3d fentonbwaynet@2 4.168.128.78...[color=blue]
> 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.[/color]

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

[Bob Quintal]

Re: How to fill a bound textbox automatically?

"Allen Browne" <abrowne1_SpamT rap@bigpond.net .au> wrote in
news:jsv8b.9828 7$bo1.30767@new s-server.bigpond. net.au:
[color=blue]
> "David W. Fenton" <dXXXfenton@bwa y.net> wrote in message
> news:93F39BAB3d fentonbwaynet@2 4.168.128.78...[color=green]
>> 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.[/color]
>
> 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.[/color]
[color=blue]
>
> 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.[/color]

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.
[color=blue]
>
> 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.
>[/color]
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. .