DTPicker Issue on Access Form

I am attempting to use DTPicker to update a Access form.  I have a SQL statement to refresh my report after making a new selection from the dropdown calendar.  My problem is that I can seem to get the form to reconize that I selected a new date. Adding my code to On Updated doesnot seem to work.  Lost Focus works, but it is not the solution I'm looking for.  How do I reconize that the DTPicker date has changed?

Bob StammOperations ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This probably isn't what you want to hear, but I'd scrap the DT Picker and use the calendar control.  If you place the calendar control on a form, you can make it look like a pop up calendar similar to the DT Picker.  After that, its very easy to set and extract data from.

Why go to this trouble?  Because it saves tons of headaches in the present and future.  I was pulling my hair out dealing with issues like the one you are grappling with, and there are many more.  Plus, the DT Picker often wasn't recognized on other people's computers, making it more troublesome.

If you go the route of the calendar control, I'll give you some code syntax to help you get going...
Bob StammOperations ManagerAuthor Commented:
Thanks for your input.  This was the first time I attempted to use the DT Picker.  I'll take any suggestion you have for the calendar control.

No worries.  Before you go forward, I'm not saying the DT Picker is unusable.  I'm sure there are many who use it and like it.  I just don't happen to be one of them.  This process might look a bit lengthy, but its not as bad as it looks.  After you do this once, connecting all your other 'date' textboxes to the calendar control form is easy.

1.  I created a new form, place a calendar control on it, and some buttons "Select", "Clear" and "Cancel"
2.  On the form where the DT Picker was, place a textbox and small button next to it.  In the button's properties, under Format, select Picture and "..." and you can scroll down to 'calendar'.  You may need to resize your button after the picture has been selected.  Set the textbox's locked property to true
3.  In a module, I created a variable 'calledFrom'
4.  In the click event of the calendar button:

calledFrom = "TextBoxName"   'use whatever string you want here to identify the location of the textbox
docmd.openform ("Calendar")  'use the name of the form the calendar control sits on

5.  In the calendar form's module (at the top, below option explicit) :

Dim aControl As Control

6.  In the load event of the calendar form (small extract from my code - you'll have to use your own form names, control names and string values for 'calledFrom')  My example here shows two different places that use this form :

Private Sub Form_Load()
    If calledFrom = "Agreement-Expiry" Then
        Set aControl = Forms("Agreements").Controls("txtExpiryDate")
    ElseIf calledFrom = "Agreement-Potential" Then
        Set aControl = Forms("Agreements").Controls("txtPotentialDate")
        MsgBox "The calendar is not properly referenced from this object.", vbInformation
    End If
    If IsNull(aControl.Value) Or aControl.Value = "" Then
        objCalendar.Value = Date
        objCalendar.Value = aControl.Value
    End If
End Sub

7.  In the 'Cancel' button click event :

Private Sub cmdCancel_Click()
End Sub

8.  In the 'Clear' button click event :

Private Sub cmdClear_Click()
    aControl.Value = ""
End Sub

9.  In the 'Select' button click event:

Private Sub cmdSelect_Click()
    If objCalendar.Day <> 0 Then
        aControl.Value = objCalendar.Value
        MsgBox "A day has not been chosen.", vbInformation
    End If
End Sub

Hope I haven't overwhelmed you with code and instructions.  Let me know and I can explain or help you in greater detail.  Once all this is in place, you can easily connect all your date textboxes to the calendar form.
It gets easier the second time around :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I am interested in using this code, and I am a novice.  I understand the overall concept of the solution, but I am not sure how to do this step

"3.  In a module, I created a variable 'calledFrom'"

I think that means do something like this "Dim calledFrom as Variant"

But I am not sure where to put that statement.  The solutions says to put it "in a module" but I am assuming that I cannot put it in any module, and if I can, what should the module name be?

Whenever I click the open form button I get the "The calendar is not properly referenced from this object." which I think means that the calledFrom statement might be in the wrong spot.  Currently I have it in the function that is assigned to the button to open the calendar:

Private Sub Command37_Click()
Dim calledFrom As Variant
calledFrom = "EndStartDate"
DoCmd.OpenForm ("frmcal")
End Sub

Any ideas?
Declare the calledfrom variable in any module (not a form).

Public calledFrom as string

If you don't make it public, the calendar form won't know what calledFrom is, which is why you got that message.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.