Date validation rule with popup built-in calendar (1 Viewer)

[Falcon88]

Hi dears
I have a form on it textbox for dates , it validation rule is between 01/01/2022 and 31/12/2022, i use a popup built-in calender form to select dates . But the validation rule didnot work except when user insert the date by keyboard or using the access popup calendar .

How to make that v.rule works by using that popup built-in calendar ?

 

[theDBguy]

Are you able to post a sample db to demonstrate the problem?

 

[Falcon88]

Are you able to post a sample db to demonstrate the problem?

Yes of course.

Later .

 

[Falcon88]

See to that sample ...

I use beforeupdate event with some code but didn't works good.

 

[Pat Hartman]

But the validation rule didnot work except when user insert the date by keyboard or using the access popup calendar .

I mentioned this in my earlier video but went into more detail on the newest one which hasn't been released yet. This is just ONE of the reasons I don't use the control's BeforeUpdate event or any control events for that matter, to validate data.

You don't need to create an account to watch the video. Sorry, it's a half hour.

Bad Data is Bad for Business - Pat & Tony - Video 1

Bad Data is Bad for Business - Pat & Tony - Video 1

screencast-o-matic.com

In summary, the control level events do NOT fire when the control is modified using VBA (or a macro). If you move the validation code to the Form's BeforeUpdate event, it will work correctly. NOTHING gets past the Form's BeforeUpdate event

Alternatives:
1. Use the built in calendar. It doesn't have the problem because it is part of the form, NOT a separate form.
2. For this particular situation (you are populating the value from a dialog form), if you open the calendar as a dialog, you can add a line of code after the line that opens the Calendar form to execute the Control's beforeUpdate event:

YourControlName_BeforeUpdate

Remember, when you open a form as a dialog, all code in the calling form STOPS. when the dialog form closes, the code in the calling procedure picks up on the next line and continues to the end. This only works when the form is opened as a dialog. otherwise, code following the OpenForm method runs while the form is open and so it can't help you.

 

[Falcon88]

I mentioned this in my earlier video but went into more detail on the newest one which hasn't been released yet. This is just ONE of the reasons I don't use the control's BeforeUpdate event or any control events for that matter, to validate data.

You don't need to create an account to watch the video. Sorry, it's a half hour.

Bad Data is Bad for Business - Pat & Tony - Video 1

Bad Data is Bad for Business - Pat & Tony - Video 1

screencast-o-matic.com

In summary, the control level events do NOT fire when the control is modified using VBA (or a macro). If you move the validation code to the Form's BeforeUpdate event, it will work correctly. NOTHING gets past the Form's BeforeUpdate event

Thanks Mr Pat Hartman .
Have you downloaded my attached file ?

 

[Pat Hartman]

ActiveX controls don't just float around in the ether, they need to be installed on every single computer where you run your application which is why you should use alternative methods. You will find this out when you try to install the application on a user's computer.

Opening the ActiveX control is not the same as opening a form in dialog mode.

Did you try to add the line of code I suggested AFTER the return from the ActiveX?

Did you watch my video?

 

[Falcon88]

Mr Pat

See , i use this expression on the orederdate testbox validation rule :

<DateSerial(Year(Elookup("MyDefaultDate";"tblDefaultDate";"MyDefaultDayID=1"));1;1) And >DateSerial(Year(Elookup("MyDefaultDate";"tblDefaultDate";"MyDefaultDayID=1"));12;31)

Then use this code

Option Compare Database
Option Explicit
Private Sub cmdCalendar_Click()
InputDateField OrderDate, "Select Order Date"
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ValideStDate As String
Dim ValideEnDate As String

ValideStDate = SQLDate(DateSerial(Year(ELookUp("MyDefaultDate", "tblDefaultDate", "MyDefaultDayID=1")), 1, 1))
ValideEnDate = SQLDate(DateSerial(Year(ELookUp("MyDefaultDate", "tblDefaultDate", "MyDefaultDayID=1")), 12, 31))

If Me.OrderDate < ValideStDate Then
MsgBox "AAAAAAAAA"
Cancel = True
Me.OrderDate.Undo
End If
If Me.OrderDate > ValideEnDate Then
MsgBox "zzzzzzzzzz"
Cancel = True
Me.OrderDate.Undo
End If
End Sub

Private Sub OrderDate_BeforeUpdate(Cancel As Integer)
'Dim ValideStDate As String
'Dim ValideEnDate As String
'
'ValideStDate = SQLDate(DateSerial(Year(ELookUp("MyDefaultDate", "tblDefaultDate", "MyDefaultDayID=1")), 1, 1))
'ValideEnDate = SQLDate(DateSerial(Year(ELookUp("MyDefaultDate", "tblDefaultDate", "MyDefaultDayID=1")), 12, 31))
'
'If Me.OrderDate < ValideStDate Then
'MsgBox "AAAAAAAAA"
'Cancel = True
'Me.OrderDate.Undo
'End If
'If Me.OrderDate > ValideEnDate Then
'MsgBox "zzzzzzzzzz"
'Cancel = True
'Me.OrderDate.Undo
'End If
End Sub

 

[Pat Hartman]

I told you why your code didn't work. You can try one of my THREE suggestions or argue that you are correct.

 

[arnelgp]

i created new frmOrder, because your old form is using Arabic control names so
i cannot make it work.
see frmOrder and the code on the button and the Orderdate.

 

[Falcon88]

i created new frmOrder, because your old form is using Arabic control names so
i cannot make it work.
see frmOrder and the code on the button and the Orderdate.

Thanks , please reattaches this db with ms access 2007 format .

 

[arnelgp]

Thanks , please reattaches this db with ms access 2007 format .

it's been too long, i hope i still follow your code.
here is an access 2007 version. open frmOrders_agp.