Solved Calculated Controls

S

silversun

Registered User.
Local time
Today, 03:38
Joined
Dec 28, 2012
Messages
204
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

Regards Ypma user not a professional
 
Saving calculations is not usually considered to be "good practice".
 
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 .

Respectfully Ypma
 
I only store the mileage, amount of fuel and the total cost and let Excel work out the price. Access will do the same thing. ?
 
Uncle Gizmo said:
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.
Click to expand...
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 .

Regards Ypma
 
Uncle Gizmo said:
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)

ID F1 F2 F3
1 1000 3 3000
2 5 5 25
3 4 2.5 1
4 56 67 3752
5 6 6 36
6 30 100 3000
7 8 1000 8000

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..
Click to expand...
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.

Regards Ypma
 
Pat Hartman said:
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.
Click to expand...
Can you please explain with an example. I was not able to create the desired query.
Thank you
 
Pat Hartman said:
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.
Click to expand...
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
 
ypma said:
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

Regards Ypma user not a professional
Click to expand...
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
 
silversun said:
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
Click to expand...
Silverson, I am assuming Uncle Gizmo's demo which I have tested is too complex for you to use in your database.
I am still not 100% what your exact requirement is but have had another attempt .
My demo assumes that two of the three fields are blank and one field has data by entering data in one of the blank fields, the calculation in the Afterupdate event will enter the result in the remaining blank field. I am using NULL so your blank fields must not have zero .

Hope this is a pointer as to where you need to be.

Ypma

After note faulty demo see post 36 , feed back would be appreciated
 
Last edited:
Uncle Gizmo said:
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)

ID F1 F2 F3
1 1000 3 3000
2 5 5 25
3 4 2.5 1
4 56 67 3752
5 6 6 36
6 30 100 3000
7 8 1000 8000

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

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

See updated example attached....
Click to expand...
Your example works very well. I need to implement it into my db now. Since I have too many other controls and don't want them to be included in this process then how do I use the tags? Could you please show me the modifications?
In your example I noticed once a new record added it goes to the first row of the table and takes the PK= 1 rather than appending to the end of the table. Is this on purpose or I can just remove that part?
Thank you
 
ypma said:
Silverson, I am assuming Uncle Gizmo's demo which I have tested is too complex for you to use in your database.
I am still not 100% what your exact requirement is but have had another attempt .
My demo assumes that two of the three fields are blank and one field has data by entering data in one of the blank fields, the calculation in the Afterupdate event will enter the result in the remaining blank field. I am using NULL so your blank fields must not have zero .

Hope this is a pointer as to where you need to be.

Ypma
Click to expand...
Hi,
I opened your db attached here but I couldn't save even one record properly. I guess you can see the issue on the picture. Here for instance I entered 8 as gas price in first filed and 2 for gas amount. Expecting to see 16 in total (third field) but it would display the first saved record in in first control and second control while saving the 8 in row number 8 of table. Please see the attached picture and help me to run this code once at least to see if it works for me.
1608400339017.png


Thank you
 
Pat Hartman said:

silversun​

Please confirm that you are OK with saving records that have 1 or none of the three data fields we are talking about in them. Records do NOT need all three to be complete. Just confirm and I'll go away.
Click to expand...
Hi Pat,
This is the situation: You have many invoices from gas stations and need to have all these in a table. Two records are necessary and enough to obtain the third one. Although user have the third one printed on the receipt but we don't use all three (to save time) because we can always calculate the third one as user enters the first two. Therefore it IS NOT OK to save a record with only one field. I need to have three fields saved in each record.
BTW, I could not find the demo you had as you mentioned "use a bounded query". Can you please link me?

Thank you very much
 
Uncle Gizmo said:
My assumption is you have transferred my code into your solution and you are getting this error. If you run my example, the error you describe does not occur.
Click to expand...
I employed your code in my db and the only thing I changed was the name of the fields. You had F1, F2 & F3 but my names were Gprice, Gamount & Gtotal. In your code you looked the controls that have F as first letter of their name where as in my code I looked for G.
After all it is not still working. Here is your code after I changed the names:

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) = "G" 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 "Gprice"
                            Me.Gprice = Me.Gtotal / Me.Gamount
                            
                            Case "Gamount"
                            Me.Gamount = Me.Gtotal / Me.Gprice
                        
                            Case "Gtotal"
                            Me.Gtotal = Me.Gprice * Me.Gamount
                        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) = "G" 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.Gprice = 0
    Me.Gamount = 0
    Me.Gtotal = 0
End Sub

Please check and let me know whats wrong.
 
silversun said:
Hi,
I opened your db attached here but I couldn't save even one record properly. I guess you can see the issue on the picture. Here for instance I entered 8 as gas price in first filed and 2 for gas amount. Expecting to see 16 in total (third field) but it would display the first saved record in in first control and second control while saving the 8 in row number 8 of table. Please see the attached picture and help me to run this code once at least to see if it works for me.
View attachment 87644

Thank you
Click to expand...
Error noted and will rectify ASP

Ypma
 
silversun said:
Hi,
I opened your db attached here but I couldn't save even one record properly. I guess you can see the issue on the picture. Here for instance I entered 8 as gas price in first filed and 2 for gas amount. Expecting to see 16 in total (third field) but it would display the first saved record in in first control and second control while saving the 8 in row number 8 of table. Please see the attached picture and help me to run this code once at least to see if it works for me.
View attachment 87644

Thank you
Click to expand...
I am in the process of modelling my example on Uncle's solution. I have got it to work ,it only a demo so error handling not yet completed, also i have placed the call of the function in the after update event ,you might move to to the before update event as suggest by Pat. Let me know how you get on.
Have updated my demo
Regards Ypma
 

Attachments

Last edited:
Pat Hartman said:
Silverman, you didn't understand my question. I've said this more than once but I'll say it again. The code you are using will NOT prevent you from saving incomplete records. ARE YOU OK WITH THAT???????
Click to expand...
@ Pat
I am not OK with that. I need complete and valid data being saved in my table.
Sorry if I was not in the right track. I knew what you were asking but didn't answer properly.
Thank you for following up anyways
 
Last edited:
You must log in or register to reply here.

Similar threads

D
Solved Display Less Decimal Point Accuracy To What Is Stored In Calculated Field Of Query
Replies
6
Views
957
gemma-the-husky
gemma-the-husky
C
Subform Footer Textbox wont update
Replies
9
Views
1,014
Gasman
Gasman
D
After update
Replies
15
Views
1,570
dullster
D
D
Invalid value error-dropdown
Replies
8
Views
679
Danny
D
P
Calculated Field Error
2 3
Replies
40
Views
3,505
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom