Table Update 1

[dpn2004]

Dear All:

I need to Update a field in my table while I am doing the data Entry.
Let me explain, this table has a field which carries Payment amount in Local currency and I have a another field which has exchange rate. in my third field I need to put a calculation saying [amt paid]/[exchange rate] but it didn't work as I expected. Please help me..!!!

thanks for your valueable knowledge

 

[Steve101]

Assuming that your two known controls are named [paid] and [exchange rate] respectively, then define the third control - lets name it [local value], and in design view, set its ControlSource property to:
[tt]
[red]=[paid]/[exchange rate][/red]
[/tt]

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

Forgot to add:
(a) Dont forget the equals sign at the beginning of the property
(b) I'm assuming above that exchange rate value will not be zero; otherwise you will need to use the iif() function to trap and handle this.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Edski]

dpn2004,
If your entering your data by using a table then firstly it is not possible, secondly you shouldn't be doing it this way in the first place.

If you've got a data entry form then it is quite easy. In the Control Source for the field just put = [amt paid]/[exchange rate]

Now then, this brings me to another point. Why are you storing this information anyway? You shouldn't be storing calculated values at all (unless you have a very good reason, such as the exchange rates varying over time - quite likely in your case).

Cheers.

 

[dpn2004]

Yes, it is a table which they usually do the data entry, but as you mentioned I created a form, and in control source specified the calculation but it won't update my table it will be perfectly displaying in the form,
How do I update the value in form on the table

thanks for your time

 

[Steve101]

As edski says, you should not have the third field in your table, as it is simply derived from the other two fields. This derivation can occur as the controlsource in forms, reports, and also as expressions in queries, so there is NEVER any need to actually physically store it in the table.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[RoyVidar]

As pointed out, you shouldn't! As long as the calculation easily could be performed on the fly, you should not store caclulated values. When (not if) the calculation at some time fails, you will get errors.

That said, create another text control (txtCalc) on the form, bind it to the field in question, and in some relevant event, perhaps the on current and the after update events of the controls holding the value that updates this (or perhaps the before update event of the form?) assign the value to this new control.

[tt]Me!txtCalc.Value = Me!txtControlWithCalculations.Value[/tt]

Roy-Vidar

 

[RoyVidar]

Oups - here's me interferring again;-) Must've forgot to refresh Steve101.

Roy-Vidar

 

[Edski]

How do I update the value in form on the table

As long as the control in the form is bound to the field in the table/query, it should automatically update.

But again, there is no need to store this calculated field in the table! One, it's redundant. Two, it's wasting valuable storage space.
Cheers.