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
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
If you want simpler code, use the BeforeUpdate event of the FORM. At that point you know that all the fields that will be entered have been entered. That allows you to raise an error and prevent the record from being saved if at least two of the values have not been entered. Doing this calculation as you envision it field by field means that you you have to ignore errors and that is ultimately very dangerous. I haven't checked Uncle's code. I'm going to assume it works to flag the errors since most experts who post code check it first but it is not doing anything to prevent invalid records from being saved. It can't if you expect it to run field by field because it has to ignore errors. That means that people can simply blow by the error messages and save the bad data anyway.
And finally, if you use Uncle's code and you want to do it as the data is being entered (I don't recommend this due to having to ignore errors), you should do one final check in the Form's BeforeUpdate event just to make sure that all of the fields have been entered or calculated. That check, if you are doing the field by field check, should simply verify that all three fields are present. If they are are not ALL present, cancel the update to prevent the record from being saved.
The technique the script demonstrates is to loop through ALL of the Form controls. It's a very useful technique, which I'm sure you will be able to employ in many situations once you get your head around how it works.
I highlighted "All" because there are several gotcha's that can catch you out when using this technique. Because the code operates on all of the controls, then you may find your code tries to operate on a particular property which the control does not have, doing this will cause issues. That's why the Select Case Statement that selects the particular type or types of control the code works on is important.
Another technique is to identify a group of controls to work on, by adding a flag to the controls Tag property, or as in this example, use a specific prefix to the controls name.
Hi Pat,
My solution is designed to tackle the problem described by silversun in post #18
>>>receipts one of the values faded, other two readable<<<
Entering any 2 values calculates the obscured one.
As Bob Fitz said right at the beginning, it's not a good idea to store a calculated result.
I think that a suitable modification to the OP's database would be to remove the result field, only calculate it from the price per litre X the amount when required.
This modification would correct the issue you have spotted in that there is the possibility of invalid records being saved.
the reason the poster gave was at least rational. I don't like it but OK. Now he thinks he has a solution, but he doesn't since invalid records will be saved.
Yes, but only cosmetically! I removed some redundant code and I added a command button to set the fields to 0 because it was impossible to change the results once all three fields had been filled.
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
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 ..
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
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.
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.
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.
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.
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.
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?
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.
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
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
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
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
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???????
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???????
@ 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
