On Exit Event firing on Close Button

[Design by Sue]

We have a form in which we wish to validate the data in each field as
the use exits the field. For example we have a part number field which can’t
be null or blank so we have the following code in the exit event for that
field.

Private Sub PartNumberTxt_Exit(Cancel As Integer)

If IsNull(Me.PartNumberTxt) Or Me.PartNumberTxt = "" Then

Cancel = True

strMsg = strMsg & vbCrLf & "Please enter a Wheel Number. "

MsgBox strMsg, vbExclamation, "ATTENTION"

End If



End Sub



However this event fires when the user clicks the close button, prompting
the user to enter a number which is annoying. Is there anyway around this

 

[Douglas J. Steele]

One approach is to use a module-level variable that's set to, say, False
when you open the form, and to True when they click on the Close button.
You'd then check for that variable as well in your code:

Private Sub PartNumberTxt_Exit(Cancel As Integer)

If mbooClosing = False Then
If Len(Me.PartNumberTxt & vbNullString) = 0 Then
Cancel = True
strMsg = strMsg & vbCrLf & "Please enter a Wheel Number. "
MsgBox strMsg, vbExclamation, "ATTENTION"
End If
End If

End Sub

Note the different check I put in to see whether the value is Null or a
zero-length string. Believe it or not, it's actually a little more efficient
(not that you'd notice the difference...)

 

[John W. Vinson]

We have a form in which we wish to validate the data in each field as
the use exits the field. For example we have a part number field which can’t
be null or blank so we have the following code in the exit event for that
field.

Private Sub PartNumberTxt_Exit(Cancel As Integer)

If IsNull(Me.PartNumberTxt) Or Me.PartNumberTxt = "" Then

Cancel = True

strMsg = strMsg & vbCrLf & "Please enter a Wheel Number. "

MsgBox strMsg, vbExclamation, "ATTENTION"

End If



End Sub



However this event fires when the user clicks the close button, prompting
the user to enter a number which is annoying. Is there anyway around this

Don't use the Exit event - use the BeforeUpdate event of each control instead.
This fires only if the user selects the control and changes its contents, and
it can be cancelled.

Similarly, you should check for NULL values in the Form's BeforeUpdate event,
which can also be cancelled. Code on the PartNumberTxt control's events will
never fire at all if the user simply skips over the control and never edits
it!

John W. Vinson [MVP]

 

[Design by Sue]

Thanks but -

Douglas : Doesn’t work as the close button code never executes if the
PartNumberTxt is empty or null.

John: This doesn’t work as if the user just tabs through the part number
the BeforeUpdate event never fires.

 

[John W. Vinson]

John: This doesn’t work as if the user just tabs through the part number
the BeforeUpdate event never fires.

Neither will the Exit event. if the user uses the mouse rather than tabbing.

That's why I suggested using the Form's (not the control's) BeforeUpdate
event.

John W. Vinson [MVP]