Subscript out of range error

[lwilly]

I am using this code to find line breaks in a string of text retrieved from our ERP system and replacing it with a carriage return on my report.

Dim strNotes As String
Dim strFormatNotes As String
Dim intEnterPos As Integer
Dim i As Integer

strNotes = Me.COLIN_XTXT

ReDim strTemp(0) As String

intEnterPos = InStr(strNotes, Chr(13))

Do Until intEnterPos = 0
ReDim Preserve strTemp(UBound(strTemp) + 1) strTemp(UBound(strTemp)) = Trim$(Left$(strNotes, intEnterPos - 1))
strNotes = LTrim$(Right$(strNotes, Len(strNotes) - intEnterPos))
intEnterPos = InStr(strNotes, Chr(13))
Loop

For i = 1 To (UBound(strTemp))
If strTemp(i) = Chr(13) Then
Exit For
End If
strFormatNotes = strFormatNotes & " " & strTemp(i)& vbCrLf
Next i

Me.FormatedNotes = strFormatNotes

I am getting a subscript out of range error and I lose text after my last line break. Will someone please tell me what I am doing wrong!

 

[KenReay]

Hi

Not a direct answer to your question, but if you are using Access2000 or later, why not just use Replace() function ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mikevh]

You would lose text after your last line break because
your Do loop exits when it can no longer find a carriage
return in the string. If your string isn't terminated by
a final carriage return, you will lose text at the end.

I'm not sure about the array subscript out of range, but
I'm curious as to why you're starting your For loop
index at 1. Isn't the lower bound of strTemp 0?

KenReay's suggestion above to use the Replace function
if you can is a good one. This got me thinking about
you'd do it if you didn't have Replace builtin, though.
Here's my attempt.

Function myReplace(ByVal StrIn As String, ByVal strReplaceThis As String, _
  ByVal strReplaceWith As String) As String

  Dim intI As Long
  
  For intI = 1 To Len(StrIn)
    If Mid(StrIn, intI, 1) = strReplaceThis Then
      StrIn = Left(StrIn, intI - 1) & strReplaceWith & Right(StrIn, Len(StrIn) - intI)
    End If
  Next intI
  
  myReplace = StrIn
  
End Function

Here's a call from the Immediate Window that replaces
carriage returns (chr(13)) with vbcrlf's (chr(13) & chr(10)):

?myReplace("abc" & vbcr & "def" & vbcr & "ghi", vbcr, vbcrlf)
abc
def
ghi

 

[lwilly]

Thank you both for the quick response. Took you advice and replaced my code with the Replace() function and of course it works like a charm.
Thanks again