Replace text w/i Memo field w/o affecting the remaining data. (1 Viewer)

[kfschaefer]

I am looking to modify the following code to replace a portion of a memo field w/o affecting the data within the memo field. I have tried numerous methods and this is the closes I got, unfortunately it only works partly.

Say My comment field contains the following:
'******************************************
John Smith is going to the country

Work Code: Work In Lab

This is a test of the replace code.
'******************************************
So when the user wishes to change the Work Code from "Work Code: Work in Lab" to "Work Code: Work in Field"
I want to only change that portion of the comments.

My current code changes that portion, but unfortunately, removes the remaining comments "This is a Test of the replace Code".

What am I missing?

Thanks,

Karen
'************************************************************************************

Public Sub Work_Code_AfterUpdate()
strSearch = Me.RequestorComments.Value
gJG = Nz(Me.Job_Group)
Select Case Work_Code
Case 1
gWC = Me.Work_Code.Column(1)
Case 3
gWC = Me.Work_Code.Column(1)
End Select
sFieldName = "Work Code: " & gWC
strReplaceWith = sFieldName
If strSearchFor = vbNullString Then

If Len(Nz(Me.RequestorComments)) > 0 Then
Me.RequestorComments = Me.RequestorComments & vbCrLf & sFieldName
Else
Me.RequestorComments = sFieldName
End If
ElseIf strSearchFor = sFieldName Then
GoTo ResumeNext:
ElseIf strSearchFor <> sFieldName Then
recValue = Substitute(strSearchFor, strReplaceWith, strSearch)
Me.RequestorComments = recValue
End If
ResumeNext:
Me.RequestorComments.Requery
End Sub
'************************************************************************************
Private Sub Work_Code_Dirty(Cancel As Integer)
strSearch = "Work Code: "
gWC = Me.Work_Code.Column(1)
strSearchFor = strSearch & gWC

End Sub
'************************************************************************************

Function Substitute(strSearchFor As String, strReplaceWith As String, strSearch As String) As String
On Error GoTo Oops
Dim I As Integer, LastTest As Integer, Temp As String

If Len(strSearchFor) > Len(strSearch) Then
Substitute = strSearch
GoTo Out
End If
LastTest = Len(strSearch) - Len(strSearchFor) + 1

For I = 1 To LastTest
If Mid(strSearch, I, Len(strSearchFor)) = strSearchFor Then
Temp = Temp & strReplaceWith
I = I + Len(strSearchFor) - 1
Else
If I < LastTest Then
Temp = Temp & Mid(strSearch, I, 1)
Else
Temp = Temp & Right(strSearch, Len(strSearchFor))
End If
End If
Next I
Substitute = Temp
Out:
Exit Function

Oops:
Substitute = strSearch
Resume Out

End Function
'************************************************************************************


--------------------
Thanks,

K

 

[ByteMyzer]

If you are using Access 2000 or newer, try the Replace() function:

Replace Function (Visual Basic)


If you are using Access 97, try the code at the following link:

ACC: How to Replace a String with Another String

 

[kfschaefer]

I did try the Replace function and it does not replace just the portion I am looking to replace it replaces the entire text w/i the Memo.

Please show me the best way to use this function so it does not replace the entire text?

 

[ByteMyzer]

If your memo field has the following content:

John Smith is going to the country

Work Code: Work In Lab

This is a test of the replace code.

...and if strSearchFor = "Work Code: Work In Lab", and strReplaceWith = "Work Code: Work In Field", then the following statement will work:

recValue = Replace(strSearch, strSearchFor, strReplaceWith)

 

[kfschaefer]

thanks, that did it, however, Now I need to expand the replace function to include comparing the data w/i the Comments to the values of a table - to see if there are any differences and if so update the Comments the same was as for the Work Code.

See the attached screen shot of my form. I need to compare the data from the EquipmentDetail with the data in the Comments. If different the replace the comments w/o affecting the remaining data.

Here is what I have so far using the Replace function from MS Knowledge base.
'**********************************************************
Private Sub cmdCopyWSNo_Click()
Dim sFieldName, recValue, recValue1, strSearch1
Dim rs1 As Recordset

Select Case Work_Code
Case 1
gWC = [Forms]![frmSR_Main]!Work_Code.Column(1)
Case 3
gWC = [Forms]![frmSR_Main]!Work_Code.Column(1)
End Select
gJG = [Forms]![frmSR_Main]![JobGroup]
gLab = [Forms]![frmSR_Main]![Cmis_Lab]
cntx = DCount("[RecID]", "tblEquipListingPerJobGroup", "[Job_group]= " & Chr(39) & gJG & Chr(39) & "")
cntx1 = DCount("[RecID]", "tblEquipListTemp", "[Job_group]= " & Chr(39) & gJG & Chr(39) & "")
Debug.Print cntx
Debug.Print cntx1
strSQL = "SELECT *" & _
" FROM tblEquipListTemp" & _
" WHERE (Job_Group=" & Chr(39) & gJG & Chr(39) & ")"
Set rs = CurrentDb.OpenRecordset(strSQL)
sFieldName = rs.Fields("Equipment_ID").Name & " " & rs.Fields("MeasNo").Name & _
" " & rs.Fields("WSNo").Name & ""

strSQL1 = "SELECT *" & _
" FROM tblEquipListingPerJobGroup" & _
" WHERE (Job_Group=" & Chr(39) & gJG & Chr(39) & ")"
Set rs1 = CurrentDb.OpenRecordset(strSQL1)

If Nz(RequestorComments, vbNullString) = True Then
If gLab = "F100" Then
recValue = "Work Code: " & gWC
GoSub MyUpdate:
Else
'Data from tblEquipListTemp
rs.MoveFirst
Do Until rs.EOF
recValue = recValue & vbCrLf & _
rs!Equipment_ID & " " & _
rs!MeasNo & " " & rs!WSNo & ""
rs.MoveNext
Loop
recValue = sFieldName & vbCrLf & recValue & vbCrLf & "Work Code: " & gWC
Forms![frmSR_Main]![RequestorComments].Value = recValue
GoTo ResumeNext:
End If

ElseIf Len(Nz(Me.RequestorComments)) > 0 Then
rs.MoveFirst
Do Until rs.EOF
'Data from tblEquipListTemp
recValue = recValue & vbCrLf & _
rs!Equipment_ID & " " & _
rs!MeasNo & " " & rs!WSNo & ""
rs.MoveNext
Loop
recValue = sFieldName & vbCrLf & recValue
'Data from tblEquipListingPerJobGroup
rs1.MoveFirst
Do Until rs1.EOF
recValue1 = recValue1 & vbCrLf & _
rs1!Equipment_ID & " " & _
rs1!MeasNo & " " & rs1!WSNo & ""
rs1.MoveNext
Loop
recValue1 = sFieldName & vbCrLf & recValue1
GoSub MyUpdate:
End If
MyUpdate:
'cntx = count of Records from tblEquipListingPerJobGroup
'cntx1 = count of Records from tblEquipListTemp
If cntx <> cntx1 Then
strSearchFor = recValue1 'Data from tblEquipListingPerJobGroup
Debug.Print strSearchFor
strReplaceWith = recValue 'Data from tblEquipListTemp
Debug.Print strReplaceWith
strSearch = Forms![frmSR_Main]![RequestorComments]
Else
GoTo ResumeNext:
End If
Forms![frmSR_Main]![RequestorComments].Value = Replace(strSearch, strSearchFor, strReplaceWith)
GoTo ResumeNext:
Return
ResumeNext:
rs.Close
Set rs = Nothing
rs1.Close
Set rs1 = Nothing
Forms![frmSR_Main].RequestorComments.Requery
End Sub


'*********************************************************
Function Replace(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
Replace = Temp
End Function
'******************************************************
Debug.Print strSearchFor = following results

Equipment_ID MeasNo WSNo

Test1 A101A
Test4 A101A
Test3 A101A
Test2 A101A
'******************************************************
Debug.Print strReplaceWith = following results
Equipment_ID MeasNo WSNo

Test1 A101A
Test4 A101A
Test3 A101A
Test2 A101A
Test31 A101A
Test41 A101A

'*******************************************************

However the data does not get replaced. What am I missing?

K