Value you entered isn't valid for this field

Has anyone ever found a way to trap this error message and provide the user with a different message and even undo the field entry?  So far, I have not found any solutions.  The error happens before the LostFocus, Exit, BeforeUpdate events of the control.
I tried creating a function that would run in the OnValidate.  It seemed to work to a point, but was unreliable.  
To see what I'm talking about in action and to provide a working solution in a sample form I have uploaded a small database.
Open Form1. Enter 130pm in the MyDate field and you will get the error I'm referring to.
Sample.accdb
TIMASAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Place this code to your form's module:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim ErrNo As Integer
ErrNo = 2113
    If (DataErr = ErrNo) Then
        Response = acDataErrContinue
        MsgBox "Please, correct date value", vbOKOnly
    End If
End Sub

Open in new window


Add Me.MyDate.Undo after message if you want to cancel changes in this field, but I prefer allow user to edit entered value
Sample.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
The problem with "Dates" is that you forever have to workaround all the different ways a user might enter an incorrect date
So if 130pm is wrong, ...then they try: 1:30pm, or1.30, ...or 130p or 130ppm
Or something like this for full dates
1/1/2017
1\1\2017
1\\1\2017
1/2017
1/17 (Biggest issue because of the Y2k issue, ...is this 2017 or 1917?)
001/1/22017

For me, ...I tend to follow what most websites use for dates, ...to avoid most of these date issues:
*Ask for the date in separate (Month, Day and Year) textboxes.*
for example
Seperate textboxes for date validation
Else, ...you will forever be creating systems to check and workaround invalid dates.
This was the reason for the creation of the Date picker in Access, (though not perfect), it is designed to let a user only select valid dates)
If users can type in 130pm, ...for a date, ...you can expect an almost endless array of other invalid date formats

(even Input masks sometimes create more problems than they are worth)

I attached a basic modified sample db

JeffCoachman
Sample1.accdb
0
 
Gustav BrockCIOCommented:
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
TIMASAuthor Commented:
I had tried using the OnError event previously, but couldn't get it to work consistently.  I didn't trap the specific error 2113 though like you did.  This looks like it will work very well especially when including a date range in the OnValidate event to prevent users from entering just a valid time value that the OnError event would not catch.
0
 
als315Commented:
You can find list of errors here:
http://www.fmsinc.com/microsoftaccess/errors/errornumber_description.html
Error 2113 - The value you entered isn't valid for this field.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Gustav Brock,
Great Article...
;-)

You know, ...every-time I get a chance, ...I try to lead my users into using the ISO date standard for dates and time...

Make everything a lot simpler...
;-)

Jeff
0
 
Gustav BrockCIOCommented:
Indeed, but in many cases wishful thinking - like to turn the US from imperial to metric measures.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.