Editable calculated expression

[kwatai]

First post, so go easy on me

I have a field in the table that needs to be filled up with American Dollars. However, the user is going to be seeing and entering canadian dollars. For the seeing part, I can have a calculated expression by muliplying the data. However, for the input part I'm stuck. Can't modify calculated fields!

So, I pretty much need a two way black box between the user and the table.

Any suggestions?
Thx
Kenta

 

[Pat Hartman]

I don't see any alternative. You'll need an unbound field that you caluclate a value for in the form's onCurrent event. Then in the field's AfterUpdate event, you'll need code to calculate a value to place in the table column.

in the Form_Current event
Me.txtYourUnboundControl = Nz(Me.tableField,0) / ConversionFactor

in the control's AfterUpdate event
Me.tableField = Nz(Me.txtYourUnboundControl,0) * ConversionFactor

 

[kwatai]

Yeah, i thought so. But from my understanding this thing really doesn't work when you've got a continuous form. It seems that all unbounded objects acts together, so all calculations are actually just the result of the first one. Plus, onCurrent applies to only the current form so unbounded boxes don't get updated until the you select it.

Oh well, thx for you help though...

Kenta

 

[Pat Hartman]

You didn't mention that you were using a continuous form in your original post or I would not have suggested the unbound text boxes. Regardless, I re-read the problem again and I think you actually need to store two out of the three fields, Canadian dollars, the conversion factor, US dollars. The reason for this is that conversion factors are good only at a point in time. So, if you store US dollars last week and you display the record this week, you'll see Canadian dollars at the current exchange rate rather than what was input last week. As long as you store two of the three columns (I would suggest both $ amounts for convenience), you can use bound fields and simply not show the US dollars amount on the form. As long as it is included in the form's recordsource you can still update it.

 

[kwatai]

Thats actually not a bad idea. Thx for the input.

And sorry for not mentioning the continuous form part... wanted to make the situation as simple as possible.

Kenta