Date Comparison in Access 2002 using VBA

[Karl Gibbon]

Hi There,
I currently have a database in Access 2002 with several
forms. I would like to restrict access to one form in perticular until
November 1st every year. My current method (attempted method) is a
heavy handed, no nonsense, direct date comparison using VBA.

Function NovemberOnwards()
'Use with 'On Avtivate' on Orders form

CurrentDate = Date 'I cannot decide which function is best to use
'to get the current date

If CurrentDate < #11/1/2005# Then
Forms![Orders].Visible = False
End If
End Function

Any suggestions are welcome and thanks is given in advance,

Karl Gibbon

[Wayne Morgan]

This should work and will automatically fill in the year for you.

If Date < DateSerial(Year(Date), 11, 1) Then

Also, your method will hide the form, but still allow it to be opened. If
you want to prevent it from even being opened, you could make the same check
in the form's Open event and set Cancel=True if the date doesn't fit.

Example:
Cancel = (Date < DateSerial(Year(Date), 11, 1))

--
Wayne Morgan
MS Access MVP
"Karl Gibbon" <gi*******@hotmail.com> wrote in message
news:13**************************@posting.google.c om...

Hi There,
I currently have a database in Access 2002 with several
forms. I would like to restrict access to one form in perticular until
November 1st every year. My current method (attempted method) is a
heavy handed, no nonsense, direct date comparison using VBA.

Function NovemberOnwards()
'Use with 'On Avtivate' on Orders form

CurrentDate = Date 'I cannot decide which function is best to use
'to get the current date

If CurrentDate < #11/1/2005# Then
Forms![Orders].Visible = False
End If
End Function

Any suggestions are welcome and thanks is given in advance,

Karl Gibbon

[Karl Gibbon]

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<ba*******************@newssvr11.news.prodigy .com>...

This should work and will automatically fill in the year for you.

If Date < DateSerial(Year(Date), 11, 1) Then

Also, your method will hide the form, but still allow it to be opened. If
you want to prevent it from even being opened, you could make the same check
in the form's Open event and set Cancel=True if the date doesn't fit.

Example:
Cancel = (Date < DateSerial(Year(Date), 11, 1))

Indeed your method does work, however there seems to be slight flaw in
it somewhere. Upon opening the database i am greeted by the
switchboard which contains several buttons to open different forms,
one of which is the 'Orders' form which is the one i am concerend with
preventing access to before 1st November every year. I click on the
button to open this 'Orders' form and for some reason it is allowed to
open. I then open the module to view the code, and run it directly
from there using the 'Run' button and the recently opened 'Orders'
form is closed. The code will then work every time after that (I'm not
sure on this but i think it might be because the Date function can't
get the date for some reason unless the code is directly run ). I
don't see the point however, in having a piece of code to prevent
access to a particular form if it will only work if the code has
already been directly run.
Thanks for your suggestion Wayne and hope you can help me with this
new little problem.

Thanks also to anyone else who is kind enough to reply,

Karl Gibbon

[Wayne Morgan]

I can't reproduce it here. The code works, even if I try to open the form
from the switchboard immediately after opening the database. Do you have any
other code in the Open event of the form that may be causing the problem?

--
Wayne Morgan
MS Access MVP
"Karl Gibbon" <gi*******@hotmail.com> wrote in message
news:13**************************@posting.google.c om...

Indeed your method does work, however there seems to be slight flaw in
it somewhere. Upon opening the database i am greeted by the
switchboard which contains several buttons to open different forms,
one of which is the 'Orders' form which is the one i am concerend with
preventing access to before 1st November every year. I click on the
button to open this 'Orders' form and for some reason it is allowed to
open. I then open the module to view the code, and run it directly
from there using the 'Run' button and the recently opened 'Orders'
form is closed. The code will then work every time after that (I'm not
sure on this but i think it might be because the Date function can't
get the date for some reason unless the code is directly run ). I
don't see the point however, in having a piece of code to prevent
access to a particular form if it will only work if the code has
already been directly run.