Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

Remove/Delete then Replace a portion of a Memo field

Looking for the best way to replace a portion of data within a Memo field.  Either by deleting and then replacing the necessary data.  Usually within the middle of the text of the Memo field, but could be from the middle to the end, but I can't count on that happening all the time.

Any suggestions is greatly appreciated.

K
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image



Well, we have the Replace() Function ... eg

Replace([YourMemoField", "SomeSearchText", "")

This would effectively delete the text if found.

mx
Avatar of Karen Schaefer

ASKER

However, I want to delete/Replace only a portion of the text with the Memo field.  ie. the auto update of the Memo field enters data from various fields on the form without affecting the original users comments with in the memo field.

Here is my current code that updates my memo field- however when it gets to the Req EMo fields data It wants to duplicate the entry instead of replace  See '>>>>>>> portion of the code

K
Private Sub cmdCopyWSNo_Click()
    Dim MaxWidth(1 To 3) As Long
    Dim iEMO(4) As Long
    Dim i As Integer
    
    MaxWidth(1) = 15
    MaxWidth(2) = 10
    MaxWidth(3) = 0

    Set curDB = Application.CurrentDb
    sFieldName = vbNullString
    recValue = vbNullString
    strSQL = vbNullString
    
    Select Case Me.Work_Code
        Case 1, 3
            gWC = Me.Work_Code.Column(1)
    End Select
    
    gJG = Me.Job_Group
    gLab = Me.Cmis_Lab
    
    strSQL = "SELECT *" & _
            " FROM tblEquipListingPerJobGroup" & _
            " WHERE (Job_Group=" & Chr(39) & gJG & Chr(39) & ")"
    
    Set rs = curDB.OpenRecordset(strSQL)
    
    sFieldName = UCase(SPad(rs.Fields("Equipment_ID").Name, MaxWidth(1)))
    sFieldName = UCase(sFieldName & SPad(rs.Fields("MeasNo").Name, MaxWidth(2)))
    sFieldName = UCase(sFieldName & SPad(rs.Fields("WSNo").Name, MaxWidth(3)))
    
    If Nz(Me.RequestorComments) = "" Then
        If gLab = "F100" Then
            recValue = "WORK CODE: " & gWC
            GoSub MyUpdate
        Else
            'Data from tblEquipListingPerJobGroup
            With rs
                If Not (.BOF And .EOF) Then
                    .MoveFirst
                    Do Until .EOF
                        If recValue <> "" Then recValue = recValue & RSLF
                        recValue = recValue & SPad(.Fields("Equipment_ID"), MaxWidth(1))
                        recValue = recValue & SPad(.Fields("MeasNo"), MaxWidth(2))
                        recValue = recValue & SPad(.Fields("WSNo"), MaxWidth(3))
                        .MoveNext
                    Loop
                End If
            End With
            recValue = sFieldName & RSLF & recValue & RSLF & "WORK CODE: " & gWC
            Me.RequestorComments = recValue & gOrigComment
            GoTo ResumeNext:
        End If
    
    ElseIf Nz(Me.RequestorComments) <> "" Then
        If gLab = "F100" Then
            recValue = "WORK CODE: " & gWC
            GoSub MyUpdate
        'Data from tblEquipListTemp
        Else
            With rs
                If Not (.BOF And .EOF) Then
                    .MoveFirst
                    Do Until rs.EOF
                        If recValue <> "" Then recValue = recValue & RSLF
                        recValue = recValue & SPad(.Fields("Equipment_ID"), MaxWidth(1))
                        recValue = recValue & SPad(.Fields("MeasNo"), MaxWidth(2))
                        recValue = recValue & SPad(.Fields("WSNo"), MaxWidth(3))
                        .MoveNext
                    Loop
                End If
            End With
        recValue = sFieldName & RSLF & recValue & RSLF & "WORK CODE: " & gWC
        End If
    End If
    '---------------------------------------------------------------------------------------
  '>>>>>>>>>>>  'copy Req EMO data into Comments field.
    '---------------------------------------------------------------------------------------
    If Not IsNull(Me.RequestorComments) = True Then
        If InStr(Me.RequestorComments, "Req Emo") = 0 Then
            gEMO1 = Me.REQ_EMO1
            gEMO2 = Me.REQ_EMO2
            gEMO3 = Me.REQ_EMO3
            gEMO4 = Me.REQ_EMO4
        
            If Not IsNull(gEMO1) Or Not IsNull(gEMO2) Or Not IsNull(gEMO3) Or Not IsNull(gEMO4) Then
                iEMO(1) = gEMO1
                iEMO(2) = gEMO2
                iEMO(3) = gEMO3
                iEMO(4) = gEMO4
                
                For i = 1 To 4
                    If iEMO(i) > 0 Then
                      recValue = recValue & RSLF & "Req Emo" & i & ":   " & iEMO(i)
                    End If
                Next
            End If
    '    Debug.Print recValue
        ElseIf InStr(Me.RequestorComments, "Req EMO") > 0 Then

            Select Case _
                MsgBox("The Comments of this Service Request Record, currently contains REQ EMO data, Do you wish to replace Req EMO Data?", _
                vbYesNo Or vbExclamation Or vbDefaultButton1, "Req EMO Data")
            
                Case vbYes
                    gEMO1 = Me.REQ_EMO1
                    gEMO2 = Me.REQ_EMO2
                    gEMO3 = Me.REQ_EMO3
                    gEMO4 = Me.REQ_EMO4
                    If Not IsNull(gEMO1) Or Not IsNull(gEMO2) Or Not IsNull(gEMO3) Or Not IsNull(gEMO4) Then
                        iEMO(1) = gEMO1
                        iEMO(2) = gEMO2
                        iEMO(3) = gEMO3
                        iEMO(4) = gEMO4
                        
                        For i = 1 To 4
                            If iEMO(i) > 0 Then
                              recValue = recValue & RSLF & "Req Emo" & i & ":   " & iEMO(i)
                            End If
                        Next
                    End If
                      GoSub MyUpdate:
                  GoTo ResumeNext:
                Case vbNo
                    GoTo ResumeNext:
            End Select
        End If
'GoSub MyUpdate:
        
    End If
    'Modify Comments on the SR Main form
MyUpdate:
    Set gcomments = Me.Controls("RequestorComments")
    strSearchFor = GetCommentData.TestEquipmentDetails
    Set gcomments = Nothing
    strReplaceWith = recValue
    strSearch = Nz(Me.RequestorComments)
    
    If strSearchFor = "" Then
        gValue = strReplaceWith & RSLF & strSearch
    Else
        gValue = Replace(strSearch, strSearchFor, strReplaceWith)
    End If
    Me.RequestorComments = gValue
    GoTo ResumeNext:
    
    Return
    
ResumeNext:
    rs.Close
    Set rs = Nothing
    Me.RequestorComments.Requery
    Me.Repaint
    
End Sub

Function SPad(ByVal InString As Variant, Optional ByVal PadToWidth As Long = 0, _
    Optional ByVal PadChar As String = " ") As String
    'returns string InString padded with character PadChar to a total width of PadToWidth characters
    'PadToWidth positive values: padding added to end (right) of string InString
    'PadToWidth negative values: padding added to beginning (left) of string InString
    Dim n As Long
    If Len(Nz(InString)) < PadToWidth Then
        For n = 1 To Abs(PadToWidth) - Len(Nz(InString))
            SPad = SPad & PadChar
        Next n
    End If
    Select Case PadToWidth
        Case Is > 0
            SPad = Nz(InString) & SPad
        Case Is < 0
            SPad = SPad & Nz(InString)
        Case Else
            SPad = Nz(InString)
    End Select
End Function

Open in new window

REQ-EMO.png
Here some more of my code that might help.  Look a the GetCommentData_EMO code - My attempt to modify the original GetCommentData.

K
Public Function GetCommentData() As CommentData
    Dim SX() As String
    Dim SY() As String
    Dim i As Long
    Dim n As Long
    Dim blnWorkCodeFound As Boolean
    
    If gcomments Is Nothing Then Exit Function
    
    SX = Split(Nz(gcomments), RSLF)
    
    'look through array elements for work code
    For n = LBound(SX) To UBound(SX)
        If Trim(SX(n)) Like sWC Then
            blnWorkCodeFound = True
            Exit For
        End If
    Next n
    
    With GetCommentData
    
        Select Case n
            Case Is > UBound(SX)
                'work code not found within requestor comments
                .OriginalComment = Nz(gcomments)
                .TestEquipmentDetails = ""
            Case UBound(SX)
                'work code found within requestor comments as last element
                .OriginalComment = ""
                .TestEquipmentDetails = Join(SX, RSLF)
                
            Case Is < UBound(SX)
                'work code found within requestor comments at element n
                SY = SX
                ReDim Preserve SY(LBound(SY) To n)
                .TestEquipmentDetails = Join(SY, RSLF)
                n = n + 1
                For i = LBound(SX) + n To UBound(SX)
                    SX(i - n) = SX(i)
                Next i
                ReDim Preserve SX(LBound(SX) To UBound(SX) - n)
                .OriginalComment = Join(SX, RSLF)
        End Select
        
    End With
    

End Function
Public Function GetCommentData_EMO() As CommentData
    Dim SX() As String
    Dim SY() As String
    Dim i As Long
    Dim n As Long
    Dim blnEMOFound As Boolean
    
    If gcomments Is Nothing Then Exit Function
    
    SX = Split(Nz(gcomments), RSLF)
    
    'look through array elements for work code
    For n = LBound(SX) To UBound(SX)
        If Trim(SX(n)) Like sEMO Then
            blnEMOFound = True
            Exit For
        End If
    Next n
    
    With GetCommentData_EMO
    
        Select Case n
            Case Is > UBound(SX)
                'ReqEMO not found within requestor comments
                .OriginalComment = Nz(gcomments)
                .TestEquipmentDetails = ""
            Case UBound(SX)
                'ReqEMO found within requestor comments as last element
                .OriginalComment = ""
                .TestEquipmentDetails = Join(SX, RSLF)
                
            Case Is < UBound(SX)
                'wReqEMO found within requestor comments at element n
                SY = SX
                ReDim Preserve SY(LBound(SY) To n)
                .TestEquipmentDetails = Join(SY, RSLF)
                n = n + 1
                For i = LBound(SX) + n To UBound(SX)
                    SX(i - n) = SX(i)
                Next i
                ReDim Preserve SX(LBound(SX) To UBound(SX) - n)
                .OriginalComment = Join(SX, RSLF)
        End Select
        
    End With
 
End Function

Open in new window

does this make any sense to you.  Let me know if you are still review this problem.

K
Sorry ... I have to leave for a dr appt now ... hopefully someone else will be along in the meantime.
Sorry.
If you want to replace a portion of text within another text, then already suggested function Replace is the way to go. Why do you think it wouldn't work?
I am using the Replace function, however, portion of the before text is still being set in the comments after the Replace function is run.

If I update run the initial update of the comments, then make a change to the REQ_EMO fields, rerun the code and it will update the change data, but also include portion of the unchanged data to create duplicate data, see the attached image.

Here is my latest code I narrowed down the problem area see '>>>>>>>>>>>>>>>> portion of the code - especially the setting of the gvalue.  It is here it seems to want to hold onto portion of the searchfor data.

Thanks for the assistance,

K
Private Sub cmdCopyWSNo_Click()
    Dim MaxWidth(1 To 3) As Long
    Dim iEMO(4) As Long
    Dim i As Integer
    Dim a As Integer
    
    MaxWidth(1) = 15
    MaxWidth(2) = 10
    MaxWidth(3) = 0

    Set curDB = Application.CurrentDb
    sFieldName = vbNullString
    recValue = vbNullString
    strSQL = vbNullString
    a = 0
    Select Case Me.Work_Code
        Case 1, 3
            gWC = Me.Work_Code.Column(1)
    End Select
    
    gJG = Me.Job_Group
    gLab = Me.Cmis_Lab
    
    strSQL = "SELECT *" & _
            " FROM tblEquipListingPerJobGroup" & _
            " WHERE (Job_Group=" & Chr(39) & gJG & Chr(39) & ")"
    
    Set rs = curDB.OpenRecordset(strSQL)
    
    sFieldName = UCase(SPad(rs.Fields("Equipment_ID").Name, MaxWidth(1)))
    sFieldName = UCase(sFieldName & SPad(rs.Fields("MeasNo").Name, MaxWidth(2)))
    sFieldName = UCase(sFieldName & SPad(rs.Fields("WSNo").Name, MaxWidth(3)))
    
    If Nz(Me.RequestorComments) = "" Then
        If gLab = "F100" Then
            recValue = "WORK CODE: " & gWC
            GoSub MyUpdate
        Else
            'Data from tblEquipListingPerJobGroup
            With rs
                If Not (.BOF And .EOF) Then
                    .MoveFirst
                    Do Until .EOF
                        If recValue <> "" Then recValue = recValue & RSLF
                        recValue = recValue & SPad(.Fields("Equipment_ID"), MaxWidth(1))
                        recValue = recValue & SPad(.Fields("MeasNo"), MaxWidth(2))
                        recValue = recValue & SPad(.Fields("WSNo"), MaxWidth(3))
                        .MoveNext
                    Loop
                End If
            End With
            recValue = sFieldName & RSLF & recValue & RSLF & "WORK CODE: " & gWC
            Me.RequestorComments = recValue & gOrigComment
        End If
    
    ElseIf Nz(Me.RequestorComments) <> "" Then
        If gLab = "F100" Then
            recValue = "WORK CODE: " & gWC
            GoSub MyUpdate
        'Data from tblEquipListTemp
        Else
            With rs
                If Not (.BOF And .EOF) Then
                    .MoveFirst
                    Do Until rs.EOF
                        If recValue <> "" Then recValue = recValue & RSLF
                        recValue = recValue & SPad(.Fields("Equipment_ID"), MaxWidth(1))
                        recValue = recValue & SPad(.Fields("MeasNo"), MaxWidth(2))
                        recValue = recValue & SPad(.Fields("WSNo"), MaxWidth(3))
                        .MoveNext
                    Loop
                End If
            End With
        recValue = sFieldName & RSLF & recValue & RSLF & "WORK CODE: " & gWC
        End If
    End If
    '---------------------------------------------------------------------------------------
    'copy Req EMO data into Comments field.
    '---------------------------------------------------------------------------------------
    If Not IsNull(Me.RequestorComments) = True Then
        If InStr(Me.RequestorComments, "Req Emo") = 0 Then
            gEMO1 = Nz(Me.REQ_EMO1)
            gEMO2 = Nz(Me.REQ_EMO2)
            gEMO3 = Nz(Me.REQ_EMO3)
            gEMO4 = Nz(Me.REQ_EMO4)
        
            If Not IsNull(gEMO1) Or Not IsNull(gEMO2) Or Not IsNull(gEMO3) Or Not IsNull(gEMO4) Then
                iEMO(1) = gEMO1
                iEMO(2) = gEMO2
                iEMO(3) = gEMO3
                iEMO(4) = gEMO4
                
                For i = 1 To 4
                    If iEMO(i) > 0 Then
                      recValue = recValue & RSLF & "Req Emo" & i & ":   " & iEMO(i)
                    End If
                Next
            End If
        ElseIf InStr(Me.RequestorComments, "Req EMO") > 0 Then

            Select Case _
                MsgBox("The Comments of this Service Request Record, currently contains REQ EMO data, Do you wish to replace Req EMO Data?", _
                vbYesNo Or vbExclamation Or vbDefaultButton1, "Req EMO Data")
            
                Case vbYes
                    a = 1
                    gEMO1 = Nz(Me.REQ_EMO1)
                    gEMO2 = Nz(Me.REQ_EMO2)
                    gEMO3 = Nz(Me.REQ_EMO3)
                    gEMO4 = Nz(Me.REQ_EMO4)
                    If Not IsNull(gEMO1) Or Not IsNull(gEMO2) Or Not IsNull(gEMO3) Or Not IsNull(gEMO4) Then
                        iEMO(1) = gEMO1
                        iEMO(2) = gEMO2
                        iEMO(3) = gEMO3
                        iEMO(4) = gEMO4
                        
                        For i = 1 To 4
                            If iEMO(i) > 0 Then
                              recValue = recValue & RSLF & "Req Emo" & i & ":   " & iEMO(i)
                            End If
                        Next
                    End If
                        GoSub MyUpdate:
                        GoTo ResumeNext:
                Case vbNo
                    GoTo ResumeNext:
            End Select
        End If
    End If
    'Modify Comments on the SR Main form
MyUpdate:
    Set gcomments = Me.Controls("RequestorComments")
    If a = 1 Then
        strSearchFor = GetCommentData_EMO.TestEquipmentDetails
    Else
        strSearchFor = GetCommentData.TestEquipmentDetails
    End If
    Set gcomments = Nothing
    Debug.Print strSearchFor
    strReplaceWith = recValue
    Debug.Print strReplaceWith
    strSearch = Nz(Me.RequestorComments)
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    If strSearchFor = "" Then
        gValue = strReplaceWith & RSLF & strSearch
    Else
        gValue = Replace(strSearch, strSearchFor, strReplaceWith)
    End If
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Me.RequestorComments = gValue
    GoTo ResumeNext:
    
    Return
    
ResumeNext:
    rs.Close
    Set rs = Nothing
    Me.RequestorComments.Requery
    Me.Repaint
    
End Sub

Open in new window

REQEMO-RESULTS.jpg
I believe part of my problem lies with the GetCommentCode_EMO -   when it is determining the beginning and end of the current text within the comment field prior to updating the revised comment.

 It seems to be stopping the count after there is a change within the REQ_EMO data and not removing the remaining original text from that point on prior to updating the data.

Also the final results for the replace function - does not eliminate the entire original text before updating.

Any Ideas?  Sorry can't attach example - hoping the screen shot above will illustrate my problem.

K
Public Function GetCommentData_EMO() As CommentData
    Dim SX() As String
    Dim SY() As String
    Dim i As Long
    Dim n As Long
    Dim blnEMOFound As Boolean
    
    If gcomments Is Nothing Then Exit Function
    
    SX = Split(Nz(gcomments), RSLF)
    
    'look through array elements for work code
    For n = LBound(SX) To UBound(SX)
        If Trim(SX(n)) Like sEMO Then
            blnEMOFound = True
            Exit For
        End If
    Next n
    
    With GetCommentData_EMO
    
        Select Case n
            Case Is > UBound(SX)
                'ReqEMO not found within requestor comments
                .OriginalComment = Nz(gcomments)
                .TestEquipmentDetails = ""
            Case UBound(SX)
                'ReqEMO found within requestor comments as last element
                .OriginalComment = ""
                .TestEquipmentDetails = Join(SX, RSLF)
                
            Case Is < UBound(SX)
                'wReqEMO found within requestor comments at element n
                SY = SX
                ReDim Preserve SY(LBound(SY) To n)
                .TestEquipmentDetails = Join(SY, RSLF)
                n = n + 1
                For i = LBound(SX) + n To UBound(SX)
                    SX(i - n) = SX(i)
                Next i
                ReDim Preserve SX(LBound(SX) To UBound(SX) - n)
                .OriginalComment = Join(SX, RSLF)
        End Select
        
    End With
 
End Function

Open in new window

All this code you posted is too complex (for me) to just take a look and spot the problem. So, what I can offer is this:

1. upload your database here, and tell what to do in order to reproduce the problem

or

2. use debugging in VBA. Then you will be able to see the development of your recValue and strReplaceWith. Create Watch in debugging with break whenever their values change. Issue commands in the immediate window. To see the value of MyVariable type in immediate window ?Myvariable and hit enter

Sorry if you already know all the above from #2, but this is definitely what I would do as programmer if I was facing such a problem.
I can upload mdb - proprietary - and I have already attempted to debug the issue and I am stuck.  did you see my last post?

K
I meant I can't upload mdb - proprietary-
> did you see my last post?

No, I was still typing mine when you posted. But anyways, I don't think there's another way to find out other than debugging and looking at the variables at every step.

If you can't upload the whole database, then maybe you could isolate just this subroutine into a separate database, making it a test case, which you could upload.
Ok here is a sample mdb to illustrate my problem with the updating of the comments - that duplicate a portion of the data.

See attached doc that illustrates the steps and problems I am currently having - Please note that the screen shots may not look the same as sample but the steps are the same.


SampleCommentIssue.zip
SampleComments.pdf
ok, I see the database, but now please tell exactly the steps I need to do in order to see the problem. In this format please:

===================
1. in the form, the field "comments" has the following value:

EQUIPMENT_ID   MEASNO    WSNO
Test           Test      test
CM5126         0004002   A10A
WORK CODE: Work in field
Req Emo1:   2122
Req Emo2:   5555
Req Emo3:   4566
Req Emo4:   2222
Req Emo2:   222
Req Emo3:   4566
Req Emo4:   2222
Req Emo2:   222
Req Emo3:   2222
Req Emo4:   2222
test


2. click  the second REQ EMOs , which is 555

3. click button "save record only"

4. answer "yes" to the prompt to replace EMO data

5. after this the field comments should be (what?), while instead it is (what?)
===================

In the above, the contents of Comments is what is in your database in the zipfile I downloaded.

"In the above, the contents of Comments is what is in your database in the zipfile I downloaded."

Not sure what you are asking?

In the download there are 2 files correct?  1 mdb and 1 pdf correct?

the pdf illustrates the problem.

Step 1 Select a record.
2.  Review current contents of the Memo field.
3.  Change the value of REQ_EMO 2. to 4444
4.  Change the value of REQ_EMO 4. to 5555
5. Click Save Record Only button.
6. Click yes for Dialog.
7.  Review revised Memo field.  See where the updated REQ_EMO2 and 4 have been changed - but the memo field still includes some of the original memo data for the other REQ_EM)s.  
See the last page of demo - this illustrates my point.

Hope this helps
before:

===========================
QUIPMENT_ID   MEASNO    WSNO
Test           Test      test
CM5126         0004002   A10A
WORK CODE: Work in field
Req Emo1:   2122
Req Emo2:   5555
Req Emo3:   4566
Req Emo4:   2222
Req Emo2:   5555
Req Emo3:   4566
Req Emo4:   2222
Req Emo2:   222
Req Emo3:   4566
Req Emo4:   2222
Req Emo2:   222
Req Emo3:   2222
Req Emo4:   2222
test
===========================

after:
===========================
EQUIPMENT_ID   MEASNO    WSNO
Test           Test      test
CM5126         0004002   A10A
WORK CODE: Work in field
Req Emo1:   2122
Req Emo2:   4444
Req Emo3:   4566
Req Emo4:   5555
Req Emo2:   5555
Req Emo3:   4566
Req Emo4:   2222
Req Emo2:   5555
Req Emo3:   4566
Req Emo4:   2222
Req Emo2:   222
Req Emo3:   4566
Req Emo4:   2222
Req Emo2:   222
Req Emo3:   2222
Req Emo4:   2222
test
===========================

What was expected? (I'm asking because I have no idea what this all is, how it's supposed to work, what is EMO etc. - so I try to avoid any guesswork).
there should not be any duplicates of the REQ_EMO fields - there should be no more that 4 rows of data containing the REQ EMOs.

Using your example the results should look like, after changing any of the 4 REQs

EQUIPMENT_ID   MEASNO    WSNO
Test           Test      test
CM5126         0004002   A10A
WORK CODE: Work in field
Req Emo1:   2122
Req Emo2:   4444
Req Emo3:   4566
Req Emo4:   5555

Test

If I change REQ EMO2 = 1111 then the final results should be:

EQUIPMENT_ID   MEASNO    WSNO
Test           Test      test
CM5126         0004002   A10A
WORK CODE: Work in field
Req Emo1:   2122
Req Emo2:   1111
Req Emo3:   4566
Req Emo4:   5555

TEST
=========================================================
PLEASE note the I left out 1 piece of code in the sample.mdb.  see below .  You will also will need to rename the        

gValue = Replace(strSearch, strSearchFor, strReplaceWith)
 to
gValue = ReplaceME(strSearch, strSearchFor, strReplaceWith)

 in the cmdCopyWSNo_Click.
Function ReplaceME(ByVal strSearch As String, ByVal strSearchFor As _
                 String, ByVal strReplaceWith As String) As String
   Dim Temp As String, P As Long
   Temp = strSearch
   P = InStr(Temp, strSearchFor)
   Do While P > 0
      Temp = Left(Temp, P - 1) & strReplaceWith & _
          Mid(Temp, P + Len(strSearchFor))
      P = InStr(P + Len(strReplaceWith), Temp, strSearchFor, 1)
   Loop
   Debug.Print "TEMP: " & Temp & vbCrLf
   Debug.Print " ReplaceME: " & strReplaceWith & vbCrLf
   Debug.Print "Search: " & strSearchFor & vbCrLf
   ReplaceME = Temp
End Function

Open in new window

I'm still somewhat confused, because when I just open the form, before any changes, in the record 1 it already shows comments with duplicates, which you say is wrong.

But let's go to record 2. The comments initially are

EQUIPMENT_ID   MEASNO    WSNO
Test           Test      test
WORK CODE: Work in lab
Req Emo1:   1235
Req Emo2:   4546
Req Emo3:   2222
Req Emo4:   555
THIS IS A TEST DO NO   PROCESS


If I specify REQ EMO 2 = 9999 on the left, press the button, say "yes" to the prompt, then the comments become

EQUIPMENT_ID   MEASNO    WSNO
Test           Test      test
WORK CODE: Work in lab
Req Emo1:   1235
Req Emo2:   9999
Req Emo3:   2222
Req Emo4:   555
Req Emo2:   4546
Req Emo3:   2222
Req Emo4:   555
THIS IS A TEST DO NO   PROCESS

while you expect that they should become

EQUIPMENT_ID   MEASNO    WSNO
Test           Test      test
WORK CODE: Work in lab
Req Emo1:   1235
Req Emo2:   9999
Req Emo3:   2222
Req Emo4:   555
THIS IS A TEST DO NO   PROCESS


Is this correct?
while you expect that they should become

EQUIPMENT_ID   MEASNO    WSNO
Test           Test      test
WORK CODE: Work in lab
Req Emo1:   1235
Req Emo2:   9999
Req Emo3:   2222
Req Emo4:   555
THIS IS A TEST DO NO   PROCESS



Is this correct?  YES,

The example was from test data that had the wrong amount of rows of data in the comments field.

I appreciate your efforts..

Thanks,

Karen
ASKER CERTIFIED SOLUTION
Avatar of Karen Schaefer
Karen Schaefer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks again