Quick Date Picker question using text box

Question

Have two text boxes set to date format.  I have a subform that is fed from a query.  This query only shows records between the two dates in the above text boxes.  My issue is, when someome clicks on the text box, the date picker shows.  When you click the date you want, the text box "shows" the new date, but the subform doesn't update until I hit the enter button or click on another textbox in the form.  All my code runs on the change event of that text box.  But, I would like after the user clicks the date he/she wants from the date picker, to simulate the "enter" key being hit.  Just trying to stream line things.  People just don't like having to click other objects when they don't need to. 

Answer 1

You can add code to the Timer event to refresh the Form every second or so then after the date is selected the timer event will process and update. I can't think of anything else as there is no access to (AFAIK) the date pickers selection end code.

HTH

---

Chris Ward

Answer 2

put this in your on change event of the text box

Private Sub theDate_Change()
    
    'what ever other code you have

    If IsDate(theDate) Then
        Me.[NameOfNextControl].SetFocus
    End If
End Sub

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 3

The problem isn't that the text box's Change event doesn't fire when the date picker is used;  it does.  However, it also fires whenever the user types any character in the text box, even before they have finished typing their date entry.  What you would ideally want to use for requerying your subform is the text box's AfterUpdate event, but the date picker doesn't raise that event.  So what we need is a way to distinguish, in the Change event, whether the date picker was used or the user is actually typing in the control, and call the AfterUpdate event if the date picker was used to change the text box.

I don't think -suzyQ's approach, checking in the Change event whether the text box represents a date or not, will work in all cases, because (a) you'd need to be looking at the control's .Text property, not its .Value property, and (b) even an incomplete value can pass the IsDate() test.  For example, if I am intending to type "8/20/2012", IsDate() will return True for "8/2", "8/20", "8/20/2", "8/20/20", and "8/20/201", long before I get to "8/20/2012".

I just tried an idea that passed a quick test.  The idea was to check whether the user had typed any key in the text box before the Change event was raised.  Here's sample code:

Option Compare Database
Option Explicit

Dim mblnKeyPressed As Boolean

Private Sub txtDate_AfterUpdate()

    Me.Requery

    mblnKeyPressed = False
    
End Sub

Private Sub txtDate_Change()

    If mblnKeyPressed = False Then Me.Requery
    
End Sub

Private Sub txtDate_KeyPress(KeyAscii As Integer)

    Select Case KeyAscii
        Case vbKeyTab, vbKeyReturn ' ignore
        Case Else
            mblnKeyPressed = True
    End Select
    
End Sub

I've only done some preliminary tests, so the approach could still be flawed, but it seems to work so far.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

I don't think -suzyQ's approach, checking in the Change event whether the text box represents a date or not, will work in all cases, because (a) you'd need to be looking at the control's .Text property, not its .Value property, and (b) even an incomplete value can pass the IsDate() test.  For example, if I am intending to type "8/20/2012", IsDate() will return True for "8/2", "8/20", "8/20/2", "8/20/20", and "8/20/201", long before I get to "8/20/2012".

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Good to know.  I didn't of that.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 5

I think Dirk's approach is best. But I wonder if you can include a validation Rule to it where the data must be like;

##/##/####

---

Chris Ward