Thanks for the response, Allen. Your comments and your site have
always helped me out and are a real value to the community.
Your suggestion is on target, but I was worried about about one
problem I didn't list: sometimes the users will legitimately need to
enter a past date. As a result, I need to only move the date when a
user enters a short date like:
m/d, mm/d, mm/dd, or m/dd (or the same, except with a space instead of
a slash). These are the cases that Access expands out to a full date
in the current year.
To solve this, I've taken an example from your site (which is great!)
and customized it. I've modified the items from
http://allenbrowne.com/ser-34.html
into this subroutine that moves dates. Hopefully it helps someone in
the future. Allen, feel free to comment if you see something that
could be improved... especially if you know of a way to catch the
value of a bound field before a mouse click (see below).
Thanks again,
Scott
------------------------------------------------------------
Sub subFutureDates(ctl As Control, Optional KeyAscii As Integer)
' Purpose: If a user enters a date without a year, always set
that to a future date.
' These dates can be: m/d, mm/d, mm/dd, or m/dd (or
the same, except with a space instead of a slash).
' Usage: Call the subroutine from the control's KeyDown event
procedure:
' Call subFutureDates(Me.MyTextBox, KeyAscii)
' And call the subroutine from the control's KeyDown
event procedure, omitting the optional KeyAscii
' Call subFutureDates(Me.MyTextBox)
'
' NOTE: You must put this in the KeyDown event, NOT
KeyPress
' KeyPress will not capture return or tab keys
' (See MSDN article titled, "Order of events
for keystrokes and mouse clicks")
'
' NOTE: If you have a control bound to a date field, you can
catch users exiting the field using a
' tab or enter keystroke, but you WILL NOT be able to
catch a mouse click before the date changes.
' Access modifies the date as soon as a user clicks out
of the field, before any events fire
' So, by the time you can grab the date you won't be
able to tell what was entered.
' One way to work around this: make your field unbound,
then copy the value after you clean it up.
'
' Error handling: add your own as desired!
Dim dteDateValue As Date
'KeyAscii is optional in order to allow us to call this from
'a control's exit event
'We only act if the user hits tab or enter or if KeyAscii is null
If (KeyAscii = Asc(vbTab)) Or (KeyAscii = 13) Or (KeyAscii = 0)
Then
'To avoid an error, we'll set the variable to today
dteDateValue = date
'First, check if the entry is either large enough to be a full
date
'or is too small to be a valid date
'If it is either, then then we'll leave it and exit
'The smallest possible full date is like 1/1/1, so five
characters
'The smallest possible month/date combo is three characters,
like 1/1
If (Len(ctl.Text) >= 5) Or (Len(ctl.Text) < 3) Then Exit Sub
'OK.. we have a partial date
'Check if the date would be before today
'To keep this simple, I'm NOT validating that this value can
be a date
'Instead, we'll just resume next...
On Error Resume Next
dteDateValue = CDate(ctl.Text)
On Error GoTo 0
'Now, if the date is less than today, then we'll move it...
If dteDateValue < date Then
'The date is before today, so let's move it to next year
dteDateValue = DateAdd("yyyy", 1, CDate(ctl.Text))
ctl.Text = dteDateValue
End If
End If
Exit Sub
