Hi,
I have a simple bounded form. There are three controls (A, B & F) in my form bonded to three fields of a table.
A = Gas price $/litter
B = Amount of gas, litter
F = Total value of the gas
F = A x B
Assume user enters the gas price (A) in the first control and amount of gas in the second one (B). A simple VBA can calculate (multiply) these two numbers and send the result to the last control (F). My question here is how to write a VBA code or query that always calculates the third value out of any two given numbers. I mean if user enters the total amount (F) and the gas price per litter (A) then the code will calculate the amount of gas (B) which is B=F/A.
It should always calculate the third operand once two of them are entered.
F=A*B
A=F/B
B=F/A
I think it is clear enough. Please let me know if it isn't.
Masoud
Thank you for your help
I tried a command button to obtain the three different results .
Code:
Private Sub Command3_Click()
If (IsNull([ValueofGas])) Then
ValueofGas = amountGas * Gasprice
ElseIf (IsNull([Gasprice])) Then
Me.Gasprice = Me.ValueofGas / Me.amountGas
ElseIf (IsNull([amountGas])) Then
Me.amountGas = Me.ValueofGas / Me.Gasprice
End If
End Sub
Not sure how you wish to trigger the desired outcome
I agree with Bob you shouldn't be doing this! Because you are storing data in the table, and any changes to that data will damage the integrity of your data. Having said that, I fancied a bit of a challenge this morning so I had a go and here's my solution:-
The simplest solution is to bind the form to a query rather than the table and that will allow you to have a calculated field
Select A, B, A * B as F
From yourtable.
F will automagically change when you change the value of A or B. It will not be saved. It does not need to be as the others have pointed out. Just calculate it in your query whenever you need it.
Pat ,my understanding of the question was that result of any one of the three fields in question could be calculated using the data from the other two fields . You state that F will automatically change but , no mention of other scenario where F and A are given to provide B . Does your solution cover all three calculation. ? is so I would appreciate and example of how this would work without having any of the fields bound.to a table .
Your original question, (which I must say was very clear and well written which was one of the reasons i provided an answer) stated that the controls are bound. I am now confused.
Your original question, (which I must say was very clear and well written which was one of the reasons i provided an answer) stated that the controls are bound. I am now confused.
Uncle Gizmo, thank you for the courtesy of replying , you are confused as I did not pose the original question but tried with my limited knowledge to provide a solution. The reasons why silverson wanted the scenario is not clear to me. Regretfully I could not get your example to work , yes you were clear about the controls being bound in your demo. Maybe I am missing something but, could this scenario be an exception to rule concerning calculated fields , we are not talking a about A * B = C , but the permutation of 3 possible results .
I'm not sure why the solution I provide doesn't work for you. Possibly you are using an earlier version of Microsoft Access. If that's the case, these are the instructions for recreating my solution.
You need a table with the following Fields, as number - double format, (accept for "ID" which is an auto number)
You need a form with 3 text boxes named F1 F2 and F3. These text boxes should be linked to the corresponding fields in the table.
Then you will need the following code in the forms code module:-
Code:
Option Compare Database
Option Explicit
Private Function fFill_MT_Field()
Dim intCtrlFilledCount As Integer
Dim Ctrl As Control
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acTextBox
If Left(Ctrl.Name, 1) = "F" Then
If Ctrl.Value > 0 Then
intCtrlFilledCount = intCtrlFilledCount + 1
End If
End If
End Select
Next Ctrl
If intCtrlFilledCount = 1 Then
MsgBox ">>> " & "Need to Fill Another"
Exit Function
End If
If intCtrlFilledCount = 3 Then
MsgBox ">>> " & "DO Nothing, ALL Filled"
Exit Function
End If
If intCtrlFilledCount = 2 Then
Select Case fGetCtrlZero
Case "F1"
Me.F1 = Me.F3 / Me.F2
Case "F2"
Me.F2 = Me.F3 / Me.F1
Case "F3"
Me.F3 = Me.F1 * Me.F2
End Select
Exit Function
End If
End Function 'fFill_MT_Field
Private Function fGetCtrlZero() As String
Dim Ctrl As Control
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acTextBox
If Left(Ctrl.Name, 1) = "F" Then
If Ctrl.Value = 0 Then
fGetCtrlZero = Ctrl.Name
End If
End If
End Select
Next Ctrl
End Function 'fGetCtrlZero
Private Sub btnReset_Click()
Me.F1 = 0
Me.F2 = 0
Me.F3 = 0
End Sub 'btnReset_Click
If you were to recreate this example in your particular version of MS Access and post it on the forum, it would be most helpful to others..
Update:-
I've cleaned up the code, added a reset button, changed the afterupdate events to call the function directly ..
I'm not sure why the solution I provide doesn't work for you. Possibly you are using an earlier version of Microsoft Access. If that's the case, these are the instructions for recreating my solution.
You need a table with the following Fields, as number - double format, (accept for "ID" which is an auto number)
You need a form with 3 text boxes named F1 F2 and F3. These text boxes should be linked to the corresponding fields in the table.
Then you will need the following code in the forms code module:-
Code:
Option Compare Database
Option Explicit
Private Function fFill_MT_Field(oText As TextBox) As String
Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "
Dim strSubName As String
Dim strModuleName As String
strSubName = "fFill_MT_Field"
strModuleName = "Form - " & Me.Name
'strModuleName = "Module - basModuleName"
On Error GoTo Error_Handler
Dim intCtrlFilledCount As Integer
Dim Ctrl As Control
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acTextBox
If Left(Ctrl.Name, 1) = "F" Then
If Ctrl.Value > 0 Then
intCtrlFilledCount = intCtrlFilledCount + 1
End If
End If
End Select
Next Ctrl
If intCtrlFilledCount = 1 Then
MsgBox ">>> " & "Need to Fill Another"
Exit Function
End If
If intCtrlFilledCount = 3 Then
MsgBox ">>> " & "DO Nothing, ALL Filled"
Exit Function
End If
If intCtrlFilledCount = 2 Then
Select Case fGetCtrlZero
Case "F1"
Me.F1 = Me.F3 / Me.F2
Case "F2"
Me.F2 = Me.F3 / Me.F1
Case "F3"
Me.F3 = Me.F1 * Me.F2
End Select
Exit Function
End If
Exit_ErrorHandler:
'adoCon.Close
'Set adoCon = Nothing
'Set adoCmd = Nothing
Exit Function
Error_Handler: 'Version - 1a
Dim strErrFrom As String
Dim strErrInfo As String
strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
Select Case Err.Number
Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
Case Else
MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
End Select
Resume Exit_ErrorHandler
End Function 'fFill_MT_Field
Private Function fGetCtrlZero() As String
Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "
Dim strSubName As String
Dim strModuleName As String
strSubName = "fGetCtrlZero"
strModuleName = "Form - " & Me.Name
'strModuleName = "Module - basModuleName"
On Error GoTo Error_Handler
Dim Ctrl As Control
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acTextBox
If Left(Ctrl.Name, 1) = "F" Then
If Ctrl.Value = 0 Then
fGetCtrlZero = Ctrl.Name
End If
End If
End Select
Next Ctrl
Exit_ErrorHandler:
'adoCon.Close
'Set adoCon = Nothing
'Set adoCmd = Nothing
Exit Function
Error_Handler: 'Version - 1a
Dim strErrFrom As String
Dim strErrInfo As String
strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
Select Case Err.Number
Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
Case Else
MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
End Select
Resume Exit_ErrorHandler
End Function 'fGetCtrlZero
Private Sub F1_AfterUpdate()
Call fFill_MT_Field(F1)
End Sub
Private Sub F2_AfterUpdate()
Call fFill_MT_Field(F2)
End Sub
Private Sub F3_AfterUpdate()
Call fFill_MT_Field(F3)
End Sub
If you were to recreate this example in your particular version of MS Access and post it on the forum, it would be most helpful to others..
Uncle Gizmo, I revisited your demo and when I tabbed to a new record it produce the required results , I was trying change the figures on the records your created , with no results and assumed it was not liking my version. I was impressed with your script and will learn from it, for any of my future endeavours . Thank again and keep safe.
I ignored the over-the-top situation. Unless there is some really strange situation, pick two of the three values, make those required, and always calculate the third. I would be surprised if the user actually needed this flexibility. If he does, Uncle was very kind and provided code which I'm assuming works.
I think I've got lockdown itis. I'm bored out of my skull, I am snapping at everybody for no reason. I have to be careful what I say to people, even on the forum here! I find it necessary to answer the interesting questions here because it helps me maintain what little sanity I have left.
The simplest solution is to bind the form to a query rather than the table and that will allow you to have a calculated field
Select A, B, A * B as F
From yourtable.
F will automagically change when you change the value of A or B. It will not be saved. It does not need to be as the others have pointed out. Just calculate it in your query whenever you need it.
I ignored the over-the-top situation. Unless there is some really strange situation, pick two of the three values, make those required, and always calculate the third. I would be surprised if the user actually needed this flexibility. If he does, Uncle was very kind and provided code which I'm assuming works.
Lets assume user needs to enter hundreds of gas receipts into a table. Some of them are old enough to have faded one of the values but other two are readable. In this situation one solution is to use a calculator and always obtain two operands and let a simple VBA calculate the total or have the form to do that in a smart way. That's why I need to find the simplest and most efficient way.
Thank you for helping me
I tried a command button to obtain the three different results .
Code:
Private Sub Command3_Click()
If (IsNull([ValueofGas])) Then
ValueofGas = amountGas * Gasprice
ElseIf (IsNull([Gasprice])) Then
Me.Gasprice = Me.ValueofGas / Me.amountGas
ElseIf (IsNull([amountGas])) Then
Me.amountGas = Me.ValueofGas / Me.Gasprice
End If
End Sub
Not sure how you wish to trigger the desired outcome
To trigger the function my goal was to use "GotFocus" or "AfterUpdate" or "Change" or similar attributes of three controls. Once user starts writing the number of any of three controls function should start in the background and as soon as he enters the second value (total or one of the operands) it calculates the third one. It is best if it displays the third value in its according control simultaneously. At the end when user moves to the next new record all numbers should be saved in the table.
Thank you for your help
Once user starts writing the number of any of three controls function should start in the background and as soon as he enters the second value (total or one of the operands) it calculates the third one
