Form BeforeUpdate Ignoring Cancel

[Chuck]

I have a situation where I must verify that both of two related controls have
been entered or neither has been entered. The following code does just what
I want except that Access seems to be ignoring the Cancel value:

Function CrossCheckKeys(Cancel As Integer)
Dim CurrForm As Form: Set CurrForm = Screen.ActiveForm
With CurrForm
If (IsNull(.Controls("comSinkKey")) And Not
IsNull(.Controls("comSrceKey"))) _
Or (IsNull(.Controls("comSrceKey")) And Not
IsNull(.Controls("comSinkKey"))) Then
If IsNull(.Controls("comSinkKey")) Then
MsgRtn "DYN003", "Sink", "Source"
Else
MsgRtn "DYN003", "Source", "Sink"
End If 'IsNull(.Controls("SinkKey"))
Cancel = True
Else
BeforeFormUpdate
End If '(.Controls("SinkForm") = "" And .Controls("SrceForm") <> "") _
....
End With 'CurrForm
End Function

This code is logically identical to other functions I've used with Control
BeforeUpdate events and it worked just fine there. With the Form
BeforeUpdate event, Access ignores the fact that Cancel is True and saves the
bad record without allowing the user to correct it in response to the error
message.

I tried invoking this function from an [Event Procedure] and got the same
bad results.

Any ideas? Thanks in advance for any assistance.

 

[Tom van Stiphout]

On Wed, 13 Jan 2010 20:57:02 -0800, Chuck

Comment out all this code and replace with:
Cancel = True
Can you still save a record? I think not, but if yes that's an
important clue something funny is up with this form. Use the wizard to
quickly create a temporary form. Repeat the test.

-Tom.
Microsoft Access MVP

 

[Rick Brandt]

I have a situation where I must verify that both of two related controls
have
been entered or neither has been entered. The following code does just
what I want except that Access seems to be ignoring the Cancel value:

Function CrossCheckKeys(Cancel As Integer)
Dim CurrForm As Form: Set CurrForm = Screen.ActiveForm
With CurrForm
If (IsNull(.Controls("comSinkKey")) And Not
IsNull(.Controls("comSrceKey"))) _
Or (IsNull(.Controls("comSrceKey")) And Not
IsNull(.Controls("comSinkKey"))) Then
If IsNull(.Controls("comSinkKey")) Then
MsgRtn "DYN003", "Sink", "Source"
Else
MsgRtn "DYN003", "Source", "Sink"
End If 'IsNull(.Controls("SinkKey"))
Cancel = True
Else
BeforeFormUpdate
End If '(.Controls("SinkForm") = "" And .Controls("SrceForm") <> "")
_
...
End With 'CurrForm
End Function

This code is logically identical to other functions I've used with Control
BeforeUpdate events and it worked just fine there. With the Form
BeforeUpdate event, Access ignores the fact that Cancel is True and saves
the bad record without allowing the user to correct it in response to the
error message.

I tried invoking this function from an [Event Procedure] and got the same
bad results.

Any ideas? Thanks in advance for any assistance.

What you have here is a function, not the BeforeUpdate event code. Events
can be canceled, not functions. If you want to use a function in an event
that can be canceled then have the function return a value back to the code
in the event and let code there decide to cancel.

EX:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If SomeFunction() = 0 Then
Cancel = True
End If

End Sub

 

[Tom van Stiphout]

On Thu, 14 Jan 2010 06:25:55 -0600, Rick Brandt

Good eyes. I missed that.
-Tom.

I have a situation where I must verify that both of two related controls
have
been entered or neither has been entered. The following code does just
what I want except that Access seems to be ignoring the Cancel value:

Function CrossCheckKeys(Cancel As Integer)
Dim CurrForm As Form: Set CurrForm = Screen.ActiveForm
With CurrForm
If (IsNull(.Controls("comSinkKey")) And Not
IsNull(.Controls("comSrceKey"))) _
Or (IsNull(.Controls("comSrceKey")) And Not
IsNull(.Controls("comSinkKey"))) Then
If IsNull(.Controls("comSinkKey")) Then
MsgRtn "DYN003", "Sink", "Source"
Else
MsgRtn "DYN003", "Source", "Sink"
End If 'IsNull(.Controls("SinkKey"))
Cancel = True
Else
BeforeFormUpdate
End If '(.Controls("SinkForm") = "" And .Controls("SrceForm") <> "")
_
...
End With 'CurrForm
End Function

This code is logically identical to other functions I've used with Control
BeforeUpdate events and it worked just fine there. With the Form
BeforeUpdate event, Access ignores the fact that Cancel is True and saves
the bad record without allowing the user to correct it in response to the
error message.

I tried invoking this function from an [Event Procedure] and got the same
bad results.

Any ideas? Thanks in advance for any assistance.

What you have here is a function, not the BeforeUpdate event code. Events
can be canceled, not functions. If you want to use a function in an event
that can be canceled then have the function return a value back to the code
in the event and let code there decide to cancel.

EX:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If SomeFunction() = 0 Then
Cancel = True
End If

End Sub