Reconfigure a date on enter on a form

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Reconfigure a date on enter on a form

Post by Jeff H »

I have controls for a few filter parameters on a form. Although I have Start and End date textboxes, I only want to filter for full months. So if a user enters any date other than the first or last day of a month, I want the textbox to display them as first and last.

Here's what I've tried but it isn't working. Is there a way I can do it? Or is it just another nutty idea I should abandon?

Code: Select all

Private Sub txtEndDate_BeforeUpdate(Cancel As Integer)
Dim lYear As Long
Dim lMonth As Long
Dim lMonth2 As Long
Dim lDay As Long

If Not IsNull(Me.txtEndDate) Then
    If IsDate(Me.txtEndDate) Then
        lYear = Year(Me.txtEndDate)
        lMonth = Month(Me.txtEndDate)
        lMonth2 = DateAdd("m", 1, lMonth)
        lDay = Day(DateSerial(lYear, lMonth2, 0))
        Me.txtEndDate = DateSerial(lYear, lMonth, lDay)
    End If
End If

End Sub

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reconfigure a date on enter on a form

Post by HansV »

Instead of the Before Update event, use the After Update event.
If you set the Format property of the text box to one of the date formats, the user can only enter a date, so you don't need to check for that.

Code: Select all

Private Sub txtEndDate_AfterUpdate()
    If Not IsNull(Me.txtEndDate) Then
        Me.txtEndDate = DateSerial(Year(Me.txtEndDate), Month(Me.txtEndDate) + 1, 0)
    End If
End Sub
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Reconfigure a date on enter on a form

Post by Jeff H »

I don't have time to test it right now, but it looks easy and sounds right. I'll let you know after I've plugged it in.

Thanks Hans!

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Reconfigure a date on enter on a form

Post by Jeff H »

Yep! Works great. It's very helpful to me to see well written code.