How to limit input length in Access form textbox?

[Killer42]

Hi all.

Does a textbox on an Access form have an equivalent of the VB6 .MaxLength property? I need to limit the length of an input string. It's not bound to anything, just used in code, but I'd prefer to limit it in the control rather than validating afterward.

[NeoPa]

Specifically no.
But you might want to check out the 'InputMask Property'. It may be able to give the required functionality.

[Killer42]

Specifically no.
But you might want to check out the 'InputMask Property'. It may be able to give the required functionality.

Nuts! I was hoping to avoid that.

Thanks, anyway.

[MMcCarthy]

Hi all.

Does a textbox on an Access form have an equivalent of the VB6 .MaxLength property? I need to limit the length of an input string. It's not bound to anything, just used in code, but I'd prefer to limit it in the control rather than validating afterward.

In code you could

 Dim someStr As String 
 
  someStr = InputBox("Enter some string: ")
  If Len(someStr) > 100 Then
     someStr = Left(someStr,100)
     'Msg to user
  End If
 

[MMcCarthy]

In code you could

 Dim someStr As String 
 
someStr = InputBox("Enter some string: ")
If Len(someStr) > 100 Then
     someStr = Left(someStr,100)
     'Msg to user
End If
 

Or if textbox, damn I should read these things better ...

 Dim someStr As String 
 
someStr = Me.textbox
If Len(someStr) > 100 Then
     someStr = Left(someStr,100)
     Me.textbox = someStr
     'Msg to user
End If
 

[MMcCarthy]

Or if textbox, damn I should read these things better ...

 Dim someStr As String 
 
someStr = Me.textbox
If Len(someStr) > 100 Then
     someStr = Left(someStr,100)
     Me.textbox = someStr
     'Msg to user
End If
 

I'm now wondering if you could get the realtime text value of the input and validate on KeyPress event. Preformance ???

[Killer42]

I'm now wondering if you could get the realtime text value of the input and validate on KeyPress event. Preformance ???

It's ok, I can handle the validation alright. I just didn't want to have to, because .MaxLength is ever so much simpler.

Maybe I need to use the VB6 library ;)

[MMcCarthy]

It's ok, I can handle the validation alright. I just didn't want to have to, because .MaxLength is ever so much simpler.

Maybe I need to use the VB6 library ;)

Worth a try as it should be on the list. Never tried it for something like this though.

Mary

[Killer42]

Worth a try as it should be on the list. Never tried it for something like this though.

Hm... nah!

I've just spent a few minutes browsing the References list and don't really like what I saw. Think I'll just bite the bullet and put in some code to do validation (or real-time feedback) on the form.

[MMcCarthy]

Hm... nah!

I've just spent a few minutes browsing the References list and don't really like what I saw. Think I'll just bite the bullet and put in some code to do validation (or real-time feedback) on the form.

Yeah!

It is a bit of a minefield.

When you get some time :)

Have a look on msdn regarding 'Automation' from Access.

Mary

[Killer42]

Yeah!
It is a bit of a minefield.
When you get some time :)
Have a look on msdn regarding 'Automation' from Access.

Will do.

In the meantime, I ended up going with something very simple. I just turn the field background red as long as the value is not within the required length. Although it doesn't account for the possibility of a black (zero) background, here's the routine I used.

Private Sub tx_Change()
  Static HeldColour As Long
  With tx
    If Len(.Text) > 8 Then
      If HeldColour = 0 Then
        Beep
        HeldColour = .BackColor
        .BackColor = vbRed
      End If
    Else
      If HeldColour <> 0 Then
        .BackColor = HeldColour
        HeldColour = 0
      End If
    End If
  End With
End Sub
 

[MMcCarthy]

If you wanted even more control:

 if Len(tx.Text) > 8 then 
Me.someothercontrol.SetFocus
Me.tx = Left(Me.tx, 8)
Msgbox("You are limited to 8 characters ....", vbOKOnly + vbWarning)
Me.tx.SetFocus
Else
>
>
>
 

[Killer42]

If you wanted even more control: ...

Yeah, I thought about chopping it off, but decided against it. The users know the limits, if they choose to ignore the warning then so be it.

One technique I've used in similar cases at times is to have a timer on the form. To highlight a field I do one of two things:

• set a value in the .Tag property (VB6, not sure about Access) and the timer watches for these flags and flashes the controls on and off.
• Highlight the control (typically by setting background colour), and also flag it in .Tag or whatever. The timer gradually fades the colour back to normal. This one looks quite good, actually. You get a beep, the field turns (typically) bright red, then over a few seconds it fades back to normal, but it doesn't interfere with your typing or anything.

(Note, in cases such as these the warning message might be displayed in a particular place on the form, rather than as a message box).

Damnit, I've got to stop spending so much time here - I keep getting carried away. :(

[MMcCarthy]

Never thought about using the control tags in this way. Sounds interesting.

Mary

[Killer42]

Never thought about using the control tags in this way. Sounds interesting.

I find them quite useful for this kind of thing.

One caveat, though.

I recommend that one always assume there may be other info in there. I try to

• Add my flag onto the end and make it distinctive, with something like "/FLASHFIELD", and
• Remove it without disturbing any other contents.

That way, you can make use of multiple routines that use the .Tag property, without any cross-interference.

[missinglinq]

A simple hack would be to add a "dummy" field to your table, set its length to 6 in the Table Design View, and bind the text box to it. What is it you don't like about Input Masks?

[Killer42]

A simple hack would be to add a "dummy" field to your table, set its length to 6 in the Table Design View, and bind the text box to it. What is it you don't like about Input Masks?

Oh nothing much, I suppose. I think it's just the nuisance of losing the .MaxLength. After all, I don't have the slightest interest in what goes in there, just that it isn't too long. So, it would be fair to say I'm merely being stubborn.

As for the dummy field, it's an interesting idea. Would it limit input, or just spit the dummy if you enter too much?

Might be more trouble than it's worth though - nothing on the form is currently bound to anything. I'm just using the form to get input a la VB6, then building and executing a query, and closing the form.

[missinglinq]

Setting the length of a text field in Design View for the table limits the input to that many characters. If the user keeps typing the cursor simply doesn't move.

[ADezii]

Hi all.

Does a textbox on an Access form have an equivalent of the VB6 .MaxLength property? I need to limit the length of an input string. It's not bound to anything, just used in code, but I'd prefer to limit it in the control rather than validating afterward.

There is a little known and undocumented method for limiting the number of
Input characters in an Access Text Box using the API, here it goes:

Private Declare Function SendMessageLong Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lngValue As Long) As Long

Private Declare Function GetFocus Lib "user32" () As Long

Private Const EM_SETLIMITTEXT As Long = &HC5

Private Sub LimitChars(txt As Textbox, lngLimit As Long)
Dim hWnd As Long
Dim lngResult As Long
Dim lngNewMax As Long
 
'Get Window Handle for the current Window
hWnd = GetFocus()
 
'Check for existing length of Text and adjust lngLimit
lngNewMax = Len(txt.Text)
If lngNewMax < lngLimit Then
    lngNewMax = lngLimit
End If
 
'Send the Message to the Text Box to limit itself to lngNewMax _
'characters
SendMessageLong hWnd, EM_SETLIMITTEXT, lngNewMax, 0
End Sub

NOTE: As always, when using the API, the syntax must be exact.
Hook the code in the Change() Event of the Text Box

[MMcCarthy]

Nice one, that's going in my keeper box.

Thanks ADezii

There is a little known and undocumented method for limiting the number of
Input characters in an Access Text Box using the API, here it goes:

Private Declare Function SendMessageLong Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lngValue As Long) As Long

Private Declare Function GetFocus Lib "user32" () As Long

Private Const EM_SETLIMITTEXT As Long = &HC5

Private Sub LimitChars(txt As Textbox, lngLimit As Long)
Dim hWnd As Long
Dim lngResult As Long
Dim lngNewMax As Long
 
'Get Window Handle for the current Window
hWnd = GetFocus()
 
'Check for existing length of Text and adjust lngLimit
lngNewMax = Len(txt.Text)
If lngNewMax < lngLimit Then
lngNewMax = lngLimit
End If
 
'Send the Message to the Text Box to limit itself to lngNewMax _
'characters
SendMessageLong hWnd, EM_SETLIMITTEXT, lngNewMax, 0
End Sub

NOTE: As always, when using the API, the syntax must be exact.
Hook the code in the Change() Event of the Text Box

[Killer42]

Nice one, that's going in my keeper box.
Thanks ADezii

Ditto. In fact I vaguely recall seeing this years ago when discussing VB, but of course with the MaxLength property it wasn't needed.

Um... one question, though. You say to place this in the Change event? That doesn't sound right. Wouldn't the SendMessage be called once only, at form load or something? (Alright, two questions then). I thought the purpose of it was simply to set the maximum length for future editing, not to check a change which was just made (or attempted).

[jrworsham]

This is an old thread, but if it helps anybody, there is a much easier way to do this. Simply:

Private Sub ProgramName_Change()
    If Len(Forms.RequestNew.ProgramName.Text) > 10 Then
        Forms.RequestNew.ProgramName.Text = Left(Forms.RequestNew.ProgramName.Text, 10)
        Forms.RequestNew.ProgramName.SelStart = 10
    End If
End Sub

All you have to do is chop the Len of the text when it is over the maxlength and reset the cursor location to the end. Try it.

[yondok]

You may write a simple control into "keypress" event like below example where 24 is the max length. If the user tries to enter more chrs it sends a backspace

Private Sub tPackTX_KeyPress(KeyAscii As Integer)
  If Len(tPackTX.Text) = 24 Then KeyAscii = 8: Exit Sub
End Sub

[NeoPa]

Using the KeyPress event is a slightly different idea (in as much as using the Change event has already been discussed - which is fundamentally very similar), but your usage of it is not quite correct. To avoid handling the new character you should set KeyAscii to 0. Setting it to 8 (or BS) would ensure you lost the last (24th in this case) character and be left with a 23 char string.

Private Const conMaxXX As Integer = 24
 
Private Sub XX_KeyPress(KeyAscii As Integer)
    If Len(Me.XX.Text) >= conMaxXX Then KeyAscii = 0
End Sub

This isn't the same as a simple .MaxLength property, but it should give a similar effect.

[yondok]

Neo Pa, in your solution it is not possible to use backspace when you reach 24 chars, as you set keyascii=0.
You may use my solution to keep 24 chars by checking len(...)=25.

[NeoPa]

No. The character isn't added until the KeyPress event procedure has been completed as it is used to determine which charater, if any, should be added. Therefore, it doesn't make sense to try to use a backspace - as that would be to clear a character that has never been added.

Did you even try testing this before arguing the point. Especially as the problem has already been pointed out to you.

However, you do make a good point that the code I posted would not handle deleting characters when the maximum length had been reached. The solution is certainly not to play around with adding a backspace character, but could be handled by allowing the Del or Backspace characters through regardless of the limit.

Private Const conMaxXX As Integer = 24
 
Private Sub XX_KeyPress(KeyAscii As Integer)
    If Len(Me.XX.Text) < conMaxXX Then Exit Sub
    If KeyAscii = vbKeyBack _
    Or KeyAscii = vbKeyDelete Then Exit Sub
    KeyAscii = 0
End Sub

[Killer42]

Phew! Talk about a long conversation... :-)

Seems to me, the simplest thing would be to just chop off the last character, something like the proposal in message #22.

I really don't like the idea of playing around with keypress, as it interferes too much with normal operation of the field. Consider, for example, what would happen if the field is full and I select a few characters then type a letter to replace them. Or I use the mouse to paste in some text which makes the value too long.

Another alternative would be to keep track of the current value in the field. Each time it changes (Change event, I suppose) you check the new value. If it's too long, simply return the previous value. (Note though, if changing a field within that field's change event, one must be wary of creating an infinite loop).

[zmbd]

I've done variations on theme for keypress, onchange, etc...
The simplist and the one that gives my users the least heartburn is to just tell them in the lable text:

LableTextForField (max10): [(un)BoundControlHere]

then to either just nail it with inputbox as in
Post #4 or some variation upon the theme such as Post#22
usually in the afterupdate event.

I do have a couple of fancy textboxes where I have the onchange event keep track of the number of charaters and updates the the builtin status bar with a # of ## thing. However, that's for the fields that have 100 or so character limit.