Checking text box input

[Jeff H]

I have a form control called Hours bound to a table field in Short Time format (0:00).

If the user enters an integer, e.g. "1" instead of "1:00", I'd like to catch that, automatically assume they meant one hour, and change the value to "1:00". However, the BeforeUpdate event doesn't catch it before the invalid value error appears.

Is there a way to do this?

- Jeff

[HansV]

Access checks whether the entry conforms to the Format property before firing the Before Update event.

I would leave it as it is and instruct users to enter a valid time. But if you really want to do this, you could place an unbound text box txtEntry on the form, and leave its Format property blank.

Create the following event procedures for txtEntry:

Code: Select all

Private Sub txtEntry_AfterUpdate()
    If Not IsDate(Me.txtEntry) Then
        Me.Hours = Val(Me.txtEntry) / 24
    Else
        Me.Hours = Me.txtEntry
    End If
End Sub

Private Sub txtEntry_BeforeUpdate(Cancel As Integer)
    If Not IsDate(Me.txtEntry) Then
        If Not IsNumeric(Me.txtEntry) Or Val(Me.txtEntry) <> Int(Val(Me.txtEntry)) Then
            MsgBox "Can't make sense of this!"
            Cancel = True
        End If
    End If
End Sub

Set the Enabled property of Hours to No and its Locked property to Yes, to prevent users from entering data directly in this control.

[Jeff H]

Got it. Thanks for the solution and the advice, Hans.

BTW, I'm very much enjoying the Michael Alexander book (Access 2019 Bible) you recommended. He's clarified a lot of stuff I've been using for 25 years without understanding!

[HansV]

Good to hear that!