Testing for Invalid Date Entry

  • Thread starter Thread starter John C.
  • Start date Start date
J

John C.

I have a form where the user can either use a spin button,
or change the date entry themselves.

I am trying to trap the entry of an invalid date by use of
Isdate() function.

I have tried BeforeUpdate, but I receive an application
error message before the BeforeUpdate event occurs.

Where do I need to place my code so I can trap this?

Also, will I be able to perform a Forms![ControlName].undo?
 
John

We're not there, so we can't tell what your error message says. Is there a
chance it provides some useful information?

How have you determined that the error message comes before your
BeforeUpdate event triggers?

More info, please...

Jeff Boyce
<Access MVP>
 
As Jeff said, our E.S.P. link to your PC is down at present. So you will
have to manually tells us the text of the error message that you are
getting.

BeforeUpdate is the correct event to do a data entry check. If the entered
data fails the check, do not .Undo it, Just set the Cancel parameter of the
event to True. This stops the cursor leaving the field, and keeps the
entered value displayed. The user might prefer to correct the entered value,
rather than having to re-type it from scratch.

If you Undo, but do not set Cancel to true, the entered value will be
erased, >and<, the cursor will move to the next field. That is probably not
what you want.

HTH,
TC
 
And another thing!

Try entering a date like: 1.2.3 and see what happens.

On my PC, the result is a date in 1800, with a time compnent of 01:02:03!
(The date is actualy not 1800, but I don't remember what it was, & I don't
have Access here to check. It was a very early date. I think, from memory,
that it was the earliest date that Access is designed to handle.)

So, if the date passes IsDate(), it is also worth checking whether it has a
time component. I normally reject all dates with time components, on the
assumption that the user has made a mistake, as shown above. (None of my
systems need dates with time components.)

HTH,
TC
 
So sorry you couldn't read my mind...

Here's more information:
Error: The value you entered isn't valid for this field.

For example, you may have entered text in a numeric field
or a number that is larger than the fieldsize setting
permits.

***************************
The form uses a text box, which feeds back to the table
field. The table field is set for data type "Date/Time".

I have placed just a msgbox in the BeforeUpdate event, and
it doesn't fire before I receive this error message.

How/where can I trap the error.
 
So sorry you couldn't read my mind...

Here's more information:
Error: The value you entered isn't valid for this field.

For example, you may have entered text in a numeric field
or a number that is larger than the fieldsize setting
permits.

***************************
The form uses a text box, which feeds back to the table
field. The table field is set for data type "Date/Time".

I have placed just a msgbox in the BeforeUpdate event, and
it doesn't fire before I receive this error message.

How/where can I trap the error.
 
Next question, John.

What did you enter into the textbox that led to the error message?

Jeff Boyce
<Access MVP>

P.S. The error message suggests that something entered doesn't match what
data type is designated for that field. So knowing the field type of the
underlying data would also be useful.
 
If I enter a valid date, the msgbox I have in the
BeforeUpdate event responds.

So...entering an invalid date doesn't trigger the
BeforeUpdate event before I receive the application error
message.

Entering a valid date does trigger the BeforeUpdate event.

....how/where do I trap the error message before it occurs?
 
John C. said:
If I enter a valid date, the msgbox I have in the
BeforeUpdate event responds.

So...entering an invalid date doesn't trigger the
BeforeUpdate event before I receive the application error
message.

Entering a valid date does trigger the BeforeUpdate event.

...how/where do I trap the error message before it occurs?
Click to expand...

If you try to enter data into a field that is invalid for that field,
the error is raised before the BeforeUpdate event because the field
ain't gonna be updated, not nohow! However, the form's Error event will
be raised, and you can handle the error there and suppress the default
message. For example:

'----- start of example code -----
Private Sub Form_Error(DataErr As Integer, Response As Integer)

' Handle invalid data entry in control "txtMyDate".
If DataErr = 2113 _
And Me.ActiveControl.Name = "txtMyDate" _
Then
MsgBox "Sorry, that's not a valid date. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If

End Sub
'----- end of example code -----
 
Thankyou...that resolved my issues. I was unaware that
the form had a Form_Error event that would trap this form
me.

Happy New Years!
 
Back
Top