SQL Server 2000 behind MS Access 2000

[Wayne]

I am using SQL Server 2000 behind a MS Access 2000
application.

I am trying to limit the number of characters a user may
enter in a text box on a form.

The field in the SQL table is nvarchar with a length of
4000.

Here is the code I use to test the number of characters in
the text box. I get the error on the line .Text = Left
(.Text, maxi) I am trying to remove the string that is
too long and replace it with a string that will match the
maximum length allowed. I pass in the textbox control and
its maximum allowed length.


Public Sub CheckMax(ByVal maxi As Integer, myctl As
Control)
With myctl
If Len(.Text) > maxi Then
DoCmd.Beep
MsgBox "You have exceeded " & maxi & "
characters", vbInformation, ""
.Text = Left(.Text, maxi)
'Set the cursor at the end of the text...
myctl.SelStart = maxi
End If
End With
Set myctl = Nothing


This code worked well untill I changed the length of the
field in SQL Server from 255 to 4000.

Thanks,
Wayne

 

[Rod Scoullar]

Wayne,

You could put code in the OnChange event.

Something like

Private Sub Textbox_Change()
If Len(Textbox.Text) > 4000 Then
MsgBox "You have exceeded the maximum length for this entry."
Textbox.Text = Left$(Textbox.Text,4000)
End If
End Sub

This will let the user know when they reach the limit rather than truncating
their entry when they finish. It does create more overhead because it will
be called each time they type a character rather than once at the end, but
users won't notice it unless they are phenomenal typists.

Regarding the error you are getting, what does Access think the field size
is for the field bound to the control? Forgive me for asking but did you
relink the table after changing the field size. I've forgotten to do it.

Rod Scoullar.

 

[Guest]

Hi Rod,

Thanks.

Refer to http://www.lebans.com/limitcharsmemo.htm for the
solutiuon by Stephen Lebans that I used. I tried several
approaches including the one you mentioned.

I want the characters to just stop appearing when the
maximum character limit is reached.

This is what Stephen's solution does.

Thanks again,
Wayne