Entering 24-hour time with input mask and full validation in Microsoft Access

To enter time is difficult

Entering time in a form in Microsoft Access can be difficult. There is no time picker like the date picker, and if there was, it would be too slow to operate for many users. Even worse, validation is only rudimentary, meaning that errors are often first caught in an unfriendly manner when the user tries to save the field or record.

One way to help the user is to set an input mask, but that adds other problems for the user if he/she clicks inside the textbox or deletes one or more digits, so many users hate input masks. Also, validation is difficult. For example, if the first hour digit is zero or one, the second hour digit can be any digit, but if the first is two, the second can not be higher than four.

How it should work

To have a textbox that just works for entering time takes several steps:

• Define an input mask that makes sense to the user
• Handle positioning of the cursor no matter how the user enters the textbox
• Enable entering of digits no matter how the user navigates in the textbox
• Catch all invalid inputs
• Provide reasonable default values to correct invalid inputs
• Set a format for the textbox

That may seem like a lot, but the outcome is a highly optimised control demonstrating the true power of an input mask.

It takes a lot to get it right

The first and the last item are quite simple.

The input mask is, as the first digit is optional:

and the format is:

This makes the textbox contain as default:

These can be set when the form loads:

Option Explicit

Dim DefaultTime     As Date

Private Sub Form_Load()
    
    Const InitialTime   As Date = #6:00:00 AM#
    
    Me!Logon.ShowDatePicker = False
    Me!Logon.InputMask = "90:00;1;0"
    Me!Logon.Format = "hh:nn"
    SetDefaultTime InitialTime

End Sub

That's surprisingly little, but then comes the code behind for the control.

First, set a default time. This may be dynamic (controlled by other code), but here it is static for simplicity:

Private Sub Logon_Enter()
  
    With Me!Logon
        If IsNull(.Value) Then
            .Value = DefaultTime
        End If
    End With

End Sub

Second, when clicking in the control, avoid ever having to select the separator (colon):

Private Sub Logon_Click()

    With Me!Logon
        If .SelStart = 2 Then
            ' Move the cursor off the separator (colon)
            ' to the first digit of minutes.
            .SelStart = 3
            .SelLength = 1
        End If
    End With

End Sub

Now comes the fun part -- to try to be smart, helping the user by correcting invalid values while still allowing the normal key entries for navigating inside the control as well as away from the control:

Private Sub Logon_KeyPress(KeyAscii As Integer)
  
    Dim Text        As String
    Dim Char        As String
    Dim Length      As Integer
    Dim SelStart    As Integer
    
    With Me!Logon
        Select Case KeyAscii
            Case vbKeyBack, vbKeyTab, Asc(vbLf), vbKeyReturn, vbKeyEscape, vbKeyF16
                ' Allow navigation etc. with
                ' BackSpace, Tab, Ctrl+Enter, Enter, Escape, Ctrl+BackSpace
            Case Is > 0
                Text = .Text
                Length = Len(Text)
                SelStart = .SelStart
                
                If KeyAscii < vbKey0 Or KeyAscii > vbKey9 Then
                    ' Replace any invalid entry with a zero.
                    KeyAscii = vbKey0
                End If
                
                Char = Mid(Text, 1 + SelStart, 1)
                If SelStart < Length Then
                    If KeyAscii <= vbKey0 + 2 Then
                        ' Always accept 0, 1, 2.
                    Else
                        ' Check if the text will represent a valid time.
                        ' If not, restore the overwritten digit.
                        Mid(Text, 1 + SelStart, 1) = Chr(KeyAscii)
                        If Not IsDate(Text) Then
                            DoCmd.Beep
                            KeyAscii = Asc(Char)
                        End If
                    End If
                End If
        End Select
    End With

End Sub

The in-line comments explain the function of the crucial parts of the code.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  
    Const TimeHourMaximum   As Integer = 24
    Const TimeHourDefault   As Integer = 20
    Const TimeMinuteTenMax  As Integer = 5
    
    Dim ctl                 As Control
    
    Dim Text                As String
    Dim SelStart            As Integer
    
    On Error Resume Next
    
    Set ctl = Screen.ActiveControl
    
    Select Case ctl.Name
        Case "Logon"
            Text = ctl.Text
            SelStart = ctl.SelStart
            If Not IsDate(Text) Then
                DoCmd.Beep
                If Val(Left(Text, 2)) > TimeHourMaximum Then
                    Mid(Text, 1) = CStr(TimeHourDefault)
                ElseIf Len(Text) > 3 Then
                    ' Length of Text is larger than two hour digits and the kolon.
                    Mid(Text, 1 + 3) = CStr(TimeMinuteTenMax)
                End If
            End If
            ctl.Text = Text
            ctl.SelStart = SelStart
            ctl.SelLength = 1
            Response = acDataErrContinue
    End Select

    Set ctl = Nothing

End Sub

Note, that it will always leave some time value in the control.

When done, you may set a new default value:

Private Sub Logon_AfterUpdate()

    With Me!Logon
        If IsNull(.Value) Then
            ' Rem this line out to allow the textbox to be cleared.
            .Value = #12:00:00 AM#
        Else
            SetDefaultTime DateAdd("n", 1, .Value)
        End If
    End With
    
End Sub


Private Sub SetDefaultTime(ThisTime As Date)

    DefaultTime = ThisTime
    Me!Logon.DefaultValue = Format(ThisTime, "\#hh:nn:00 AM/PM\#")

End Sub 

Note the conversion of the time value as a formatted string expression, as DefaultValue is a string.

Check it out

To test it for yourself, download and run the demo application. It is a simple form created in Access 2013  that should work as is for all versions of Access from 2007 to 2016. However, the code origins from Access 2.0, so it should be adoptable with minor changes for any version of Access.

• Code-behind module: TimeEntry_Form_Logon.cls
• Demo application: TimeEntry.accdb
• All files: TimeEntry_V1.0.1.zip

You can also obtain the code from Github: VBA.TimeEntry

I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.