By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 3,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

How to limit input length in Access form textbox?

Expert 5K+
P: 8,435
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.
Nov 20 '06 #1
Share this Question
Share on Google+
27 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Specifically no.
But you might want to check out the 'InputMask Property'. It may be able to give the required functionality.
Nov 20 '06 #2

Expert 5K+
P: 8,435
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.
Nov 20 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1.  Dim someStr As String 
  2.  
  3.   someStr = InputBox("Enter some string: ")
  4.   If Len(someStr) > 100 Then
  5.      someStr = Left(someStr,100)
  6.      'Msg to user
  7.   End If
  8.  
Nov 20 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
In code you could

Expand|Select|Wrap|Line Numbers
  1.  Dim someStr As String 
  2.  
  3. someStr = InputBox("Enter some string: ")
  4. If Len(someStr) > 100 Then
  5.      someStr = Left(someStr,100)
  6.      'Msg to user
  7. End If
  8.  
Or if textbox, damn I should read these things better ...

Expand|Select|Wrap|Line Numbers
  1.  Dim someStr As String 
  2.  
  3. someStr = Me.textbox
  4. If Len(someStr) > 100 Then
  5.      someStr = Left(someStr,100)
  6.      Me.textbox = someStr
  7.      'Msg to user
  8. End If
  9.  
Nov 21 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Or if textbox, damn I should read these things better ...

Expand|Select|Wrap|Line Numbers
  1.  Dim someStr As String 
  2.  
  3. someStr = Me.textbox
  4. If Len(someStr) > 100 Then
  5.      someStr = Left(someStr,100)
  6.      Me.textbox = someStr
  7.      'Msg to user
  8. End If
  9.  
I'm now wondering if you could get the realtime text value of the input and validate on KeyPress event. Preformance ???
Nov 21 '06 #6

Expert 5K+
P: 8,435
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 ;)
Nov 21 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 21 '06 #8

Expert 5K+
P: 8,435
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.
Nov 21 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 21 '06 #10

Expert 5K+
P: 8,435
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub tx_Change()
  2.   Static HeldColour As Long
  3.   With tx
  4.     If Len(.Text) > 8 Then
  5.       If HeldColour = 0 Then
  6.         Beep
  7.         HeldColour = .BackColor
  8.         .BackColor = vbRed
  9.       End If
  10.     Else
  11.       If HeldColour <> 0 Then
  12.         .BackColor = HeldColour
  13.         HeldColour = 0
  14.       End If
  15.     End If
  16.   End With
  17. End Sub
  18.  
Nov 21 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
If you wanted even more control:

Expand|Select|Wrap|Line Numbers
  1.  if Len(tx.Text) > 8 then 
  2. Me.someothercontrol.SetFocus
  3. Me.tx = Left(Me.tx, 8)
  4. Msgbox("You are limited to 8 characters ....", vbOKOnly + vbWarning)
  5. Me.tx.SetFocus
  6. Else
  7. >
  8. >
  9. >
  10.  
Nov 21 '06 #12

Expert 5K+
P: 8,435
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. :(
Nov 21 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Never thought about using the control tags in this way. Sounds interesting.

Mary
Nov 21 '06 #14

Expert 5K+
P: 8,435
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.
Nov 21 '06 #15

missinglinq
Expert 2.5K+
P: 3,532
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?
Nov 21 '06 #16

Expert 5K+
P: 8,435
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.
Nov 21 '06 #17

missinglinq
Expert 2.5K+
P: 3,532
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.
Nov 21 '06 #18

ADezii
Expert 5K+
P: 8,597
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub LimitChars(txt As Textbox, lngLimit As Long)
  2. Dim hWnd As Long
  3. Dim lngResult As Long
  4. Dim lngNewMax As Long
  5.  
  6. 'Get Window Handle for the current Window
  7. hWnd = GetFocus()
  8.  
  9. 'Check for existing length of Text and adjust lngLimit
  10. lngNewMax = Len(txt.Text)
  11. If lngNewMax < lngLimit Then
  12.     lngNewMax = lngLimit
  13. End If
  14.  
  15. 'Send the Message to the Text Box to limit itself to lngNewMax _
  16. 'characters
  17. SendMessageLong hWnd, EM_SETLIMITTEXT, lngNewMax, 0
  18. 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
Nov 21 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub LimitChars(txt As Textbox, lngLimit As Long)
  2. Dim hWnd As Long
  3. Dim lngResult As Long
  4. Dim lngNewMax As Long
  5.  
  6. 'Get Window Handle for the current Window
  7. hWnd = GetFocus()
  8.  
  9. 'Check for existing length of Text and adjust lngLimit
  10. lngNewMax = Len(txt.Text)
  11. If lngNewMax < lngLimit Then
  12. lngNewMax = lngLimit
  13. End If
  14.  
  15. 'Send the Message to the Text Box to limit itself to lngNewMax _
  16. 'characters
  17. SendMessageLong hWnd, EM_SETLIMITTEXT, lngNewMax, 0
  18. 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
Nov 21 '06 #20

Expert 5K+
P: 8,435
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).
Nov 21 '06 #21

P: 4
This is an old thread, but if it helps anybody, there is a much easier way to do this. Simply:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ProgramName_Change()
  2.     If Len(Forms.RequestNew.ProgramName.Text) > 10 Then
  3.         Forms.RequestNew.ProgramName.Text = Left(Forms.RequestNew.ProgramName.Text, 10)
  4.         Forms.RequestNew.ProgramName.SelStart = 10
  5.     End If
  6. 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.
May 17 '10 #22

P: 2
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub tPackTX_KeyPress(KeyAscii As Integer)
  2.   If Len(tPackTX.Text) = 24 Then KeyAscii = 8: Exit Sub
  3. End Sub
Nov 7 '13 #23

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. Private Const conMaxXX As Integer = 24
  2.  
  3. Private Sub XX_KeyPress(KeyAscii As Integer)
  4.     If Len(Me.XX.Text) >= conMaxXX Then KeyAscii = 0
  5. End Sub
This isn't the same as a simple .MaxLength property, but it should give a similar effect.
Nov 7 '13 #24

P: 2
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.
Nov 10 '13 #25

NeoPa
Expert Mod 15k+
P: 31,186
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.

Expand|Select|Wrap|Line Numbers
  1. Private Const conMaxXX As Integer = 24
  2.  
  3. Private Sub XX_KeyPress(KeyAscii As Integer)
  4.     If Len(Me.XX.Text) < conMaxXX Then Exit Sub
  5.     If KeyAscii = vbKeyBack _
  6.     Or KeyAscii = vbKeyDelete Then Exit Sub
  7.     KeyAscii = 0
  8. End Sub
Nov 10 '13 #26

Expert 5K+
P: 8,435
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).
Nov 20 '13 #27

zmbd
Expert Mod 5K+
P: 5,287
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.
Nov 25 '13 #28

Post your reply

Sign in to post your reply or Sign up for a free account.