Before or After Update? 1

[tonywilliams]

I have a form that contains a number of numeric controls. There are a number of rows and columns of figures and each row and column has a total.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
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


That works fine on initial input but if they later 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?
Thanks
Tony

 

[fiep]

What you can do is create a function that checks the sum of the rows. And call this function in the afterupdate of the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = CheckSumOfFields
End Sub


function CheckSumOfFields as boolean
dim blnEc as boolean
blnEc = false
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
blnEc = True
End If
End if
CheckSumOfFields = blnEc
End Function

Or something like that. I did use a continues form. So the beforeupdate of the form is triggered everytime a row (record) is updated.

Hope this helps.

 

[tonywilliams]

Thanks for that, presumably that means that I have to repeat ALL of my row and calculations in this code in the before update proerty of the form as well as the before update of the totla controls?
Thanks again
Tony

 

[fiep]

That depends on the current design of the form, which is not yet clear to me (yet). Have you created the rows by hand or are they part of a subform or is it a continues form?
If you created the rows by hand you would have to change ALL calculations and the forms before update will not work as in my example. You would have to add parameters to the function and call the function in the afterupdate of all fields.

 

[tonywilliams]

Thanks, yes the form was created by hand so looks like I need to add the code for every calculation to the before update event UGH! there's loads!
Thanks again
Tony