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.
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.
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???????
@ 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
You need to call the function from the FORM's BeforeUpdate event AND you need to cancel the event if the data is not valid. As I said, I would have done this differently. Uncle should be able to fix the problem now that he knows there is one.
Pat, in my demo post 36 I have call the function in the after update event and entered the following in the FORMS before the update event , should this suffice or am I missing something ? As a user and not a professional I am keen to improve my best practices
Code:
If (IsNull(Me.Gasprice) Or IsNull(Me.amountGas) Or (IsNull(Me.ValueofGas))) Then
MsgBox " data is required for all fields "
If MsgBox("Do you wish to save this incomplete record", vbYesNo + vbQuestion) = vbYes Then
Cancel = False
Else
MsgBox " Record not saved re-enter the correct details"
Cancel = True
End If
You MUST use the BeforeUpdate event because you cannot cancel the AfterUpdate event. Last time I looked, the code is not written to be cancelled. I've mentioned this several times. I would have done this differently. I have tried to stay out but I wanted to make sure you weren't accepting a solution that didn't actually work.
You can continue to run the code as it is from what ever event you prefer. Then in the FORM's BeforeUpdate event, check all three fields.
Code:
If Me.Miles & "" = "" OR Me.Price & "" = "" OR Me.Total & "" = "" Then
Msgbox "Please enter at least two of the three gas fields", vbOKOnly
Cancel = True
Me.Miles.SetFocus
Exit Sub
End If
The values may not be null. Concatenating a ZLS caters to both null and ZLS.
Pat, thank you for your response, I fully except Concatenating a ZLS caters to both null and ZLS and will take it on board , however not withstanding that, my code gave the user the option to ignore any changes or save the record , yours code does not give him that option. I requested that you look at my Post 36 , however it does not appear to have been viewed . It looks a though the originator of the post is opting for Uncle's solution so I will move on.
Your solution works fine now.
I am sorry to make such simple mistake and couldn't catch it.
Now I am wondering about error handling. Please explain the best approach and how to implement it. I have never done this part.
Thank you
Happy Holidays and Merry Christmas
Wish you all the best in new year 2021, healthy and safe.
Pat, thank you for your clarification, I misunderstood the posters needs and tried to give him an out in the Forms before update event , plus the fact that if the function is called from the records Before update event, which you have been advocating from day one , no incorrect record should be saved ..
In my Post 36 demo, I entered some error handling code for the function and will leave it to others who are better qualified than me,
As an eighty year old my effort was liken to the "blind leading the blind", but it was simulating plus a learning curve.
I hope you and the people you love have a safe Christmas and a happy New Year
Ypma
