In a form how do you set up a circular calculation for text box 1from that entered in text box 2 and / or the other way around
I have a form where sample analytical values are posted to table t_Samples. The user gets a report from a lab where sample data percentages are either expressed on an as-is (water included) basis or on a dry (water excluded) basis. I want the user to be able to enter the water % of the sample (always given) and then enter either the as-is or the dry sample percentages depending on how data has been provided.
For example: A sample that is 10% water (90% dry) with an as-is protein content of 20% (20% of the weight - including water weight - is protein) would have a dry protein content of 0.2 / (1-0.1) = 22.22%. A sample that is 10% water with a dry protein content of 20% (20% of the dry weight - no water weight included - is protein) would have an as-is protein content of 0.2 * (1-0.1) = 18%
I would like the user to be able to put values for protein in either of two text boxes and have the form calculate the other based on the water % of the sample. The form then submit just the as-is value to the table together with the water content.
Conceptually the calculation is circular, but the user would only enter one of the values (dry or as-is), the other would be calculated based on what data was provided and the water content. You would not know which value the user would have. I would like to display both (one entered, one calculated) once one has been filled in.
I would like to do this not using VBA if at all possible although if needed or appropriate I could use it (I'm a noob). I also have several sample constituents (protein, fat, ash, fiber) on the same form and all would have two text boxes (dry or as-is) side by side and a single sample water content for that sample.
Conceptually its similar to Celcius or Fahrenheit or English units vs Metric if you dont know which will be received. Any help would be appreciated.
For example: A sample that is 10% water (90% dry) with an as-is protein content of 20% (20% of the weight - including water weight - is protein) would have a dry protein content of 0.2 / (1-0.1) = 22.22%. A sample that is 10% water with a dry protein content of 20% (20% of the dry weight - no water weight included - is protein) would have an as-is protein content of 0.2 * (1-0.1) = 18%
I would like the user to be able to put values for protein in either of two text boxes and have the form calculate the other based on the water % of the sample. The form then submit just the as-is value to the table together with the water content.
Conceptually the calculation is circular, but the user would only enter one of the values (dry or as-is), the other would be calculated based on what data was provided and the water content. You would not know which value the user would have. I would like to display both (one entered, one calculated) once one has been filled in.
I would like to do this not using VBA if at all possible although if needed or appropriate I could use it (I'm a noob). I also have several sample constituents (protein, fat, ash, fiber) on the same form and all would have two text boxes (dry or as-is) side by side and a single sample water content for that sample.
Conceptually its similar to Celcius or Fahrenheit or English units vs Metric if you dont know which will be received. Any help would be appreciated.
ASKER
What would happen if data was entered in one, and then the user changed their mind and entered in the other. In other words once both textboxes have been populated (one with an data entry the other with a calculation) what happens of the user enters data over the calculated field?
ASKER
The solution offers a hierarchy of field entry that is not appropriate - any other ideas, am I asking too much from the program?
ASKER
Any ideas? Am I not being clear? Will the proposed solution actually work and I am too dumb to see it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the late reply I had given up on this one. That solution sounds promising, perhaps if I used a more obvious example. A form needs to accept data entry in either of two text boxes (same form): Metric and English. If the metric data box is populated (with a mouse and keyboard), then the English box would be calculated, if the English box was populated, the metric would be calculated. Once both boxes display something the user needs to be able come back to that record and enter data in either box (metric or English) and have the other change appropriately. The form would feed only one of the data box results (Metric) back to a table.
ASKER
Anyone else want to comment, would entry and subsequent reentry of text "fire" either (and the correct) calculation?
I'll review this later ... rough day, need to get some beer in me ;)
Function CalcPercentage() As Boolean
If Me.Textbox1 = 0 and Me.Textbox2 = 0 then
'/not enough info to proceed
Exit Function
End If
If Me.textbox1 > 0 Then
'/user entered a value in Textbox1; perform a calculation
Else
'/user entered a value in textbox2, perform a different calculation
End If
End Funciton
Now call CalcPercentages from your textbox AfterUpdate events:
Sub_Textbox1_AfterUpdate()
CalcPercentages
End Sub