After field Size validation Can one change the text box?

[darbid]

I am implementing this to make sure that people do not enter to many characters

Private Sub mytextbox_BeforeUpdate(Cancel As Integer)
If Len(Me.mytextbox) > 1000 Then
   MsgBox "Too big"
   Cancel = True
End If
End Sub

As my field is big I want to show the user how many characters they can enter so I thought this would work but it does not.

Private Sub txt_comments_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.txt_comments)) > 1000 Then
    Cancel = True
    MsgBox "too big"
    Me.txt_comments.Value = Left(Me.txt_comments, 1000)
End If
End Sub

Could someone please assist.

 

[gemma-the-husky]

if your textbox is BOUND, you will find you cannot enter too many characters anyway

standard textbox is normally 50, but it can go to 255 (max)

if you need more than 255, then memo will let you go to 65000

------------
if its an UNBOUND form, then you may need to check the size, but you cant edit the entry in the beforeupdate event , i think - just cancel the edit, and get the user to reenter

 

[darbid]

Thanks GTH.

I am using a SQL Server 2000 backend where the field is a nvarchar - which access shows as a memo.

In anycase automatically deleting something of the users is probably not the best.

So I have instead put in the following. int_fld_size is the size that the field is allowed to be. This appears to work ok.

ctl_current.SelStart = 0
    ctl_current.SelLength = int_fld_size

 

[boblarson]

If you don't want more than a certain number of characters, I would probably just use the text box's On Change event to keep track in another text box beside it to show how many characters they have left. And then use the Key Up event to cancel the keystrokes after 1000 have been entered. (Remember: you need to enable the KEY PREVIEW property of the form if you want to use the Key Up event)

In the On Change you can use this to keep track:

Me.YourOtherTextBox = 1000 - Len(Me.YourTextBoxName.Text)

and then use the Key Up event to validate and cancel the key input if the length has been reached.

If Len(Me.YourTextBoxName.Text) = 1000 Then
   KeyCode=0
End If

 

[missinglinq]

Bob has the right idea, but if you reach the 1000 character limit and set KeyCode = 0 thereafter, the user can't go back and edit the data, which you sometimes have to do, because no keys, including Backspace, Delete , Arrow keys, etc. will work.


This takes this possibility into account:

Private Sub YourTextBox_Change()
 CharactersLeftTextbox = "You Have " & 1000 - Len(Me.YourTextBox.Text) & " Characters Left"
End Sub

Private Sub YourTextBox_KeyDown(KeyCode As Integer, Shift As Integer)
 If Len(Me.YourTextBox.Text) > 999 Then
  Select Case KeyCode
   Case vbKeySpace, vbKeyDelete, vbKeyBack, vbKeyReturn, vbKeyRight, vbKeyLeft
    KeyCode = KeyCode
   Case Else
    KeyCode = 0
  End Select
 End If
End Sub

 

[darbid]

Lov'in it guys!

Thanks Bob. And Missingling you saved me at least 10 to 20 hairs that I would of pulled out after working out that if the user got to the limit that everything was disabled.

So for people finding this thread - a great solution is Missinglings, but read Bobs as well for setting up the form.

Thanks heaps guys.

 

[boblarson]

Lov'in it guys!

Thanks Bob. And Missingling you saved me at least 10 to 20 hairs that I would of pulled out after working out that if the user got to the limit that everything was disabled.

So for people finding this thread - a great solution is Missinglings, but read Bobs as well for setting up the form.

Thanks heaps guys.

Linq always seems to correct my errors like that, so I am thankful he's here to keep things moving. I sometimes forget little details like that and he's there (as well as many, many others too) to get the nuances taken care of.