Before Update After Update Which one????

[Tony Williams]

I have a form that contains a number of numeric controls. The user fills in
all the controls, even the totals. I have validation code on each total to
make sure the total is correct (the user wants to key in the total and
Access check if it's correct). An example of the code in the before update
of the totals follows:

Private Sub txtDomfactot_BeforeUpdate(Cancel As Integer)
If ([txtDomfacsole] + [txtDomfacpart]) <> [txtDomfactot] Then
If MsgBox("Row 1 does not add up" & vbCrLf & "It should be " &
[txtDomfacsole] + [txtDomfacpart] & " - Do you want to accept the error?",
vbYesNo, "Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

That works fine on initial input but if they edit any of the figures and
don't amend the total there is no error message. Is there anyway of putting
this code somewhere so that if they amend any figure the total is rechecked
and the error message appears?
TIA
Tony

 

[Paul Overway]

You need to do the validation at form level...not at the control level. Put
your code in Form_BeforeUpdate

 

[Tony Williams]

Thanks Paul. Wont this mean that the message will only appear once they've
input all the data though? The form has a number of columns and rows and I
want the error messages to appear after they've input any incorrect total
rather than at the end of the form input.
Tony

You need to do the validation at form level...not at the control level. Put
your code in Form_BeforeUpdate

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

I have a form that contains a number of numeric controls. The user fills in
all the controls, even the totals. I have validation code on each total to
make sure the total is correct (the user wants to key in the total and
Access check if it's correct). An example of the code in the before update
of the totals follows:

Private Sub txtDomfactot_BeforeUpdate(Cancel As Integer)
If ([txtDomfacsole] + [txtDomfacpart]) <> [txtDomfactot] Then
If MsgBox("Row 1 does not add up" & vbCrLf & "It should be " &
[txtDomfacsole] + [txtDomfacpart] & " - Do you want to accept the
error?",
vbYesNo, "Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

That works fine on initial input but if they edit any of the figures and
don't amend the total there is no error message. Is there anyway of
putting
this code somewhere so that if they amend any figure the total is
rechecked
and the error message appears?
TIA
Tony

 

[Paul Overway]

Yes...that is correct. Your logic is failing me here. How would you know
that they've put in an incorrect amount without ALL the data?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Thanks Paul. Wont this mean that the message will only appear once they've
input all the data though? The form has a number of columns and rows and I
want the error messages to appear after they've input any incorrect total
rather than at the end of the form input.
Tony

You need to do the validation at form level...not at the control level. Put
your code in Form_BeforeUpdate

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

I have a form that contains a number of numeric controls. The user fills in
all the controls, even the totals. I have validation code on each total to
make sure the total is correct (the user wants to key in the total and
Access check if it's correct). An example of the code in the before update
of the totals follows:

Private Sub txtDomfactot_BeforeUpdate(Cancel As Integer)
If ([txtDomfacsole] + [txtDomfacpart]) <> [txtDomfactot] Then
If MsgBox("Row 1 does not add up" & vbCrLf & "It should be " &
[txtDomfacsole] + [txtDomfacpart] & " - Do you want to accept the
error?",
vbYesNo, "Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

That works fine on initial input but if they edit any of the figures
and
don't amend the total there is no error message. Is there anyway of
putting
this code somewhere so that if they amend any figure the total is
rechecked
and the error message appears?
TIA
Tony

 

[Tony Williams]

Sorry Paul probably not explaining myself. The form has a number of rows and
columns of figures and each row and column has a total. I want the error
messages to appear as they complete each column and row total, not when they
have completed ALL the rows and columns. Here is an idea of how the form
looks:

Col 1 Col2 Col3 Totals
Row 2 3 4 9
Row2 4 7 2 13
Row3 6 7 7 20
Totals 12 17 13 52

Is that any better?
Thanks for your help here.
Tony

Yes...that is correct. Your logic is failing me here. How would you know
that they've put in an incorrect amount without ALL the data?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Thanks Paul. Wont this mean that the message will only appear once they've
input all the data though? The form has a number of columns and rows and I
want the error messages to appear after they've input any incorrect total
rather than at the end of the form input.
Tony

You need to do the validation at form level...not at the control level. Put
your code in Form_BeforeUpdate

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have a form that contains a number of numeric controls. The user

fills
in

all the controls, even the totals. I have validation code on each

total
to

make sure the total is correct (the user wants to key in the total and
Access check if it's correct). An example of the code in the before update
of the totals follows:

Private Sub txtDomfactot_BeforeUpdate(Cancel As Integer)
If ([txtDomfacsole] + [txtDomfacpart]) <> [txtDomfactot] Then
If MsgBox("Row 1 does not add up" & vbCrLf & "It should be " &
[txtDomfacsole] + [txtDomfacpart] & " - Do you want to accept the
error?",
vbYesNo, "Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

That works fine on initial input but if they edit any of the figures
and
don't amend the total there is no error message. Is there anyway of
putting
this code somewhere so that if they amend any figure the total is
rechecked
and the error message appears?
TIA
Tony

 

[Paul Overway]

You're making things much more difficult for yourself than need be. You
still need to validate that they've entered the columns before you can
validate the totals. Whether you realize it or not, you are trying to
validate a SET or subset of data...which would be accomplished most easily
and logically in Form_BeforeUpdate. Otherwise, you'll have to write code
for each control AND Form_BeforeUpdate (just to be sure that your logic is
correct). It seems you're just trying to dictate the order that they enter
the information....and that really shouldn't matter at all. One person may
enter the data vertically, the next horizontally. As long as ALL of the
entries are made and they make sense, so what?

The biggest issue I see here is that you have a poor design. You're having
the user enter totals....when normal form would have them calculated
automatically. You also appear to be storing them...another no no. And
you're going to have a mess to validate here anyway...because a row total
may be incorrect while a column total is correct or vice versa. Why
wouldn't you use a spreadsheet for this anyway? I suspect you've left out
critical information from the data model shown...i.e., what does each row
actually represent? Each column? Is each row related to a date? If so,
where are you storing that?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Sorry Paul probably not explaining myself. The form has a number of rows
and
columns of figures and each row and column has a total. I want the error
messages to appear as they complete each column and row total, not when
they
have completed ALL the rows and columns. Here is an idea of how the form
looks:

Col 1 Col2 Col3 Totals
Row 2 3 4 9
Row2 4 7 2 13
Row3 6 7 7 20
Totals 12 17 13 52

Is that any better?
Thanks for your help here.
Tony

Yes...that is correct. Your logic is failing me here. How would you
know
that they've put in an incorrect amount without ALL the data?

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

Thanks Paul. Wont this mean that the message will only appear once they've
input all the data though? The form has a number of columns and rows
and I
want the error messages to appear after they've input any incorrect total
rather than at the end of the form input.
Tony
You need to do the validation at form level...not at the control
level.
Put
your code in Form_BeforeUpdate

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have a form that contains a number of numeric controls. The user fills
in
all the controls, even the totals. I have validation code on each total
to
make sure the total is correct (the user wants to key in the total and
Access check if it's correct). An example of the code in the before
update
of the totals follows:

Private Sub txtDomfactot_BeforeUpdate(Cancel As Integer)
If ([txtDomfacsole] + [txtDomfacpart]) <> [txtDomfactot] Then
If MsgBox("Row 1 does not add up" & vbCrLf & "It should be " &
[txtDomfacsole] + [txtDomfacpart] & " - Do you want to accept the
error?",
vbYesNo, "Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

That works fine on initial input but if they edit any of the figures
and
don't amend the total there is no error message. Is there anyway of
putting
this code somewhere so that if they amend any figure the total is
rechecked
and the error message appears?
TIA
Tony