verify date value after calendar updates text box 1

[THWatson]

My form has a text box control called "txtEndDate"

Single click in the control enters the current date. However, I do not want the user to be able to enter the current date if it is less than the last day of the current month. In that case, I want to date shown as the last day of the previous month.

Private Sub txtEndDate_Click()
Me.txtEndDate = Date
If Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = Date
End If
End Sub

Double click in that control brings up a Calendar form. However it would still be possible for the user to select a date that is less than the last day in the current month...so I want the same rules to apply.

The code behind double click is

Me.txtEndDate = Null

'Code sample from Accessory [URL unfurl="true"]http://www22.brinkster.com/accessory/[/URL]
    Set ctlIn = Me.Controls("txtEndDate")
    DoCmd.OpenForm "frmCalendar"

As soon as a date is selected on the calendar form that date gets plugged into txtEndDate and the calendar closes.

How do I ensure that the same rules are followed and if the date selected in the calendar is less than the last day of the current month, it automatically defaults to the last day of the previous month?

I have tried the following, plus several other options. But all result in an error.

Me.txtEndDate = Null
Dim txtEndDateOld As Variant

'Code sample from Accessory [URL unfurl="true"]http://www22.brinkster.com/accessory/[/URL]
    Set ctlIn = Me.Controls("txtEndDate")
    DoCmd.OpenForm "frmCalendar"
Me.txtEndDate = txtEndDateOld
If Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = txtEndDateOld
End If

Thanks.

Tom

 

[PHV]

Have you tried to enforce your rule in the LostFocus event procedure of txtEndDate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

That does it.

Thanks, PHV.

Tom

 

[THWatson]

PHV
Actually, even though that works, I can't use it.

The problem is that when Start and End dates are entered in the appropriate text boxes, there are 2 possible subforms that can be opened once those dates are selected, and for the instance of one I want the End Date to stick, and for the other I want to End Date to revert to the last day of the previous month.

I'll have to play around with it some more.

Tom