Is it possible to bound form controls with record manually in visual basic? The access bound limited some functionality I need. For example, display modified data field, after change, write back in another calculated number.
Bound controls manual
- Thread starter benzhu
- Start date
- Local time
- Today, 17:14
- Joined
- Feb 19, 2002
- Messages
- 48,202
We generally don't store calculated values. Calculations should be done in the query so the results are always fresh.
I think you are asking if you can store a calculated value and the answer is yes. You need a control that is bound to the table field. Then in an appropriate control or form event, store the calculated value.
Me.CalcField = Me.Fld1 + Me.Fld2
I think you are asking if you can store a calculated value and the answer is yes. You need a control that is bound to the table field. Then in an appropriate control or form event, store the calculated value.
Me.CalcField = Me.Fld1 + Me.Fld2
what really want to do is: Store the absolute value in table - the data has wide range, from 0.95 to 1e-15. Normal user experience is like this: 32%, 5 ppm, 10 ppb. Access can only display like: 0.32, 0.000005, 0.00000001 - it will be mess if I have to ask user input data like this.
So what I need to do:
on load of record: convert data to 32 %, 5 ppm, 10 ppb (I have control for unit symbol and unit already.
When user change the value to e.g. 16 %, or 2.5 ppm, or 5 ppb - then like the data to be converted to absulote value (0.16, or 0.0000025, or 0.000000005) and store in record
Problem with access: no easy way to handle conversion in bound field (at least I have find solution yet.
So what I need to do:
on load of record: convert data to 32 %, 5 ppm, 10 ppb (I have control for unit symbol and unit already.
When user change the value to e.g. 16 %, or 2.5 ppm, or 5 ppb - then like the data to be converted to absulote value (0.16, or 0.0000025, or 0.000000005) and store in record
Problem with access: no easy way to handle conversion in bound field (at least I have find solution yet.
- Local time
- Today, 17:14
- Joined
- Feb 19, 2002
- Messages
- 48,202
There isn't a problem with the percent field. You just change the format. If the user enters 16, Access will "know" it is actually .16.
As for the others, there isn't any way to get rid of those significant zeros so you need to use unbound controls that you populate in the FORM's Current event and then you'll need code in the FORM's BeforeUpdate event of each of these fields to store the modified value. You could use the individual controls' AfterUpdate event but the FORM's BeforeUpdate event is less likely to cause a problem if the user cancels his update and since you don't need to modify the bound value until just before the record is saved, there is no timing issue with using the FORM's BeforeUpdate event.
As for the others, there isn't any way to get rid of those significant zeros so you need to use unbound controls that you populate in the FORM's Current event and then you'll need code in the FORM's BeforeUpdate event of each of these fields to store the modified value. You could use the individual controls' AfterUpdate event but the FORM's BeforeUpdate event is less likely to cause a problem if the user cancels his update and since you don't need to modify the bound value until just before the record is saved, there is no timing issue with using the FORM's BeforeUpdate event.
Thanks Pat - your suggestion is very helpful. Right now, I created redundant field to hold the redundant data to associated user input field. The actual data is calculated in control's after event - I probably change the update into form's before update event.
As you mentioned for unbounded control - it will be ok for "Single Form" - in my continue form (+it is a sub form inside another form) - the unbounded control became useless (shows the same data on all following records)
I thinks about to creat new temperal table to hold those calculated data - then delete the table after use. I still need to learn how to create table and query on fly - and check if it works in shared environment.
As you mentioned for unbounded control - it will be ok for "Single Form" - in my continue form (+it is a sub form inside another form) - the unbounded control became useless (shows the same data on all following records)
I thinks about to creat new temperal table to hold those calculated data - then delete the table after use. I still need to learn how to create table and query on fly - and check if it works in shared environment.
- Local time
- Today, 22:14
- Joined
- Sep 12, 2006
- Messages
- 16,121
I would show the "scale" as a small label behind the record
so in a field that is 2.5 ppm, store the value as 2.5 - but bear in mind that it needs to be divdied by 10^6 for calculation purposes.
maybe store the calculation factor in a setup table somewhere.
then it just looks like
[ 2.5] ppm
The is just equivalent to dealing in units that makes sense. eg, I presume medical use will tend to be 0.5mls of a substance, rather than 0.0005 litres.
so in a field that is 2.5 ppm, store the value as 2.5 - but bear in mind that it needs to be divdied by 10^6 for calculation purposes.
maybe store the calculation factor in a setup table somewhere.
then it just looks like
[ 2.5] ppm
The is just equivalent to dealing in units that makes sense. eg, I presume medical use will tend to be 0.5mls of a substance, rather than 0.0005 litres.
- Local time
- Today, 17:14
- Joined
- Feb 19, 2002
- Messages
- 48,202
I like Dave's solution. I didn't want to suggest it if the problem was localized to this one form. I have worked with applications that used it. I did an application for Clairol (hair care products) that calculated batch quantities for their shop floor. The dyes and fragrances were so small compared to the other components that we did exactly that. Rather than storing the very small things with 6 leading zeros, we stored them as numbers with two decimal places.
