on_exit code fires twice

[Alex]

I have the following code on_exit of a form control to make sure there is a
part # entered before clicking on any other control on the form. If I click
the close button while my cursor is in the Part control, the msg fires twice.
Is there a way that I can get it to only fire once when the close button is
clicked and have the form close? Thanks.

Private Sub Part__Exit(Cancel As Integer)

If IsNull(Me.Part) Then
MsgBox "You must enter a valid Part #."
Cancel = True
End If

End Sub

 

[Graham Mandeno]

Hi Alex

The Exit event is a difficult one to control, because as you have found, you
have no way to stop it firing if you want to do something legitimate like
click a Close button.

I suggest that you use the form's BeforeUpdate event instead. Something
like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MsgBox( "You must enter a valid Part #.", vbRetryCancel )
Case vbRetry
Cancel = True
Me.Part.SetFocus
Case vbCancel
Me.Undo
End Select
End Sub

 

[Alex]

I don't want a user to be able to exit the field until they've typed a part.
They can, however decide not to fill out the form at all and close it, but I
don't want the below msg firing twice when he/she closes the form.

Hi Alex

The Exit event is a difficult one to control, because as you have found, you
have no way to stop it firing if you want to do something legitimate like
click a Close button.

I suggest that you use the form's BeforeUpdate event instead. Something
like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MsgBox( "You must enter a valid Part #.", vbRetryCancel )
Case vbRetry
Cancel = True
Me.Part.SetFocus
Case vbCancel
Me.Undo
End Select
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have the following code on_exit of a form control to make sure there is a
part # entered before clicking on any other control on the form. If I
click
the close button while my cursor is in the Part control, the msg fires
twice.
Is there a way that I can get it to only fire once when the close button
is
clicked and have the form close? Thanks.

Private Sub Part__Exit(Cancel As Integer)

If IsNull(Me.Part) Then
MsgBox "You must enter a valid Part #."
Cancel = True
End If

End Sub

 

[Linq Adams via AccessMonster.com]

The only way to use the field's Exit event to insure that a part number is
entered is use code to force the user into the field anytime a new record is
started. If you don't do this, you have no way to know that the user will
even tab/click intoo the field, and you validation code in the OnExit event
will be useless. If you use this approach, there's no way to prevent the
message from appearing if you decide to exit the form/dump the record.

As Graham has already pointed out, this type of code (validation checking for
null field values) should ***always**** be done in the Form_BeforeUpdate
event. This insures that a check is made to see if a part number has been
entered, even if the user doesn't enter the parts number field and it allows
you to give the user the opportunity to dump the record.

 

[Alex]

The form opens with the cursor in the Part field. A Part # must be typed
before any other field is populated because the Part # is suppling
information for other combo box fields. The user should only have two
choices when starting to complete the form, to type a Part# or to close the
form. How does using the Before_Update help? If a user tabs out of the
Part# field without typing anything does the form try and update?

 

[Graham Mandeno]

Hi Alex

I sent this message 12 hours ago but for some reason it never showed up in
my newsreader.

Sorry to all if it's a duplicate.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

=========== original message ==================
Hi Alex

Well, as Linq and I have pointed out, the Exit event does not play nicely if
you cancel it.

Another option is to add a function like this:

Private Function CheckPartValid()
If IsNull(Me.Part) Then
MsgBox "You must enter a valid Part #."
Me.Part.SetFocus
End If
End Function

Then, for every other control on the form that could get the focus, either
by tabbing or clicking, set the OnEnter event property to:

=CheckPartValid()