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 (txtTotNbrClients) on page 3 is the sum
of these controls on that page
([txtClientsdomfacsole] + [txtClientsdomidsole] + [txtClientsexpsole] +
[txtClientsimpsole] + [txtClientsdomfacpart] + [txtClientsdomidpart] +
[txtClientsexppart] + [txtClientsimppart])
The calculated control on page 4 (txtClientsTot) is the sum of these
controls on that page
([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients100000])
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] + [txtClients100000]) <>
([txtClientsdomfacsole] + [txtClientsdomidsole] + [txtClientsexpsole] +
[txtClientsimpsole] + [txtClientsdomfacpart] + [txtClientsdomidpart] +
[txtClientsexppart] + [txtClientsimppart]) Then
If MsgBox("Total does not agree with Total Number of Clients on Page 3" &
vbCrLf & "It should be " & [txtTotNbrClients] & " - Do you want to accept
the error?", vbYesNo, "Calculation 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]

"Tony Williams" <[e-mail]> wrote in message
news:cu**********@sparta.btinternet.com...

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 (txtTotNbrClients) on page 3 is the
sum
of these controls on that page
([txtClientsdomfacsole] + [txtClientsdomidsole] + [txtClientsexpsole] +
[txtClientsimpsole] + [txtClientsdomfacpart] + [txtClientsdomidpart] +
[txtClientsexppart] + [txtClientsimppart])
The calculated control on page 4 (txtClientsTot) is the sum of these
controls on that page
([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients100000])
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] + [txtClients100000]) <>
([txtClientsdomfacsole] + [txtClientsdomidsole] + [txtClientsexpsole] +
[txtClientsimpsole] + [txtClientsdomfacpart] + [txtClientsdomidpart] +
[txtClientsexppart] + [txtClientsimppart]) Then
If MsgBox("Total does not agree with Total Number of Clients on Page 3" &
vbCrLf & "It should be " & [txtTotNbrClients] & " - Do you want to accept
the error?", vbYesNo, "Calculation 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

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.txtClients0,0))
sngTotal = sngTotal + CSng(Nz(Me.txtClients500,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]

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" <[e-mail]> wrote in message
news:cu**********@sparta.btinternet.com...

"Tony Williams" <[e-mail]> wrote in message
news:cu**********@sparta.btinternet.com...

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 (txtTotNbrClients) on page 3 is the
sum
of these controls on that page
([txtClientsdomfacsole] + [txtClientsdomidsole] + [txtClientsexpsole] +
[txtClientsimpsole] + [txtClientsdomfacpart] + [txtClientsdomidpart] +
[txtClientsexppart] + [txtClientsimppart])
The calculated control on page 4 (txtClientsTot) is the sum of these
controls on that page
([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients100000])
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] + [txtClients100000]) <>
([txtClientsdomfacsole] + [txtClientsdomidsole] + [txtClientsexpsole] +
[txtClientsimpsole] + [txtClientsdomfacpart] + [txtClientsdomidpart] +
[txtClientsexppart] + [txtClientsimppart]) Then
If MsgBox("Total does not agree with Total Number of Clients on Page 3" & vbCrLf & "It should be " & [txtTotNbrClients] & " - Do you want to accept the error?", vbYesNo, "Calculation 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
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.txtClients0,0))
sngTotal = sngTotal + CSng(Nz(Me.txtClients500,0))

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