LimitTextLength: Enforce Max Text Length for Unbound Text Boxes and Combo Boxes

I prefer to use unbound forms to add new records in my Access applications.

One important feature that you lose with that approach is enforcement of maximum string lengths.  When a text box or combo box is bound to a text field in the database, Microsoft Access prevents the user from entering more than the maximum allowed characters.  This is one of just many of the built-in features that makes Access such a powerful rapid application development (RAD) tool for data-rich applications.

But all is not lost.  In fact, it is quite trivial to enforce a maximum string length for unbound text boxes and combo boxes.

The Code

'---------------------------------------------------------------------------------------
' Procedure : LimitTextLength
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/limittextlength/
' Date      : 10/21/2008 - 7/24/2012
' Purpose   : Used to limit the amount of text a user can enter in a text box or combo box.
' Notes     - This is not needed for bound controls, as access handles it internally.
'           - Set MaxLength to 0 to allow unlimited length strings
' Usage     : TextBox/ComboBox OnChange: =LimitTextLength(ActiveControl, #)
'---------------------------------------------------------------------------------------
'^^
Function LimitTextLength(Ctl As Control, MaxLength As Integer)
Dim SavePos As Integer
    
    If MaxLength = 0 Then Exit Function
    If Len(Ctl.Text) > MaxLength Then
        If Ctl.SelStart = MaxLength + 1 Then
            Ctl.Text = Left(Ctl.Text, MaxLength)
            Ctl.SetFocus
            Ctl.SelStart = MaxLength
        Else
            SavePos = Ctl.SelStart
            Ctl.Text = Left(Ctl.Text, Ctl.SelStart - 1) & Mid(Ctl.Text, Ctl.SelStart + 1)
            Ctl.SetFocus
            Ctl.SelStart = SavePos - 1
        End If
    End If

End Function 

Usage

As it says in the function header comments, the easiest way to call this function is directly from the control's property sheet in the On Change property:

Note that this function will handle both direct typing into the control and copying and pasting text from the clipboard.  

It will not handle programmatic changes to the text box's or combo box's value, as setting a control's value via VBA does not trigger the On Change event.  You also would not want to call this function outside of the control's On Change event because you will get an error when trying to access the Ctl.Text property if the control does not have the focus.

Referenced articles

6 Reasons Why I No Longer Use Bound Forms to Add Records in Microsoft Access

Just because something can serve two purposes, doesn’t mean it should. (Reason number four can be a real game-changer.)

No Longer SetMike Wolfe

Base Image by Acquaforte from Pixabay (modified by Mike Wolfe)