Help with calculated control on form

[Tony Williams]

I have a form with a tabcontrol which has a number of pages. I want to check
the value of a calculated control on one page with a calculated control on
another page. The calculated control (txtTotNbrClien ts) on page 3 is the sum
of these controls on that page
([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
[txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
[txtClientsexppa rt] + [txtClientsimppa rt])
The calculated control on page 4 (txtClientsTot) is the sum of these
controls on that page
([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients10000 0])
I tried using the calculated control names in my code but that didn't work
so I assumed I would have to do the calculation again in the code. So here
is my code which I have tried in the BeforeUpdate and AfterUpdate property
of txtClientsTot
If ([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients10000 0]) <>
([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
[txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
[txtClientsexppa rt] + [txtClientsimppa rt]) Then
If MsgBox("Total does not agree with Total Number of Clients on Page 3" &
vbCrLf & "It should be " & [txtTotNbrClient s] & " - Do you want to accept
the error?", vbYesNo, "Calculatio n Error") = vbNo Then
Cancel = True
End If
End If

I should get a message box if the two totals don't agree but I don't.
Anyone help here?
TIA
Tony

[Stefan Kowalski]

Re: Help with calculated control on form


"Tony Williams" <tw@tcpinvalid. com> wrote in message
news:cupvql$5eh $1@sparta.btint ernet.com...[color=blue]
>I have a form with a tabcontrol which has a number of pages. I want to
>check
> the value of a calculated control on one page with a calculated control on
> another page. The calculated control (txtTotNbrClien ts) on page 3 is the
> sum
> of these controls on that page
> ([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
> [txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
> [txtClientsexppa rt] + [txtClientsimppa rt])
> The calculated control on page 4 (txtClientsTot) is the sum of these
> controls on that page
> ([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
> [txtClients10000] + [txtClients50000] + [txtClients10000 0])
> I tried using the calculated control names in my code but that didn't work
> so I assumed I would have to do the calculation again in the code. So here
> is my code which I have tried in the BeforeUpdate and AfterUpdate property
> of txtClientsTot
> If ([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000]
> +
> [txtClients10000] + [txtClients50000] + [txtClients10000 0]) <>
> ([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
> [txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
> [txtClientsexppa rt] + [txtClientsimppa rt]) Then
> If MsgBox("Total does not agree with Total Number of Clients on Page 3" &
> vbCrLf & "It should be " & [txtTotNbrClient s] & " - Do you want to accept
> the error?", vbYesNo, "Calculatio n Error") = vbNo Then
> Cancel = True
> End If
> End If
>
> I should get a message box if the two totals don't agree but I don't.
> Anyone help here?
> TIA
> Tony
>
>[/color]

Hopefully you had your rounding issues resolved last time and understand
that bit, but this time could you be falling into the null trap. Paste this
code into a module and run it:

Public Sub TestMe()

' This sub demonstrates the folly of mis-using nulls
If 2 + 3 + Null <> 13 Then
' If it isn't thirteen, then tell me it isn't
MsgBox "Two plus three plus null does not equal thirteen"
Else
' Otherwise it must equal thirteen (doh!)
MsgBox "Two plus three plus null equals thirteen"
End If

End Sub

In other words, null values in your textboxes can wreck your calculations.
You should go slowly through each textbox building up your total. Convert
each value to the datatype you are looking for. E.g. if the result was
supposed to be of single precision which is usually sufficient:

....
sngTotal = sngTotal + CSng(Nz(Me.txtC lients0,0))
sngTotal = sngTotal + CSng(Nz(Me.txtC lients500,0))

Then you can Debug.Print to see what the total actually is, instead of
saying simply "if it isn't this, do that".

[Tony Williams]

Re: Help with calculated control on form

Thanks again Stefan. yes I did resolve the rounding issues, changed the
field type from double. I'll try out the ideas you've given me and let you
know.
Thanks again
Tony
"Stefan Kowalski" <a@b.com> wrote in message
news:cuq220$84j $1@sparta.btint ernet.com...[color=blue]
>
> "Tony Williams" <tw@tcpinvalid. com> wrote in message
> news:cupvql$5eh $1@sparta.btint ernet.com...[color=green]
> >I have a form with a tabcontrol which has a number of pages. I want to
> >check
> > the value of a calculated control on one page with a calculated control[/color][/color]
on[color=blue][color=green]
> > another page. The calculated control (txtTotNbrClien ts) on page 3 is the
> > sum
> > of these controls on that page
> > ([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
> > [txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
> > [txtClientsexppa rt] + [txtClientsimppa rt])
> > The calculated control on page 4 (txtClientsTot) is the sum of these
> > controls on that page
> > ([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
> > [txtClients10000] + [txtClients50000] + [txtClients10000 0])
> > I tried using the calculated control names in my code but that didn't[/color][/color]
work[color=blue][color=green]
> > so I assumed I would have to do the calculation again in the code. So[/color][/color]
here[color=blue][color=green]
> > is my code which I have tried in the BeforeUpdate and AfterUpdate[/color][/color]
property[color=blue][color=green]
> > of txtClientsTot
> > If ([txtClients0] + [txtClients500] + [txtClients1000] +[/color][/color]
[txtClients5000][color=blue][color=green]
> > +
> > [txtClients10000] + [txtClients50000] + [txtClients10000 0]) <>
> > ([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
> > [txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
> > [txtClientsexppa rt] + [txtClientsimppa rt]) Then
> > If MsgBox("Total does not agree with Total Number of Clients on Page 3"[/color][/color]
&[color=blue][color=green]
> > vbCrLf & "It should be " & [txtTotNbrClient s] & " - Do you want to[/color][/color]
accept[color=blue][color=green]
> > the error?", vbYesNo, "Calculatio n Error") = vbNo Then
> > Cancel = True
> > End If
> > End If
> >
> > I should get a message box if the two totals don't agree but I don't.
> > Anyone help here?
> > TIA
> > Tony
> >
> >[/color]
>
> Hopefully you had your rounding issues resolved last time and understand
> that bit, but this time could you be falling into the null trap. Paste[/color]
this[color=blue]
> code into a module and run it:
>
> Public Sub TestMe()
>
> ' This sub demonstrates the folly of mis-using nulls
> If 2 + 3 + Null <> 13 Then
> ' If it isn't thirteen, then tell me it isn't
> MsgBox "Two plus three plus null does not equal thirteen"
> Else
> ' Otherwise it must equal thirteen (doh!)
> MsgBox "Two plus three plus null equals thirteen"
> End If
>
> End Sub
>
> In other words, null values in your textboxes can wreck your calculations.
> You should go slowly through each textbox building up your total. Convert
> each value to the datatype you are looking for. E.g. if the result was
> supposed to be of single precision which is usually sufficient:
>
> ...
> sngTotal = sngTotal + CSng(Nz(Me.txtC lients0,0))
> sngTotal = sngTotal + CSng(Nz(Me.txtC lients500,0))
>
> Then you can Debug.Print to see what the total actually is, instead of
> saying simply "if it isn't this, do that".
>
>[/color]