Microsoft Access Date/Time Picker

While doing some work on a time-card application, I had a need to limit the time values that a user could enter into a date time field, but I could not find a time picker for Access so I developed my own.  I already had a date picker, so I simply had to modify that to accommodate times, and then figure out a way to set those times to certain intervals.

In this application, you will find a method to pop up either a date picker, a time picker, or a date-time picker.

  It does involve a bit of coding, but not much, and it is extremely flexible.  All you have to do to get started is import four objects (tbl_Minutes, qry_TimeIntervals, frm_Calendar, and mod_DateTimePicker) and a shortcut menu, from the sample database, into your application.  Note, in order to import the shortcut menu, you will need to click the Options button on the Import Objects dialog (External Data) and ensure that the "Menus and Toolbars" option is checked.

The sample database contains these objects and an additional Sample Form which shows a variety of ways to raise frm_Calendar.  You can do it from the right-click menu, or virtually any other control related event. I've limited this example database to right-click (shortcut menu), double_click, button, and click events, but I could see where you might want to use the GotFocus event as well.

The key to the whole operation is the shortcut menu (DateTimePicker)  and a set of functions which set the default values for several variables and several parameters of the shortcut menus single control.  

The shortcut menu contains an OnAction event which calls fnDateTimePicker, which is responsible for determining:

1.  Which controls to display

2.  What control the date/time data is supposed to come from/go into,

3.  The current values (date and/or time) of that control or the default values if the control is blank.

4.  Positioning the form when it pops up.

5.  Pushing the selected date/time values back to the calling form

The functions which set the default values are:

SetDatePicker

SetDateTimePicker

SetTimePicker

These functions can be called in a variety of ways, but I generally use the GotFocus event of the control (as seen here) or the click event of a button.

This particular function (SetTimePicker) sets the time interval to only display time in 10-minute intervals, starting at midnight.  It also includes optional parameters for TimeRangeStart and TimeRangeEnd to provide the user with options to restrict times to a certain range (for example the standard work day).

Public Function SetTimePicker(Optional Minutes As Integer = 15, _
    Optional TimeRangeStart As Date = #12:00:00 AM#, _
    Optional TimeRangeEnd As Date = #11:59:00 PM#, _
    Optional ControlName As String)

    TempVars!TimeRangeStart = TimeRangeStart
    TempVars!TimeRangeEnd = TimeRangeEnd
    TempVars!ControlName = ControlName
   
    CommandBars("DateTimePicker").Controls(1).Caption = "Time Picker"
    CommandBars("DateTimePicker").Controls(1).Parameter = Minutes

End Function

It also includes an optional parameter for the ControlName, which allows the user to call the DateTimePicker from a command button (the default is to use the active control on the form).

Private Sub cmdTimeOnly_Click()

    SetTimePicker 30, #7:00:00 AM#, , "Text16"
    CommandBars("DateTimePicker").Controls(1).Execute

End Sub

When run, the application defaults to the current day and the most recent time prior to Now(), based upon the user selected time interval (defaults to 15 minutes).  If there are already date/time values in the control for which the DateTimePicker is being used, those values will be populated in both the date picker and the time picker.

The current version of the sample database is always available for downloaded from my website.

Dale Fye

Dev-Soln, LLC