I won’t lie, I hate the built-in MS Access Date Picker. Once again, Microsoft did things as poorly as possible by implementing the most primitive Date Picker possible. One that only moves forwards or backwards one month at a time. No Month selector. No Year selector. No ability to interact with it through VBA!

Try using the built-in Date Picker to enter someone’s birthdate. For instance, try using it to enter April 1st, 1941. I hope you have some time on your hands as you’re going to have to click on the past month button 907 TIMES!!!

With a great many available Date Pickers to inspire themselves with/from, I still cannot remotely grasp how this one was adopted. We’re supposed to facilitate the end-user’s jobs by simplifying data entry, not making their lives a living nightmare.

Okay, enough with the rant, let’s move forward and see how we can actually help our users! The reality is that the best thing we can do is adopt one of many form based date pickers like one of the numerous ones I listed in my post: https://www.devhut.net/2017/06/17/great-access-tools-calendar-controls/.

That said, we have to first disable the built-in Date Picker from always displaying (possibly overtop of our calendar button). Now, we can open each form individually and then edit the Show Date Picker property of each date control, but this is time consuming and one can easily overlook a control or two. As such, I set out to find a better way than having to manually edit individual form control properties and knew that we could harvest the power of VBA to rescue us from this Date Picker affliction.

As such, I put together the following code which iterates through all the forms, and all the controls within each form to change the property (sadly, there is no global database setting and it has to be set control by control!).

'---------------------------------------------------------------------------------------
' Procedure : disableDatePicker
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Disable the built-in date picker in all the database forms.  This is to
'             avoid issue with using the calendar form that we have chosen to implement.
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Usage:
' ~~~~~~
'
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-Oct-09                 Initial Release
'---------------------------------------------------------------------------------------
Sub DisableDatePicker()
    On Error GoTo Error_Handler
    Dim ctl             As Access.Control
    Dim frm             As AccessObject

    For Each frm In CurrentProject.AllForms
        DoCmd.OpenForm frm.Name, acDesign

        For Each ctl In Forms(frm.Name).Form.Controls
            Select Case ctl.ControlType
                Case acTextBox
                    ctl.ShowDatePicker = 0
            End Select
        Next ctl

        DoCmd.Close acForm, frm.Name, acSaveYes
    Next frm

Error_Handler_Exit:
    On Error Resume Next
    Set ctl = Nothing
    Set frm = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: DisableDatePicker" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

This is now one of the first procedures that I run on databases that I take over as part of my cleanup routine. Hopefully it can help other too!