How to check for valid data (time) in a form?

[Guest]

Hello,

I'm using MS Access ver2000 and Windows XP. I have a form with two bound
text controls - txtStartTime and txtEndTime. Both of them have been formatted
as "Medium Time" and have an Input Mask of "09:00\ >LL;0;_ ".

When I enter a value that does not conform to the Input Mask, I get an error
message that says "The value you entered isn't appropriate for the input mask
specified for this field.".

How do I change this message to say "Please enter the Start Date in proper
format, e.g. 09:00AM" and then have the focus back to the control? I'll
appreciate a detailed answer so that I can understand the mechanics of what's
happening.

Another question. Where would I place the code to check that StartTime is
less than EndTime? I have these controls on a tab-page on the form. Would I
place this code in the AfterUpdate event of both the controls? Or LostFocus?
I'm assuming that I'll place the same kind of check in the OnChange event.

Thank you.

Amit

 

[Wayne Morgan]

In the form's Error event, trap the error. This error is number 2279.

Example:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then
MsgBox "Please enter the Time in proper format, e.g. 09:00AM"
Response = acDataErrContinue
End If
End Sub

You can verify that the start time is before the end time in the form's
BeforeUpdate event. You would compare the two values using an If statement
to see if Start < End. If not, pop-up a message box (as above) and set
Cancel = True to cancel the update. Set the focus back to the desired
control. The problem is, what if you go past midnight. Without including a
date AND time, this may cause you a problem.

Example:
If Me.txtStart >= Me.txtEnd Then
MsgBox "The start time needs to be before the end time."
Cancel = True
Me.txtStart.SetFocus
Exit Sub
End If

 

[Guest]

In the form's Error event, trap the error. This error is number 2279.

Example:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then
MsgBox "Please enter the Time in proper format, e.g. 09:00AM"
Response = acDataErrContinue
End If
End Sub

You can verify that the start time is before the end time in the form's
BeforeUpdate event. You would compare the two values using an If statement
to see if Start < End. If not, pop-up a message box (as above) and set
Cancel = True to cancel the update. Set the focus back to the desired
control. The problem is, what if you go past midnight. Without including a
date AND time, this may cause you a problem.

Example:
If Me.txtStart >= Me.txtEnd Then
MsgBox "The start time needs to be before the end time."
Cancel = True
Me.txtStart.SetFocus
Exit Sub
End If

Hi Wayne,

Thanks for your response, and the warning regarding time. It's a good point
and I'll definitely keep it in mind for future applications, but in the
current scenario, the times are for the same day event, so luckily, checking
for dates is not an issue.

Cheers,

-Amit

 

[Guest]

In the form's Error event, trap the error. This error is number 2279.

Example:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then
MsgBox "Please enter the Time in proper format, e.g. 09:00AM"
Response = acDataErrContinue
End If
End Sub

Hi Wayne
(or anyone else who would like to answer),

A follow-up question. I tried to write code to see the error number for
myself, but it gave the error number as 0. I'll appreciate it if you could
explain this to me. Here's the code in my form's OnError event:

=================================================
Private Sub Form_Error(DataErr As Integer, Response As Integer)
MsgBox "Inside On Error event"
MsgBox "Error # " & Err.Number & " : " & Err.Description
Exit Sub
End Sub
=================================================

I opened the form and deliberately typed in a value that didn't conform to
the Input Mask. I got the first messagebox, then the second messagebox
displayed error #:0 with no description, and then I got the standard
validation error message.

Thanks for your response.

-Amit

 

[Wayne Morgan]

When in the Error event of the form, the error number is supplied to you as
one of the arguments of the event. The number is in DataErr. The Err object
you are referring to will give the result of an error in your code; since
there isn't one, the answer is zero.

MsgBox "Error # " & DataErr