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.
To have a textbox that just works for entering time takes several steps:
That may seem like a lot, but the outcome is a highly optimised control demonstrating the true power of an input mask.
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.
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.
The zip file also contains the Date Entry form from the next and related article:
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.