Date Picker Events

[Snowflake68]

I have searched the web for a solution for this but I havent been able to resolve my issue despite others having similar issues.

I have a form with a text box using the built in date picker. I want the user to be able to manually type in a date or choose one from the date picker.

Once they have enter the date (by any of the above methods) I want to requery a subform.

I have the date picker working to requery the subform by using the OnChange event however if the user tries to type in a date the onchange event fires before the user has had chance to enter the whole date.

The OnChange event clearly isn't the event to use here as it is triggering as soon as they type a single character, so I changed it to the afterupdate event so that the user can manually type in a date and then requery the form but then I need to find a way to requery the subform after a date has been picker from the picker.

This has been driving me mad for most of the day.

I am using Access 2019.

 

[Ranman256]

don't use ON CHANGE. every letter is a change.
instead use AFTERUPDATE. then the app knows the entry is finished.

 

[bob fitz]

I have searched the web for a solution for this but I havent been able to resolve my issue despite others having similar issues.

I have a form with a text box using the built in date picker. I want the user to be able to manually type in a date or choose one from the date picker.

Once they have enter the date (by any of the above methods) I want to requery a subform.

I have the date picker working to requery the subform by using the OnChange event however if the user tries to type in a date the onchange event fires before the user has had chance to enter the whole date.

The OnChange event clearly isn't the event to use here as it is triggering as soon as they type a single character, so I changed it to the afterupdate event so that the user can manually type in a date and then requery the form but then I need to find a way to requery the subform after a date has been picker from the picker.

This has been driving me mad for most of the day.

I am using Access 2019.

I've just tried this and the After Update event doesn't seem to fire when using the date picker.
How about doing a Requery of the Subform in both events but in the OnChange event put the Requery command in an If/Then so it only re queries the subform if it is a date. Something like:

If IsDate(Me.ActiveControl.Text) Then
Me,SubFormName.Requery
End If

 

[theDBguy]

The OnChange event clearly isn't the event to use here as it is triggering as soon as they type a single character, so I changed it to the afterupdate event so that the user can manually type in a date and then requery the form but then I need to find a way to requery the subform after a date has been picker from the picker.

I, on the other hand, would say it is the perfect event to use; except, check if you have a full date first before requerying the subform.

 

[MajP]

I did not play with it but you could use a custom date picker which is better anyways. Then you can control it the way you like

A better date picker

Some months ago I uploaded a replacement date picker (see DatePicker.accdb attached) that has no Active X controls and can also be used in 64-bit Access. It was originally posted in response to a question about Stephen Lebans' calendar in this thread...

www.access-programmers.co.uk

 

[Uncle Gizmo]

No it can't be done with normal MS Access Controls, however there is a workaround. You might be able to adapt Colin's excellent Date Picker, however I've rolled my own "Nifty Date Picker" ... I've added the facility to requery a sub-form. See the sample attached...

 

[Deleted member 147267]

IsDate(Me.ActiveControl.Text)

post correction:
In the Change event, this will accept anything equal to or greater than 01/01/100 as a valid date so you will end up requerying the form before the entry is finished if it's being typed in. Also, if the control is bound it may already have a value in it, so just checking if IsDate on the control will apply to the existing value thus will requery regardless of what the control text property value is. I doubt there's a way to cover both using the picker and typing which might be one reason why people use a custom solutions, but I would start with a combination of the Change and Dirty events if I were to try. What I would do is simply tell people to tab off the control, or provide a small button to "apply changes" .

 

[MajP]

You can try this, seems to work but have not tried all possibilities. However, the other ideas are better (custom or click off the field)

Private Sub theDate_Change()
  Dim aDate() As String
  If IsDate(Me.theDate) Then
    Debug.Print theDate.Text & "  " & Format(theDate, "mm/dd/yyyy")
    aDate = Split(Me.theDate.Text, "/")
    If UBound(aDate) = 2 Then
      If Len(aDate(2)) = 4 Then
        MsgBox "Change"
      End If
    End If
  End If
End Sub

The gist is that it has to be a date, has to have two /, and the year has to be 4 places.

 

[The_Doc_Man]

There is another possibility... to enter a date OR to pick a date, you have to bring focus to the control. So use the control's LostFocus event and before doing the requery, do a comparison of .Value to .OldValue and only do the requery if they are different. Any other control you click will change focus so that might be a viable option.

 

[Deleted member 147267]

If the control is only for requerying the form, it should be unbound otherwise every change made for the purpose of requerying a subform will cause a data edit as well. If it's unbound there is no OldValue property, yes? A lot of speculation from us with no more response from OP. I alluded to the control being bound but no answer on that yet. So far I see this as a case of making something more complicated than it needs to be.

 

[Snowflake68]

If the control is only for requerying the form, it should be unbound otherwise every change made for the purpose of requerying a subform will cause a data edit as well. If it's unbound there is no OldValue property, yes? A lot of speculation from us with no more response from OP. I alluded to the control being bound but no answer on that yet. So far I see this as a case of making something more complicated than it needs to be.

Apologises for the lack of response, just been hectic with work. I haven't read through all the responses yet so I will do next in the hope to find a solution but just to answer the question about whether the control is bound or not, its not. It is an unbound control merely used to filter the dataset on the subform.

 

[Snowflake68]

I, on the other hand, would say it is the perfect event to use; except, check if you have a full date first before requerying the subform.

Sounds like this could work but how do I test for a full date first? I did try doing a length measure but it always seems to be 10 even when I have only typed a single character (i have tested this with a message box to check the length)

 

[Snowflake68]

I've just tried this and the After Update event doesn't seem to fire when using the date picker.
How about doing a Requery of the Subform in both events but in the OnChange event put the Requery command in an If/Then so it only re queries the subform if it is a date. Something like:

If IsDate(Me.ActiveControl.Text) Then
Me,SubFormName.Requery
End If

this works to a degree. What I mean is it works for the date picker and if entering a date from an emtpy text box but if you try and edit a date that is in the text box it requeries before you finish editing. for example if you have 17/02/2020 and try and change it to 10/02/2020 by deleting the 7 to enter a zero it triggers before you enter the zero because it sees 1/02/2020 as a valid date and then reformats it to 01/02/2020

 

[Snowflake68]

I think the only way to go here is to use the after update event to do the requery and then (although not ideal) just get the user to tab out of the control when they use the date picker. I will go with this for now and then perhaps I will come back to it another day when I am not up against time.

Thanks for everyone for trying to help here.

 

[Snowflake68]

There is another possibility... to enter a date OR to pick a date, you have to bring focus to the control. So use the control's LostFocus event and before doing the requery, do a comparison of .Value to .OldValue and only do the requery if they are different. Any other control you click will change focus so that might be a viable option.

thanks I may give this a go at some point when I find the time.

 

[Snowflake68]

You can try this, seems to work but have not tried all possibilities. However, the other ideas are better (custom or click off the field)

Private Sub theDate_Change()
  Dim aDate() As String
  If IsDate(Me.theDate) Then
    Debug.Print theDate.Text & "  " & Format(theDate, "mm/dd/yyyy")
    aDate = Split(Me.theDate.Text, "/")
    If UBound(aDate) = 2 Then
      If Len(aDate(2)) = 4 Then
        MsgBox "Change"
      End If
    End If
  End If
End Sub

The gist is that it has to be a date, has to have two /, and the year has to be 4 places.

I did try this but didnt work when editing a date

 

[Snowflake68]

No it can't be done with normal MS Access Controls, however there is a workaround. You might be able to adapt Colin's excellent Date Picker, however I've rolled my own "Nifty Date Picker" ... I've added the facility to requery a sub-form. See the sample attached...

something to try at a later date perhaps (no pun intended )

 

[Uncle Gizmo]

I actually broke my own hard-earned rule and said "it can't be done!" You'd be amazed how many times I've said that, and somebody's come along and shown me how it can be done! Reading the other contributors comments I think I may have picked up the wrong end of the Stick. My assumption was that you wanted the controls after update event to run after the selection of the Date. That's the problem I've got a solution for. (Not in the sample provided - that was just a quick and dirty answer) ...

A problem which annoyed me for many years was the fact that you could have a control (Say a Text box) with an adjacent command button for selecting the "Nifty Date Picker" to enter a date into to the text box. The annoyance for me, was that you could not use an after update event to trigger a calculation.

Let's say you were entering a birth date, and you had another control to display the person's age. Once you entered the birth-date into the birth day text box, you would expect the after update event would run and you could run some code to calculate and display the person's age. But this just does not work, I could not find any way round it. You either have to provide a separate command button, or rely on some other event to trigger this, which was not ideal.

This problem annoyed me so much I spent many days, weeks and probably years trying to solve it. This is actually a good thing, because this is when you really start to learn something! When you are consumed with trying to kill off some Niggly little thing that annoys you....

This quest lead me to build my own "CallCalled Class Module" which has the ability to detect a public function in the form opening the Nifty Date Picker. To be clear you have your main form, the one with your date text box on it, and a command button to open the date-picker, and within this form let's call it the main form, or an awkward but useful common name for it is "Calling Form" in this "Calling Form" you have a public function. Now you open the Pop-Up form and it follows from the Awkward name given to the main form to call it "Called Form" yet another awkward but very distinctive and useful description.

The "Called Form" opens the Call-Called Class Module and the class module gathers useful information from the "Calling Form" including a reference to the public function. So now when you close the "Called Form" it can use this public function to simulate the after update event that is annoyingly and awkwardly missing from this MS Access process...

Here are a Selection of Videos I've done explaining the CallCalled Class Module over the Years



Call Called Class - Nifty Access

Nifty Access - Call Called - Class Module

Popup Form - Easy - Nifty Access

 

[Snowflake68]

Thanks Unc for taking to time to share your experience (and frustrations). I am very interested in downloading the free db so would appreciate if you can message me with how to go about this.
Thanks again
Caroline

 

[Uncle Gizmo]

I am very interested in downloading the free db so would appreciate if you can message me with how to go about this.

Hi Caroline,
Just sign up to my YouTube channel here:- https://www.youtube.com/user/UncleGizmo?sub_confirmation=1