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

Nanco_NRC
Nanco_NRC used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
I don't know the specific formulas, obviously, but from a code standpoint, you would be best to build a single function that calculates this, then branch IN THAT FUNCTION dependant on what your user has entered. For example:

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

Author

Commented:
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?

Author

Commented:
The solution offers a hierarchy of field entry that is not appropriate - any other ideas, am I asking too much from the program?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Any ideas? Am I not being clear? Will the proposed solution actually work and I am too dumb to see it?
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Sorry this one dropped off my radar ...

<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?>

Do you want the user to enter data over the calculated field? If not, just set the control to Locked = True or Enabled = False (or both) and the user wouldn't be able to enter anything in the field

<The solution offers a hierarchy of field entry that is not appropriate - any other ideas, am I asking too much from the program?>.

It's really just an example of how to "fire" a calculation based on data entry in a control ... you would, obviously, have to create your own code/forumla for this, since we really have no idea what your exact requirements are. Also, you may need to do more checks before running your code ... for example, if you want to do one calc if the user enters data in TextboxA, but a different one if they enter in textboxB:

If Me.TextboxA <> 0 Then
  "do some calculateion
ElseIf Me.TextBox B <> 0 Then
 '/do some other calculaton
End If

Author

Commented:
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.

Author

Commented:
Anyone else want to comment, would entry and subsequent reentry of text "fire" either (and the correct) calculation?
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'll review this later ... rough day, need to get some beer in me ;)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial