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.
Comments (2)
Author
Commented:/gustav
Commented: