Validation Rule Problems

[sword856]

Hello all, I have an issue with a form I use.
There is a date field on my form that I need not to be further out
than this fiscal year, so I set the field Validation Rule to
"<#10/1/2008#". This rule works, as it prevents any entry of dates
later than that. On my form, it also prevents people from entering
dates later than that. The message that pops up, however, is a visual
basic error message. "Run-Time Error '3316': MyValidationText".
This is because I am using a calendar control to update the field. I
have a combo box that OnMouseDown, the calendar becomes visible and
gets focus. After selecting a date, that value is sent to the combo
box and the calendar disappears. It is when the combo box value is
set to the calendar value that the validation rule is checked and the
error occurs. Since the error occured during a VB process, the VB
error box comes up.
This is very undesirable.
That box has the option to "Debug". I DO NOT want users to be able to
go to my code and edit things. This could be catastrophic. I need to
be able to validate the date entry without that sort of error message,
while at the same time still using the calendar control. I tried
using the validation rule in the control's properties when editing the
form, but no matter what I put in it doesnt check that rule when
entering data. Help would be much appreciated.

TIA,
George

 

[Guest]

Its strongly advised that error handling is included in any code so that a
custom error message is displayed and the user is let down gently if an error
occurs. This can either be by branching to an error handler (as in wizard
generated code) or by inline error handling using On Error resume Next before
a line in which an error is anticipated and suitable code to handle the
error, identified by its error number, following this.

In your case, however, you should be able to avoid an error being raised by,
in your code, making the assignment of the calendar controls value to the
combo box conditional on the value being <#10/1/2008#. Better than hard
coding the end of the current fiscal year, however, would be to compute it so
that it updates once you enter the next fiscal year, which you could do with
a little function such as follows (I assume your fiscal year starts on 1
October each year)

Function EndFiscYear(dtmDate As Date) As Date

Dim intYear As Integer

If Month(dtmDate) < 10 Then
intYear = Year(dtmDate)
Else
intYear = Year(dtmDate) + 1
End If

EndFiscYear = DateSerial(intYear, 9, 30)

End Function

This will return the last day of the fiscal year for any date passed into
it, so if you wanted to exclude any dates after the end of the current fiscal
year you'd use:

Const conMESSAGE = "Date cannot be after end of current fiscal year."

If CalendarContol.Value <= EndFiscYear(VBA.Date) Then
YourComboBox = CalendarContol.Value
Else
MsgBox conMESSAGE, vbExclamation, "Invalid operation"
End If

Ken Sheridan
Stafford, England

 

[sword856]

Its strongly advised that error handling is included in any code so that a
custom error message is displayed and the user is let down gently if an error
occurs. This can either be by branching to an error handler (as in wizard
generated code) or by inline error handling using On Error resume Next before
a line in which an error is anticipated and suitable code to handle the
error, identified by its error number, following this.

In your case, however, you should be able to avoid an error being raised by,
in your code, making the assignment of the calendar controls value to the
combo box conditional on the value being <#10/1/2008#. Better than hard
coding the end of the current fiscal year, however, would be to compute it so
that it updates once you enter the next fiscal year, which you could do with
a little function such as follows (I assume your fiscal year starts on 1
October each year)

Function EndFiscYear(dtmDate As Date) As Date

Dim intYear As Integer

If Month(dtmDate) < 10 Then
intYear = Year(dtmDate)
Else
intYear = Year(dtmDate) + 1
End If

EndFiscYear = DateSerial(intYear, 9, 30)

End Function

This will return the last day of the fiscal year for any date passed into
it, so if you wanted to exclude any dates after the end of the current fiscal
year you'd use:

Const conMESSAGE = "Date cannot be after end of current fiscal year."

If CalendarContol.Value <= EndFiscYear(VBA.Date) Then
YourComboBox = CalendarContol.Value
Else
MsgBox conMESSAGE, vbExclamation, "Invalid operation"
End If

Ken Sheridan
Stafford, England






- Show quoted text -

Thanks so much, Ken! I don't really know much about error handling,
having just learned Access last month and teaching myself somewhat
some VBA. That function you gave me works wonderfully, but
unfortunately I cannot use it at the moment. The database I am
working on asks for dates within FY08, and only within FY08. Each
fiscal year will have it's own DB, as per request from higher up. It
would be useful, though, to use that function so it wouldn't have to
be changed everytime this DB is copied to be the template for the next
year. I guess it could be used if it were constructed to check that
the date is in the NEXT fiscal year as opposed to this one.
I changed your code to:

.... intYear = Year(dtmDate)+1
....Else
intYear = Year(dtmDate) + 2

And that made it work for the next fiscal year. Thanks for all your
help! You absolutely fixed my problem, and thanks for that! It was
of utmost concern.