how to format text in a memo field

[Alan B.]

I want to be able to type into a memo field, and then have the lines
automatically ended at a 70 character width.

There is a web application that does this here:

http://www.fwointl.com/FWOFormatter.html

Does anyone know where I can find some VBA that I can use to accomplish this
within an A 2000 database?

Regards,
Alan

 

[Ken Snell \(MVP\)]

You could use the form's textbox's AfterUpdate event to do this. One might
consider using a function that would do this, but you'd need to decide how
to handle lines where the 70-character limit would split a word. You need to
consider all the options for how you'd manually split the text into separate
lines and state them clearly before anyone could suggest such a function
that would work in all situations that you might have.

 

[Alan B.]

Hi Ken,

I would like the lines to max out at 70 characters. If a word would be split
by that 70 character limit, then a new line should be forced at the end of
the previous word.

I really don't want to use the after update event for several reasons.
1. I want the ability to max the lines at 70 to be optional.
2. That would be a huge pain when I go back to edit the existing text in the
field. The purpose of this field is that it used for form emails that are
sent through automation to Outlook.

Thanks for any additional help or functions that you can provide or point me
to.

Regards,
Alan

 

[Ken Snell \(MVP\)]

OK, you don't want to use the AfterUpdate event of the memo text box. So,
when would you want this "formatting" fix to be run? Also, what if you had
an entry that has more than 70 characters without a space to indicate a
break between words (e.g., someone forgot to put in a space)?

 

[John W. Vinson]

Hi Ken,

I would like the lines to max out at 70 characters. If a word would be split
by that 70 character limit, then a new line should be forced at the end of
the previous word.

I really don't want to use the after update event for several reasons.
1. I want the ability to max the lines at 70 to be optional.
2. That would be a huge pain when I go back to edit the existing text in the
field. The purpose of this field is that it used for form emails that are
sent through automation to Outlook.

Thanks for any additional help or functions that you can provide or point me
to.

You might need to use the textbox's Change event (which fires at every
keystroke). You could use string operations such as InStr() to find the most
recent CR-LF pair (or the start of the textbox if there is none), and insert a
CR-LF at the appropriate place. You may be able to use InStrRev() to find the
previous blank prior to the 70th character and replace it with a CR-LF.

 

[Alan B.]

Hi Ken,

Since the fomatting will be optional, I can fire it from a command button on
the form.

There will never be a line that has more than 70 characters without a space.

Regards,
Alan

 

[Ken Snell \(MVP\)]

Sorry for delay in replying... work has me a bit busy at the moment. I will
post a reply as soon as possible.

 

[Ken Snell \(MVP\)]

Create the following function in a regular module:

Public Function BreakTextAtX(varOriginal As Variant, _
Optional strBreakCharacter As String = " ", _
Optional lngMaxLength As Long = 72) As Variant
' Code written by Ken Snell -- 15 November 2008
' strOriginal is the original text string
' strBreakCharacter is the character that is used to break the
' text into separate lines (e.g., a blank space)
' lngMaxLength is the maximum length for each separate line
Dim strNewString As String, strWorking As String, strPart As String
Dim strOriginalNoCrLf As String
Dim lngPosition As Long, lngHold As Long, lngLength As Long
Dim lngWorkLength As Long
lngLength = Len(varOriginal & "")
If lngLength > 0 Then
strOriginalNoCrLf = Replace(Replace(CStr(varOriginal), vbCr, ""), vbLf,
"")
strNewString = ""
lngPosition = 1
Do While lngPosition <= lngLength
strWorking = Mid(strOriginalNoCrLf, lngPosition, lngMaxLength)
lngWorkLength = Len(strWorking)
If lngWorkLength < lngMaxLength Then
If Len(strNewString) > 0 And Len(strWorking) > 0 Then _
strNewString = strNewString & vbCrLf
strNewString = strNewString & strWorking
Exit Do
Else
lngHold = InStrRev(strWorking, strBreakCharacter)
If lngHold = 0 Then lngHold = lngWorkLength
If Len(strNewString) > 0 Then _
strNewString = strNewString & vbCrLf
strNewString = strNewString & Left(strWorking, lngHold)
lngPosition = lngPosition + lngHold
End If
Loop
BreakTextAtX = strNewString
Else
If IsNull(varOriginal) = True Then
BreakTextAtX = varOriginal
Else
BreakTextAtX = ""
End If
End If
End Function



Then use this code for the form's CommandButton's Click event:

Private Sub CommandButtonName_Click()
Dim strTextWithoutCrLf As String
Const strBreakCharacter As String = " "
Const lngMaxLength As Long = 70
Me.MemoTextboxName.Value = _
BreakTextAtX(Me.MemoTextboxName.Value, _
strBreakCharacter, lngMaxLength)
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ken Snell \(MVP\)]

Newsreader wrapped a line in the function -- here is a better version:

Public Function BreakTextAtX(varOriginal As Variant, _
Optional strBreakCharacter As String = " ", _
Optional lngMaxLength As Long = 72) As Variant
' Code written by Ken Snell -- 15 November 2008
' strOriginal is the original text string
' strBreakCharacter is the character that is used to break the
' text into separate lines (e.g., a blank space)
' lngMaxLength is the maximum length for each separate line
Dim strNewString As String, strWorking As String, strPart As String
Dim strOriginalNoCrLf As String
Dim lngPosition As Long, lngHold As Long, lngLength As Long
Dim lngWorkLength As Long
lngLength = Len(varOriginal & "")
If lngLength > 0 Then
strOriginalNoCrLf = Replace(Replace(CStr(varOriginal), vbCr, ""), _
vbLf, "")
strNewString = ""
lngPosition = 1
Do While lngPosition <= lngLength
strWorking = Mid(strOriginalNoCrLf, lngPosition, lngMaxLength)
lngWorkLength = Len(strWorking)
If lngWorkLength < lngMaxLength Then
If Len(strNewString) > 0 And Len(strWorking) > 0 Then _
strNewString = strNewString & vbCrLf
strNewString = strNewString & strWorking
Exit Do
Else
lngHold = InStrRev(strWorking, strBreakCharacter)
If lngHold = 0 Then lngHold = lngWorkLength
If Len(strNewString) > 0 Then _
strNewString = strNewString & vbCrLf
strNewString = strNewString & Left(strWorking, lngHold)
lngPosition = lngPosition + lngHold
End If
Loop
BreakTextAtX = strNewString
Else
If IsNull(varOriginal) = True Then
BreakTextAtX = varOriginal
Else
BreakTextAtX = ""
End If
End If
End Function

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/