Strange behaviour with replace()

[fearoffours]

Well, I'm sure it's not that strange, and merely just that in all tghe cutting and pasting of other peoples example code I have somewhere along the lines managed to make a small error in my coding!

I have a memo field "Notes" on a form. I want every new line in this field to be preceded by a by a bullet point. So far I have the following code in After Update:

Private Sub Notes_AfterUpdate()
    Dim sNotes As String
    Dim sEolPos As String
    Dim X As Integer
    Dim NewLines As Integer
    
    sNotes = Notes.Value

    NewLines = StrCount(sNotes, vbCrLf)

    
    For X = 1 To NewLines
        sEolPos = InStr(sNotes, vbCrLf)
        If Mid(sNotes, sEolPos + 2, 1) <> "•" Then
            sNotes = Replace(sNotes, vbCrLf, vbCrLf & "• ", sEolPos)
        End If
    Next

    If Left(sNotes, 1) <> "•" Then
        sNotes = "• " & sNotes
    End If


    Notes.Value = sNotes
    
End Sub

It's nearly doing what i want; but it's replacing the entire first line with a bullet point. It does seem to be the For Next loop thats the problem, as removing this inserts a bullet at the start of the first line as expected.

Anyone help? (And thanks to all the help I've already found through searching the forums over the last few days!)

 

[fearoffours]

To clarify the expected and actual behaviour:
If the user inputs:

Line 1
Second Line
This is the Third Line

I would like the output to be:

• Line 1
• Second Line
• This is the Third Line

But the output is actually:

•
• Second Line
• This is the Third Line

 

[DCrake]

Simple Software Solutions

Never come across StrCount Access 2003 does not know what it is, what version of acess are you using?

 

[fearoffours]

Sorry found it on another page:

Function StrCount(TheStr As String, TheItem As Variant) As Integer
'------------------------------------------------------------------
' PURPOSE: Counts the numbers of times an item occurs
' in a string.
' ARGUMENTS: TheStr: The string to be searched.
' TheItem: The item to search for.
' RETURNS: The number of occurences as an integer.
'
' NOTES: To test: Type '? StrCount("The quick brown fox jumped over
' the lazy dog", "the") in the debug window.
' The function will return 2.
'------------------------------------------------------------------
Dim strHold As String, itemhold As Variant
Dim placehold As Integer
Dim i As Integer, j As Integer

strHold = TheStr
itemhold = TheItem
j = 0


If InStr(1, strHold, itemhold) > 0 Then
While InStr(1, strHold, itemhold) > 0
placehold = InStr(1, strHold, itemhold)
j = j + 1
strHold = Mid(strHold, placehold + Len(itemhold))
Wend
'Debug.Print "StrCount= " & j
End If
StrCount = j
End Function

Using Access 2000

 

[ecawilkinson]

hi,

I can see that the function is add bullet points only if they have not been added already, but it's logic is flawed and in fact only checks after the first line end, so that if you remove just that bullet point, it will end up added them again from line 2 onwards. I have trimmed that function down. Admittedly, it only checks to see if the first character is a bullet point but that is no worse than the current function:

    If Left(sNotes, 1) <> "•" Then
        sNotes = Replace(sNotes, vbCrLf, vbCrLf & "• ")
        sNotes = "• " & sNotes
    End If

VBA does not have very advanced inbuilt string functions, so what seems simple requires you to write your own function.

HTH,
Chris

 

[fearoffours]

Aaah yes, you're right.
I did originally have the code you suggested, but of course every time a user updates the form, further bullet points are added!
Really I need some kind of regex or wildcard expression to replace vbCrLf & [!•] but Replace() won't accept wildcards (bizarrely).
I think writing my string manipulation function is possibly beyond me at the moment.

 

[Rabbie]

Aaah yes, you're right.
I did originally have the code you suggested, but of course every time a user updates the form, further bullet points are added!
Really I need some kind of regex or wildcard expression to replace vbCrLf & [!•] but Replace() won't accept wildcards (bizarrely).
I think writing my string manipulation function is possibly beyond me at the moment.

See this link for advice on getting Regex in VBA. Hope it helps

 

[FizzyFish]

Try this code, it seems to work for me:

Private Sub Notes_AfterUpdate()
    Dim sNotes As String
    Dim sEolPos As String, sLastEOLPos As Integer
    Dim X As Integer
    Dim NewLines As Integer
    
    sNotes = Notes.Value

    NewLines = StrCount(sNotes, vbCrLf)

    'First add the bullet point to the first line.
    sLastEOLPos = 1
    For X = 1 To NewLines
    'Now look for and add bullet points to the additional lines.
        sEolPos = InStr(sLastEOLPos + 2, sNotes, vbCrLf, vbBinaryCompare)
        If Left(sNotes, 1) <> "•" Then
            sNotes = Replace(sNotes, vbCrLf, vbCrLf & "• ")
            sNotes = "• " & sNotes
        End If
    Next
    
    If Left(sNotes, 1) <> "•" Then
        sNotes = "• " & sNotes
    End If

    Notes.Value = sNotes
End Sub

 

[fearoffours]

Thanks FizzyFish, that unfortunately maintains the problem of it continuing to add bullets on any successive edits. However I've nearly cracked it by breaking it down into a line at time and dealing with each line individually. Just need to stop it removing the last line! I'll post my solution when I'm done, or what I've managed to do once I get stuck!

 

[fearoffours]

Here we go, this is the (commented) code that works for me. Hope it's not too obfuscated that others won't find it impossible to read or even adapt if they want to!

Private Sub Notes_AfterUpdate()
    Dim sNotes As String, sEditedNotes As String
    Dim CurEolPos As Integer, NextNewLinePos As Integer
    Dim sCurSubStr As String
    Dim X As Integer
    Dim NewLines As Integer
    
    'Set some variables
    sNotes = Notes.Value
    sEditedNotes = ""
    'Count new lines, but add one in case the last line does not have a carriage return
    NewLines = StrCount(sNotes, vbCrLf) + 1
    NextNewLinePos = 1

    'Check each line individually for bullets
    For X = 1 To NewLines
        
        'Find the next EOL character,
        CurEolPos = InStr(NextNewLinePos, sNotes, vbCrLf)
        
        'If we didn't find an EOL...
        If CurEolPos = 0 Then
            'Then it's the last line of the field. So just get to end of string
            sCurSubStr = Mid(sNotes, NextNewLinePos)
        Else
            'Otherwise, the substring is taken from the beginning of the line to the next EOL
            sCurSubStr = Mid(sNotes, NextNewLinePos, CurEolPos - NextNewLinePos)
        End If

        'Is the first character of the substring a bullet?
        If Left(sCurSubStr, 1) <> "•" Then
            'No, then add a bullet.
            sCurSubStr = "• " & sCurSubStr
        End If
        
        'The next new line will begin 2 characters from the current EOL
        NextNewLinePos = CurEolPos + 2
        
        'Is it the last line?
        If X <> NewLines Then
            ' No, then add the line and a line break
            sEditedNotes = sEditedNotes & sCurSubStr & vbCrLf
        Else
            ' Yes, just add the last line
            sEditedNotes = sEditedNotes & sCurSubStr
        End If
        
    Next X
    
    Notes.Value = sEditedNotes
End Sub

And thanks to everyone who helped me look more closely at what the code was doing!