Sum the values from textbox

[sindhuja]

Hi,

Have a query !

I have a 5 textbox in a form. Out of which user enters the value in four of the textbox. The fifth textbox is to calculate the value of the all the four boxes.

I have done the calculation part. On the lostfocus event of the textbox i have entered the coding.

My requirement is instead of using lostfocus event, after updating the four textboxes the total should be displayed automatically...

Am getting error message if forget to enter any of the textbox..
Is there a way to avoid thiserror message....

Any advise on this will be highly appreciated.

-Sindhuja

[OTWarrior]

Have a default value in your calculation for each of the textboxes:

nz([textbox1],0)

Also, why have you got it on LostFocus? Have you tried OnChange?

Would you kindly post your code please.

[sindhuja]

Hi,

Here is the my coding :
Sub grp1d()
Dim d1 As Integer
Dim d2 As Integer
Dim d3 As Integer
Dim d4 As Integer
d1 = CInt([txtgrp1d1])
d2 = CInt([txtgrp1d2])
d3 = CInt([txtgrp1d3])
d4 = CInt([txtgrp1d4])
txtgrp1dftot = CInt(df506 + df513 + df519 + df526)
End Sub
' On the gotfocus event of the txtgrp1dtot textbox I called upon the procedure grp1d which calculates the total.

Private Sub txtgrp1dtot_GotFocus()
grp1d
End Sub


For example if I skip the txtgrp1d2 textbox without entering the values it gives me error. Am no longer able to procedure further.

I need to close the application and re-open again. Think tis will affect my requirement.

-Sindhuja

[OTWarrior]

Does this work?

Sub grp1d()
Dim d1 As Integer
Dim d2 As Integer
Dim d3 As Integer
Dim d4 As Integer
d1 = CInt(nz([txtgrp1d1],0))
d2 = CInt(nz([txtgrp1d2],0))
d3 = CInt(nz([txtgrp1d3],0))
d4 = CInt(nz([txtgrp1d4],0))
txtgrp1dftot = CInt(df506 + df513 + df519 + df526)
End Sub
' On the gotfocus event of the txtgrp1dtot textbox I called upon the procedure grp1d which calculates the total.

Private Sub txtgrp1dtot_GotFocus()
grp1d
End Sub

[darthobra]

Hello Sindhuja,

It might be a good idea to add an Exit event to control to the four input text boxes. In the event you could validate a numeric value for the input and insert a default '0' to avoid a summation error.

So...

Option Compare Database
Option Explicit
Private Sub txtgrp1d1_Exit(Cancel As Integer)
{Validate value here}
Call grp1d
End Sub
Private Sub txtgrp1d2_Exit(Cancel As Integer)
{Validate value here}
Call grp1d
End Sub
Private Sub txtgrp1d3_Exit(Cancel As Integer)
{Validate value here}
Call grp1d
End Sub
Private Sub txtgrp1d4_Exit(Cancel As Integer)
{Validate value here}
Call grp1d
End Sub
Sub grp1d()
Dim d1 As Integer
Dim d2 As Integer
Dim d3 As Integer
Dim d4 As Integer
d1 = CInt(Nz([txtgrp1d1], 0))
d2 = CInt(Nz([txtgrp1d2], 0))
d3 = CInt(Nz([txtgrp1d3], 0))
d4 = CInt(Nz([txtgrp1d4], 0))
txtgrp1dftot = CInt(d1 + d2 + d3 + d4)
End Sub



I hope this helps.

Darth

[OTWarrior]

Why on exit? If the user has never clicked in the box, then it will never call the code to check the value as it has never exited that object. Besides, the check is done with NZ anyway, so you are duplicating the code.

If you wanted to check the values on change or update (and put the NZ value in there instead) that would be an alternative, but you would still have the problem of the textbox never having been used.

Unless you need to use these values elsewhere, doing the check just before you do the calculation is the best solution.

If you wanted to be extra careful, you can set the default value of each text box in design view, or even on form open.

NB: another thing you could do would be to have a query bound to the form, that has the calculated value as a field, which you use in turn to populate a textbox on your form. This has the benefit of being dynamic also.

[darthobra]

OTW...

This is true, however I based my remarks on the statement


...
I have a 5 textbox in a form. Out of which user enters the value in four of the textbox. The fifth textbox is to calculate the value of the all the four boxes.

...

If the user is inputting the values and the form should calculate at every input, this would be a viable solution. I also agree in using the Default value at design time as well as imposing a mask on the textbox controls to ensure correct data input, not to mention locking the calulated textbox in the event this data is used elsewhere.

Without know the purpose of the form beyond the informaiton provided, it is difficult to assume that binding the form to a query is the best solution, therefore utilizing the benefits of a query does not seem to fit the scope of the problem of calculating the value of four textboxes into a fifth.

[OTWarrior]

Hi,
Am getting error message if forget to enter any of the textbox..
Is there a way to avoid thiserror message....


His original request was to avoid the above error, which is where the user DOESN'T enter all boxes. As I understand, he wants some error-trapping incase the user doesn't fill in all the fields. With your code suggestion you may not need the line {Validate value here}, as the validation is happening with the NZ command.

Also, the query idea was just another one I was throwing out there as a different way of doing it. Like you say, without knowing what this form actually does, it is difficult to understand the limitations.

[darthobra]

OTWarrior,

Agreed. I was not trying to undermine any suggestions for sindhuja, so my appologies if I have offended.

I believe that by leveraging the design time default values, utilizing the Nz function and running the calculation on the exit event will do away with the error in question. Of course this all depends on the intneded use of the form.

:friends:

[sindhuja]

Thanks for the update. Learned new things from the discussion.

One more query !

On the gotfocus event of the fifth texbox i called the procedure to calculate the total values.

I have locked the fifth textbox and also i have set the enabled property to No. In this scenario when i was using the tab to move around the controls it will skip the fifth textbox. in this case i will not able to calculate the total.

Is there a better way to automatically display the total in the fifth textbox once all the four values has been entered.

Actually my form has more calculations. For an example i have given one such set. As i have more calculations i dont want to focus on the controls unnecesarily. Once all the values has been entered total to be filled automatically in the coresponding control..

-Sindhuja

[darthobra]

sindhuja,

Ok, lets say that you have a total of 10 different calculations to perform on your form and that each calculation was the result of a new input. In this case you should have a relevant calculation routine called when a corresponding value has been updated.

This can be accomplished in many different ways. In the scenario you presented, there are 4 fields that are calculated to a fifth. As I suggested, the exit event of any of the 4 inputs calls the calculation routine and the fifth field is updated with the result so that with any data change the calculation occurs.

If you mimic this process with the other data groups then you will always have the desired calculation executed. Furthermore, since you will have the calculation routines in place, any of them can be called seperately or all of them can be called collectively.

Much of this is dependant on the business rules from which your form was derived. You will, of course, have to determine what the result of your form is to be and use that to develop your calculation logic.

Good luck!:)

Darth

[CreganTur]

Good morning Sindhuja,

From what I've gathered about your form, the value of the fifth textbox is derived from a calculation based on the values of the first 4 textboxes. Here's my suggestion:

Make sure you have your tab order setup so that your User will be moved through the textboxes in the correct order. Also, I'd suggest using the After Update event, not the exit event. The reason for this is that the Exit event will fire off any time the object looses focus. Using the After Update event will cause your calculation to fire only when a change has been made to the textbox (and the object looses focus). This will keep your code from running unnecessarily.

I do agree with Darth that you should have your calculation fire whenever a change it made to any textbox, but be certain that you do setup a default value (probably of 0)- otherwise your calculation will fail because a textbox contains a null value.

[sindhuja]

Good Morning Randy !

Let me check and get back if any isues....

-Sindhuja