Open form code to enter a date in short date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tandy - 30 Jun 2005 16:36 GMT

Recently I made a form that prompts my user for the year they would like to
enter with the code below. I would like to modify the code to prompt my user
for the date they would like to enter in short day format. So when my user
opens the form, I would like for the user to be prompted for the date they
would like to enter (1/1/2005) and have that date shown in a textbox called
txtShowMonth. I have tried several times to do this myself, but I don't have
a good enough grasp of code. Also, I do not want any suggestions for
alternatives (such as a pop up calendar), this is how I need this to be for
my form... sorry :(

Please help!

Private Sub Form_Open(Cancel As Integer)

Dim strShowYear As String
Dim blnDone As Boolean

' Prompt the user for the year to be shown initially.
' We'll suggest next year as a default.
' In case of erroneous entry, continue prompting until
' a valid year is given, or the prompt is cancelled.

Do
strShowYear = InputBox( _
"What year do you want to edit?", _
"Enter Year", _
CStr(Year(Date) + 1))

Select Case True
Case (Len(strShowYear) = 0)
Cancel = True
blnDone = True

Case (Not IsNumeric(strShowYear)), _
(Val(strShowYear) < 1800), _
(Val(strShowYear) > 5000)
MsgBox "That's not a valid year!", _
vbExclamation, "Invalid Entry"

Case (Val(strShowYear) < (Year(Date) - 10)), _
(Val(strShowYear) > (Year(Date) + 10))
If MsgBox( _
"You entered " & strShowYear & _
", which seems odd. " & _
"Are you sure?", _
vbQuestion + vbYesNo, _
"Please Confirm") _
= vbYes _
Then
blnDone = True
End If

Case Else
blnDone = True

End Select

Loop Until blnDone

If Cancel <> True Then
Me.txtShowYear = strShowYear
Me.Requery
End If

End Sub

Reply to this Message

Marshall Barton - 30 Jun 2005 23:21 GMT

>Recently I made a form that prompts my user for the year they would like to
>enter with the code below. I would like to modify the code to prompt my user
[quoted text clipped - 56 lines]
>
>End Sub

The open event is too early to assign a control's value.
try moving the code to the Load event.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Tandy - 01 Jul 2005 16:20 GMT

Marsh,

Hi. Thank you for your suggestion. I tried moving my code to the Load
event, but it didn't work. I also have to say that on my last form I used
this exact code on the On Open event and it works perfect. Even in my new
form I can get the dialog box to pop up asking for a date. The problem I am
finding is that when enter a date in short date format it tells me it is
invalid. So basically I need to change the code to input a date in short date
format.

> >Recently I made a form that prompts my user for the year they would like to
> >enter with the code below. I would like to modify the code to prompt my user
[quoted text clipped - 59 lines]
> The open event is too early to assign a control's value.
> try moving the code to the Load event.

Reply to this Message

Marshall Barton - 01 Jul 2005 21:59 GMT

Arrggghhhh, I got so wrapped up in all that code that I
forgot the part about changing the input box to get a full
date instead of just a year. Sorry about that.

I still say you should put it in the Load event.

Note that you can not prevent a user from entering a valid
date in any acceptable format without going to a lot of
trouble. Besides, why should you care how they enter it as
long as it's valid?

To check if the entered date is a valid date, add another
test after the InputBox:

    strShowYear = InputBox( _
            "What year do you want to edit?", _
            Format(DateSerial(Year(Date) + 1, 1, 1), "m/d/yy")
    If (Not IsDate(strShowYear)) Then
        MsgBox "Indecipherable date"
            . . .
    End If

Then convert the string to a date and put in the text box:
    Me.txtShowMonth = CDate(strShowYear)

Now you can use the standard date functions (Year, Month,
etc) for all your other checks.

Signature

Marsh
MVP [MS Access]

> Hi. Thank you for your suggestion. I tried moving my code to the Load
>event, but it didn't work. I also have to say that on my last form I used
[quoted text clipped - 67 lines]
>> The open event is too early to assign a control's value.
>> try moving the code to the Load event.

Reply to this Message

Tandy - 05 Jul 2005 17:41 GMT

Marsh,

Still no luck. The code below is the one I currently have in my form's
load event. I made what I thought were appropriate changes, such as changing
strShowYear to strShowMonth, getting ride of things under "Select Case True"
that validated a year entered and making the changes you suggested. However,
when I open the form I get an error telling me "Procedure declaration does
not match description of event or procedure having the same name." I
desperately want to complete this project, please keep helping.

Private Sub Form_Load(Cancel As Integer)

Dim strShowMonth As String
Dim blnDone As Boolean

' Prompt the user for the year to be shown initially.
' We'll suggest next year as a default.
' In case of erroneous entry, continue prompting until
' a valid year is given, or the prompt is cancelled.

Do
strShowMonth = InputBox( _
"What month do you want to edit?", _
"Enter month", _
CStr(DateSerial(Year(Date) + 1, 1, 1)))

strShowMonth = InputBox( _
"What year do you want to edit?", _
Format(DateSerial(Year(Date) + 1, 1, 1), "m/d/yy"))

If (Not IsDate(strShowMonth)) Then
MsgBox "Indecipherable date"

Select Case True
Case (Len(strShowMonth) = 0)
Cancel = True
blnDone = True

End If

Case Else
blnDone = True

End Select

Loop Until blnDone

If Cancel <> True Then
Me.txtShowMonth = CDate(strShowMonth)
Me.Requery
End If

End Sub

Reply to this Message

Marshall Barton - 05 Jul 2005 19:04 GMT

> Still no luck. The code below is the one I currently have in my form's
>load event. I made what I thought were appropriate changes, such as changing
[quoted text clipped - 47 lines]
>
>End Sub

It looks like you just changed the name of the procedure,
but forgot to adjust the procedure's arguments. The Load
event procedure does not have a Cancel argument.

Your new code is prompting the user twice, once for a year
and again for the month, but you are wiping out the year
value with the month value. You also have an End If in the
wrong place and are converting the month value in the wrong
place.

You may have a good reason for some of that, but it's not
what I thought you said you wanted to do. Let's try this
and see if it's any closer to what you want:

Private Sub Form_Load()
Dim strShowDate As String
Dim dtShow As Date

' Prompt the user for the DATE to be shown initially.
' We'll suggest next year AND MONTH as a default.
' In case of erroneous entry, continue prompting until
' a valid year is given, or the prompt is cancelled.

Do While True
    strShowDate = InputBox( _
        "What year and month do you want to edit?", _
        "Enter year and month", _
        Format(DateAdd("yyyy", 1, Date), "mmm yyyy"))

    If IsDate(strShowDate) Then
        dtShow = CDate(strShowDate)
        If (dtShow < DateAdd("yyyy", -10, Date)) _
                Or (dtShow > DateAdd("yyyy", 10, Date)) Then
            If MsgBox("You entered " & _
                Format(dtShow, "m/d/yyyy") & _
    ", which seems odd. Are you sure?", _
     vbQuestion + vbYesNo, "Please Confirm") _
                    = vbYes Then
Me.txtShowMonth = dtShow
Exit Do
End If
Else
Me.txtShowMonth = dtShow
Exit Do
End If
Else
MsgBox "Indecipherable date"
End If
Loop
End Sub

The date that a user can enter can be any valid date
(8/14/05), including only a month number and four digit year
(8/2005) or a month name or abbreviation and two or four
digit year (Aug 05 or August 05)

Signature

Marsh
MVP [MS Access]

Reply to this Message