Solved Calculated Controls

[Uncle Gizmo]

You have a typos in your code your case statement is incorrect...

                        Select Case fGetCtrlZero
                            Case "G_price"
                            Me.G_price = Me.G_total / Me.G_amount
                            
                            Case "G_amount"
                            Me.G_amount = Me.G_total / Me.G_price
                        
                            Case "G_total"
                            Me.G_total = Me.G_price * Me.G_amount
                        End Select

 

[ypma]

Case name not correct

Case "Gprice"
Me.G_price = Me.G_total / Me.G_amount

change all the case names

Regards Ypma Uncle beat me to it .

 

[Pat Hartman]

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.

 

[ypma]

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

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

 

[Pat Hartman]

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.

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.

 

[ypma]

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.

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.

Regards Ypma

 

[silversun]

You have a typos in your code your case statement is incorrect...

                        Select Case fGetCtrlZero
                            Case "G_price"
                            Me.G_price = Me.G_total / Me.G_amount
                           
                            Case "G_amount"
                            Me.G_amount = Me.G_total / Me.G_price
                       
                            Case "G_total"
                            Me.G_total = Me.G_price * Me.G_amount
                        End Select

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 Hartman]

The poster does not want to save incomplete records so there is no reason to offer an option.

You are correct. I didn't download your solution. I don't have time to look at "try this" solutions. You never said that your code fixed the problem I was trying to bring to the attention of the poster so I had no reason to believe that your solution actually addressed the problem of incomplete records.

 

[ypma]

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

 

[Pat Hartman]

Thanks ypma, I was feeling very alone. Happy New Year.