Calculated control

[jgnasser]

I have a form that I�m using to enter data into a table. One of the fields in my table is storing area measurements. The users have areas in different units of measurement (Ha, Acres and square meters). I intend to allow the user to enter the area in any unit but to specify in a separate combo box what units they are using. In order to ensure my field has uniform measurements, I have created an invisible calculated text box which calculates the correct area units by using the value entered and the combo box value using this entry into its ControlSource =[txtArea]*[cboUnits].column(1). This works great and does the conversion correctly but how do I get the converted value into my table?

I know the other way would be to use a query to calculate the values later but I figured converting on the fly would be a better idea. Please help.

 

[dcx693]

You can get the converted value into your table by creating a field for it, then setting the controlsource of your invisible control to the name of that newly created field.

Most users would say that it's better to calculate the value later in a query. Everytime you view the record in a form, you'd be recalculating the value (or at least you should be - what if a user changes a previously entered value?).

 

[jgnasser]

Its the controlsource of the invisible text box that I have set to =[txtArea]*[cboUnits].column(1) therefore I dont know how to bind it to a field. The textbox into which the user types the area is not bound, it just holds the value before its converted to the correct area units and (supposedly) stored in a table.

The only way a user can change the value is by using the form, the user does not ever interact with the table directly.

If my approach doesnt work then I will calculate the value later in a query.

 

[dcx693]

OK, if you're setting the controlsource using a formula, then you cannot bind the field to the table...that much you know.

What I would do instead is set the value of the control using some VBA code. When the txtArea field or the cboUnits field changes it's value, use VBA code like this (in the AfterUpdate events of both fields) to set the value of the invisible text box:
Me.txtInvisible=Me.txtArea * Me.cboUnits.Column(1). Now, you can bind the field to the table field in the controlsource and it will be stored when the record is saved.

 

[jgnasser]

Thanks, worked like magic. Will tread carefully knowing what might happen if someone ever edited the table directly. Glad I now know how to do it.