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!
