Breaking up line of text > 55 chars at the last space

[Silvester]

I have a textbox linked to a table textfield of size 55. How can I trim a
line of text that a user enters that is greater than 55 chars and truncate
it to the last space char < 55.

Thanks for any help

 

[Ken]

-----Original Message-----
I have a textbox linked to a table textfield of size 55. How can I trim a
line of text that a user enters that is greater than 55 chars and truncate
it to the last space char < 55.

Thanks for any help


.

This is crude but it works test it and modify for your own
form

Create a form and place two text boxes called txtBoxInput
and txtBoxOutput

Add a module and use the following code

Option Compare Database
Option Explicit

Sub getlast()
Dim txtLenString As String, txtLastChar As String
Dim gotit As Boolean
Dim nlen As Long

nlen = 55
If Len([txtboxInput]) > 55 Then ' if the text is less than
55 characters use all text
txtLenString = Mid([txtboxInput], 1, nlen)
gotit = False
Do While gotit = False
nlen = nlen - 1
txtLenString = Mid([txtboxInput], 1, nlen)
txtLastChar = Right([txtLenString], 1)
If txtLastChar = " " Then
gotit = True
txtboxOutput = Trim(txtLenString)
End If
Loop
Else
txtboxOutput = txtboxInput
End If
End Sub

Private Sub txtBoxInput_AfterUpdate()
getlast
End Sub

When text is entered into the txtBoxInput box the
txtBoxOutput displays the text truncated to the last space
character < 55

 

[fredg]

I have a textbox linked to a table textfield of size 55. How can I trim a
line of text that a user enters that is greater than 55 chars and truncate
it to the last space char < 55.

Thanks for any help

Please always include your Access Version when posting, as this could
be done a bit differently if you have Access 2000 or newer.

The below code will work in all versions.

In the Form Control's AfterUpdate event:

If Len([ControlName]) <=55 Or IsNull([ControlName]) Then Exit Sub

Dim intX As Integer
Dim strNew As String
strNew = Left([ControlName], 55)
intX = InStr(strNew, " ")
If intX = 0 Then
[ControlName] = strNew
Exit Sub
End If

intX = Asc(Right([strNew], 1))
Do While intX <> 32
strNew = Left([strNew], Len([strNew]) - 1)
intX = Asc(Right([strNew], 1))
Loop

[ControlName] = strNew

 

[Dirk Goldgar]

I have a textbox linked to a table textfield of size 55. How can I
trim a line of text that a user enters that is greater than 55 chars
and truncate it to the last space char < 55.

I'm not convinced this is feasible. If the field in the table has its
size set to 55 characters, the user will not be able to enter more than
55 characters in the text box, no matter what. I suppose you could use
the text box's KeyPress event to detect that the user has continued to
type in it, even after the length of its Text property is 55, but it
seems a lot like guessing to me. What if the user types 55 characters,
then accidentally types an extra letter, sees that it doesn't appear,
and is then satisfied with the all 55 characters that are currently
displayed?

I suppose you might use an unbound text box, let the user type as many
characters as he wants, and then truncate it before assigning it to the
field itself; but then you're letting the user waste time typing
characters that won't be accepted, with no clue that this is a waste of
time. That's not good user-interface design, I think.

 

[Dirk Goldgar]

If Len([ControlName]) <=55 Or IsNull([ControlName]) Then Exit Sub

Ah, but Fred, if the control is bound to a text field of size 55, the
length of the control's value will *never* exceed 55.