I'm having a difficult time doing military time or short time in the form view. I caught an error where the user put in 15:83 which shouldn't be allowed because the minutes are over 59, as well hours being over 23. However, I'm not sure how to create a form validation for something like that and I can't seem to find any documentation for it online surrounding this type of formatting.
CodePudding user response:
I can't seem to find any documentation for it online surrounding this type of formatting.
That's because it is difficult and requires quite a lot of code, too much to post here. But, for example, to correct invalid input:
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
Full code, documentation, and a demo application is posted with my project VBA.TimeEntry
at GitHub:
CodePudding user response:
in the validation rule property of the text box control on the form enter this expression
=Right([Text6],2)<60 Or Left([Text6],2)<24
i have assumed name of control is Text6