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

 

[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 )

 

[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