Field Validation

[Guest]

I thought this would be simple...help

I have a form that on-load sets focus on a short date field/label called
"nDate". I have a validation on this fields LostFocus to not be null or less
then Now() as follows...

Private Sub nDate_LostFocus()
If IsNull(nDate.Value) Then
MsgBox "Please enter DATE to start work.", vbExclamation, "MISSING FORM
DATE"Exit Sub
ElseIf nDate.Value < Format(Now(), "mm/dd/yyyy") Then
Msg = "The DATE you entered is in the past." & vbCrLf & "Would you like
to continue anyway?"
MsgStyle = vbYesNo + vbQuestion + vbDefaultButton2
MsgTitle = "PAST DATE"

response = MsgBox(Msg, MsgStyle, MsgTitle)

If response = vbYes Then
Exit Sub
ElseExit Sub
End If
End If
End Sub

....in return I don't wan't the user to be able to leave this field until the
validations are met but when I'm setting the >>Me!nDate.SetFocus<<ISSUE>> (as
pointed out above) at the end of the captured validation it does not seem to
work even though this looks to be the most logical approach.

Q. What am I not understanding about placing a SetFocus in the LostFocus
event of that same field?

Q. Is it possible to hold a user in a particular field until a validation is
met and how?

Thank you in advance for you assistance.

 

[Guest]

PLEASE DISREGARD THIS QUESTION!

I FOUND THE ANSWER IN A ANOTHER POSTING CALLED...
" Cursor should not move until value is entered in the control box "
.... ANSWER:

"I'm afraid that won't work, David. In the LostFocus event, the focus
hasn't actually left the control yet. but Access already knows where
it's going next. Setting the focus to the control in that event will
have no effect, because after that event the focus will move on to the
next control. The Exit event is the one to use for this, because it can
be Cancelled.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com "

MY APOLOGIES, SHOULD OF CHECKED BEFORE I POSTED THIS. I GUESS IT'S GETTING
TOO LATE BETTER CALL IT A NIGHT.

 

[Tim Ferguson]

If I may point out a number of different problems here:

Private Sub nDate_LostFocus()

You've already discovered that this is the wrong event. I prefer the
_Exit() event, because it gives you the Cancel argument, and also because
it occurs before the BeforeUpdate.

If IsNull(nDate.Value) Then

It's easier and probably more reliable to use the .Text property here,
because you get exactly what the user typed in:

If Len(nDate.Text) = 0 Then

MsgBox "Please enter DATE to start work.", _
vbExclamation, "MISSING FORM DATE"
Me!nDate.SetFocus '<<ISSUE>>

Okay, you already know that this should be... Cancel = True

Exit Sub
ElseIf nDate.Value < Format(Now(), "mm/dd/yyyy") Then

Nonononono -- you are doing a text comparison here, and with a mdy date
format it's garbage. I suggest you (a) first check for a valid date at
all, and then do a Date comparison:

ElseIf Not IsDate(nDate.Text) Then
MsgBox "You entered a rubbish date..."
Cancel = True

ElseIf DateValue(CDate(nDate.Text)) < Date() Then
If vbNo = MsgBox("Are you sure", vbYesNo) Then
Cancel = True
End If

End If


You may as well catch as many errors as you can early on!

Hope that helps


Tim F

 

[Guest]

Thank you for the valuable information.

If I may point out a number of different problems here:


You've already discovered that this is the wrong event. I prefer the
_Exit() event, because it gives you the Cancel argument, and also because
it occurs before the BeforeUpdate.


It's easier and probably more reliable to use the .Text property here,
because you get exactly what the user typed in:

If Len(nDate.Text) = 0 Then


Okay, you already know that this should be... Cancel = True


Nonononono -- you are doing a text comparison here, and with a mdy date
format it's garbage. I suggest you (a) first check for a valid date at
all, and then do a Date comparison:

ElseIf Not IsDate(nDate.Text) Then
MsgBox "You entered a rubbish date..."
Cancel = True

ElseIf DateValue(CDate(nDate.Text)) < Date() Then
If vbNo = MsgBox("Are you sure", vbYesNo) Then
Cancel = True
End If

End If


You may as well catch as many errors as you can early on!

Hope that helps


Tim F