Karen Schaefer
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
Any suggestions is greatly appreciated.
K
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
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
REQ-EMO.png
ASKER
Here some more of my code that might help. Look a the GetCommentData_EMO code - My attempt to modify the original GetCommentData.
K
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
ASKER
does this make any sense to you. Let me know if you are still review this problem.
K
K
Sorry ... I have to leave for a dr appt now ... hopefully someone else will be along in the meantime.
Sorry.
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?
ASKER
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
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
REQEMO-RESULTS.jpg
ASKER
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
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
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.
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.
ASKER
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
K
ASKER
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.
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.
ASKER
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
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.
===================
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.
ASKER
"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
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).
==========================
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).
ASKER
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.
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
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
If I specify REQ EMO 2 = 9999 on the left, press the button, say "yes" to the prompt, then the comments become
while you expect that they should become
Is this correct?
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
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
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
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?
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again
Well, we have the Replace() Function ... eg
Replace([YourMemoField", "SomeSearchText", "")
This would effectively delete the text if found.
mx