My warning message box wont work on calculated contols

[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

 

[Steve Schapel]

Tony,

First of all, you should be able to use the value of the calculated
controls in your code. However, the BeforeUpdate or AfterUpdate events
of txtClientsTot are not applicable. I don't think I can suggest a more
appropriate event for your code, without knowing more about what you are
actually doing on this form, but I would suspect this might be the main
problem here.

 

[John Smith]

Assuming that your calculated controls are displaying correctly you should be
able to use them directly without having to recalculate in your code, but with
one proviso. Unless every control has a default value and a BeforeUpdate
check to ensure that the user cannot blank it out you will need to put an Nz()
round each one. If you do not then leaving any one of them blank will Null
your total for that page.

You don't say where you are calling this code, it will need to be in the
BeforeUpdate of the _form_ to achieve what you are seeking.

HTH
John

 

[John Smith]

Assuming that your calculated controls are displaying correctly you should be
able to use them directly without having to recalculate in your code, but with
one proviso. Unless every control has a default value and a BeforeUpdate
check to ensure that the user cannot blank it out you will need to put an Nz()
round each one. If you do not then leaving any one of them blank will Null
your total for that page.

You don't say where you are calling this code, it will need to be in the
BeforeUpdate of the _form_ to achieve what you are seeking.

HTH
John

 

[John Smith]

Assuming that your calculated controls are displaying correctly you should be
able to use them directly without having to recalculate in your code, but with
one proviso. Unless every control has a default value and a BeforeUpdate
check to ensure that the user cannot blank it out you will need to put an Nz()
round each one. If you do not then leaving any one of them blank will Null
your total for that page.

You don't say where you are calling this code, it will need to be in the
BeforeUpdate of the _form_ to achieve what you are seeking.

HTH
John