DTPicker Issue on Access Form

Posted on 2004-11-04
Last Modified: 2012-06-21
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?

Question by:RobertStamm
    LVL 6

    Expert Comment

    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...

    Author Comment

    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.

    LVL 6

    Accepted Solution

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

    Expert Comment

    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?
    LVL 6

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now