I have a message box which pops up a message after a check on a total. The
code on the Before Update Event is:
If ([txtDomfacsole] + [txtDomfacpart]) <> [txtDomfactot] Then
MsgBox "Row 1 does not add up" & vbCrLf & "It should be " & [txtDomfacsole]
+ [txtDomfacpart], vbOKCancel, "Calculation Error"
Cancel = True
End If
First Question
However I want the user to be able to accept the error if they wish, at the
moment this code keeps appearing as the user tabs to the next control. How
can I change the code so that the user has the choice as to whether or not
they want to change the data?

Second Question
I have some 14 rows, with totals, on a form where this calculation needs to
check the addition of various controls. I could of course put this code
behind every total but I wondered if there was a way to do this onec using a
module. If so could someone guide thru the creation of the module and what I
would have to put behind each total control.

TIA
Tony Williams

Hi Tony.

Tony Williams wrote:
You need to use a Yes/No message box and tell the app to cancel the
transaction if the 'No' button is pressed:

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

The user can then use the ESC key to roll back the change(s) and correct
the entries. Question: why not use a query to do the calculations for
you so no calculation checking is needed?

keyword. Create a sub-routine in the form's code and call it from your
controls, eg:

Sub MyCalc()

Your code goes here

End Sub

HTH - Keith.
www.keithwilby.com

Thanks Keith. The first part works just fine. The reason I'm not using a
query for the calculation is because the users are keying in data from a
form that has been sent to them by outside organisations and they are
checking the data that has been submitted.
I'm not sure I understand the second part could you elaborate?
Thanks again
Tony
"Keith" <keith (AT) NOCARPkeithwilby (DOT) org.uk> wrote

Hi Tony, glad the first bit worked for you.

A sub-routine is a sequence of VBA code contained within an object such
as a form. The sub-routine is defined by "Sub" and the name you give it
followed by "()" and the "End Sub" statement. A simple example: if you
wanted all the text boxes on your form to cause the computer to beep and
then display a message box before they are updated, your sub-routine
might look like this:

Sub BeepMessage()

Beep
MsgBox("Data will now be updated.", vbOKOnly, "Data update"

End Sub

You would call the sub-routine from one of the controls' events, eg
'Before update':

Private Sub txtMyTextBox_BeforeUpdate(Cancel As Integer)

BeepMessage

End Sub

Not sure off the top of my head how to use a sub-routine to cancel an
event but I'll do a little digging and come back later.

Regards,
Keith.
www.keithwilby.com

Tony Williams wrote:

Looks like you'd need to use a function, not a sub:

Function MyCancel() As Boolean

Beep
If MsgBox("Are you sure you want to update the data?", vbYesNo, "Confirm
update") = vbNo Then MyCancel = True

End Function

The function returns "True" if the 'No' button is pressed.
You'd then call the function from your Before Update events:

Private Sub txtMyTextBox_BeforeUpdate(Cancel As Integer)

If MyCancel Then Cancel = True

End Sub

If 'MyCancel' returns 'True' then the event is cancelled. There may be a
more elegant way to do this, I'm sure someone will post it if there is ;-)

Regards,
Keith.
www.keithwilby.com

Keith wrote:

<snip>

Thanks Keith I'll have a play around with that.
Tony
"Keith" <keith (AT) NOCARPkeithwilby (DOT) org.uk> wrote

One thought Keith each of the 14 rows have different control names in other
words it's the same type of calculation but different control names eg
control1+control2=control3
control4+control5=control6
and so on
How would I alter the formula from the first part of my question to use that
in a function to check all rows as they were entered?
Thanks
Tony
"Keith" <keith (AT) NOCARPkeithwilby (DOT) org.uk> wrote

You'd have to pass the control names to the function as arguments:

Private Sub txtMyTextBox_BeforeUpdate(Cancel As Integer)

If MyCancel(Me.control1,Me.control2,Me.control3) Then Cancel = True

End Sub

Customise the above for whatever the control names are.

Function MyCancel(MyText1, MyText2, MyText3) As Boolean

If (MyText1 + MyText2) <> MyText3 Then

Beep
If MsgBox("Row 1 does not add up" & vbCrLf _
& "It should be " & MyText1 + MyText2 _
& " - Do you want to accept the error?", _
vbYesNo, "Calculation Error") = vbNo Then MyCancel = True

End If

End Function

Regards,
Keith.
www.keithwilby.com

Tony Williams wrote:

Thanks Keith I'll have a go at that
Tony
"Keith" <keith (AT) NOCARPkeithwilby (DOT) org.uk> wrote