The Linux Page

MS-Access not limiting unbound text fields

When creating an unbound text in MS-Access it is not constrained in length. The text limit you get when the field is bound to the database table is really neat, but there is not direct way to access that code (it's probably something hard coded in the MS-Access system.)

This means you have to re-implement the behavior.

I found this page Unbound text box: limiting entry length which has a very good solution. Just in case the page disappears, there is what I use (a simplified version that doesn't manage errors because I do not like that — errors are not supposed to happen, when they do, in most cases, your code is wrong.)

The beeping noise is obviously optional, but that's a good idea to let the user know that the computer is preventing the extra input.


Sub LimitKeyPress(ctl As Control, iMaxLen As Integer, KeyAscii as Integer)
    If Len(ctl.Text) - ctl.SelLength >= iMaxLen Then
        if KeyAscii <> vbKeyBack Then
            KeyAscii = 0
            Beep
        End If
    End If
End Sub

Sub LimitChange(ctl As Control, iMaxLen As Integer)
    If Len(ctl.Text) > iMaxLen Then
        ctrl.Text = Left(ctl.Text, iMaxLen)
        ctl.SelStart = iMaxLen
    End If
End Sub

The KeyAscii = 0 works, that means all variables are passed as reference (I learn something every day!)

The OnKeyPress event should call the LimitKeyPress as in:

     LimitKeyPress text_box, 40, KeyAscii

where text_box is the name of your text box (duh!) and 40 is the maximum number of characters in that box. The KeyAscii is the parameter of the KeyPress event and again it is received as a reference.

The limit on change is necessary in the OnChange event, this happens whenever someone does a Ctrl-V or similar action. In that case, the KeyPress is not called and the length of the field could go berserk.

    LimitChange text_box, 40

The problem with the LimitChange is that it simply truncate the field length. It cannot really know what needs to be removed unless you want to handle the Ctrl-V and menu Paste actions.