Okay, here is one that I'm struggling with. I have a form used for data entry. On that form, one of the fields control source is set to do a calculation to give me a ratio. I noticed this value now doesn't go into my data table. So, I created a text box, and want to set that value to be equal to what the calucalated ratio comes out to in the other field. Any suggestions????
Two Fields on one form made equal (1 Viewer)
- Thread starter Jul
- Start date
Okay, here is one that I'm struggling with. I have a form used for data entry. On that form, one of the fields control source is set to do a calculation to give me a ratio. I noticed this value now doesn't go into my data table. So, I created a text box, and want to set that value to be equal to what the calucalated ratio comes out to in the other field. Any suggestions????
CraigDolphin
GrumpyOldMan in Training
- Local time
- Yesterday, 22:59
- Joined
- Dec 21, 2005
- Messages
- 1,582
So you are wanting to store a calculated value in a table? If you do a search on the forum for 'storing calculated values' you will probably find out that this is /usually/ not a good thing to do since, if the value is calculated based on data in other fields, you can recalculate the value in a query or in a control on a form any time you want to.
The main exception to this rule is if you want calculate something from information in other fields that will change over time and you need to preserve the originally calculated value.
For example: A ratio of length to width can always be calculated in a query using the expression...
MyRatio: [Length]/[Width]
As long as the length measure and width measure data will not change for that item, you probably shouldn't store the ratio and bloat your db when you can calculate it as needed.
On the other hand...
MyPrice can be calculated by the expression:
MyPrice: [UnitPrice]*[UnitQuantity]
but because unit prices could change over time, and historical sales information needs to reflect prices at the time of sale, it would probably be appropriate to store the original calculated result for later reference.
If you still think you need to store the calculated value, then bind a textbox (txtRatio) on your form to the field in your table where you want to store the value. Then use vba in the after update events of your other fields to first check that all the values necessary to your calculation have been entered for that record and, if they have, then calculate the value and use Me.txtRatio = {your calculation here}.
The main exception to this rule is if you want calculate something from information in other fields that will change over time and you need to preserve the originally calculated value.
For example: A ratio of length to width can always be calculated in a query using the expression...
MyRatio: [Length]/[Width]
As long as the length measure and width measure data will not change for that item, you probably shouldn't store the ratio and bloat your db when you can calculate it as needed.
On the other hand...
MyPrice can be calculated by the expression:
MyPrice: [UnitPrice]*[UnitQuantity]
but because unit prices could change over time, and historical sales information needs to reflect prices at the time of sale, it would probably be appropriate to store the original calculated result for later reference.
If you still think you need to store the calculated value, then bind a textbox (txtRatio) on your form to the field in your table where you want to store the value. Then use vba in the after update events of your other fields to first check that all the values necessary to your calculation have been entered for that record and, if they have, then calculate the value and use Me.txtRatio = {your calculation here}.
It is something the operators out on the production floor need to view as well as save for each of their entries. It changes with each entry each day. I tried what you suggested, and still can't seem to get it to make any sense or work. Not sure what I'm doing wrong.
CraigDolphin
GrumpyOldMan in Training
- Local time
- Yesterday, 22:59
- Joined
- Dec 21, 2005
- Messages
- 1,582
Neither am I!
The result of the calculation might be different each day, but if the data associated with each day doesn't change then you can calculate the value anytime you want.
Have a look at the attached example. This shows you two ways to calulate values in unbound controls. And it also calculates and stores the value in a field in the table as described above. There's also a query showing how you can calculate your ratio in that situation also. (it checks for zero denominators etc too)
The second method uses the after-update events of the two data fields to set the value in the unbound text box, and the on_current event also for when you navigate to a different records. If you use the events to set the value in a bound field (txtRatio3) then the value is stored in that field.
The first textbox simply places the equation in the control source, but navigate to a new record to see what happens if you only enter a partial record.
