What is wrong with my coding?

[Paul]

I executed the followinh functions to eliminate some characters in a record
on a table. It seems to me that the function is only updating the first
record on the table even though it loops through all the records in the
recordset. Thanks

Option Compare Database
Option Explicit
Dim strFindAndReplace As String
Function ConvertString()
Dim db1 As DAO.Database
Dim rst1 As DAO.Recordset
Dim db2 As DAO.Database
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim strComment As String

Set db1 = CurrentDb
Set rst1 = db1.OpenRecordset("Tbl_Inventory_Detail", dbOpenDynaset)
If Not rst1.BOF Then
rst1.MoveFirst
Do While Not rst1.EOF
strComment = rst1!Comment
Debug.Print strComment & " - " & rst1!InventoryDetailID
Call FindAndReplace(strComment, vbCrLf & vbCrLf, "; ")
Set db2 = CurrentDb
Set rst2 = db2.OpenRecordset("Tbl_Inventory_Detail",
dbOpenDynaset)
rst2.Filter = rst1!InventoryDetailID
Set rst3 = rst2.OpenRecordset
On Error Resume Next
Debug.Print rst3!Comment
rst3.Edit
rst3!Comment = strFindAndReplace
rst3.Update
Debug.Print rst3!Comment
Set rst2 = Nothing
Set rst3 = Nothing
Set db2 = Nothing
rst1.MoveNext
Loop
End If
Set rst1 = Nothing
Set db1 = Nothing
End Function

Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String
Dim intPtr As Integer
If Len(strFindString) > 0 Then 'catch if try to find empty string
Do
intPtr = InStr(strInString, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & Left(strInString, intPtr -
1) & _
strReplaceString
strInString = Mid(strInString, intPtr +
Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strInString
strFindAndReplace = FindAndReplace
Debug.Print strFindAndReplace
End Function

 

[Ken Snell [MVP]]

A few problems...

One, you are setting rst2 and rst3 to Nothing but you never close them. This
will lead to memory leaks and possibly the inability of you to close ACCESS
because those recordsets that you opened are still in memory even though you
destroyed your link to them.

Two, I think you have misinterpreted how to set the Filter property of a
recordset. You don't just set it equal to a value, as you do here:
rst2.Filter = rst1!InventoryDetailID
You need to set it equal to a WHERE clause string without the WHERE.
Guessing here at the field names, but I think you want to use this step:
rst2.Filter = "[InventoryDetailID]=" & rst1!InventoryDetailID

With your code step, you are never filtering the rst2 recordset when you
open rst3 recordset. So rst3 always opens to the same recordset as rst2, and
you continually edit the first record in that recordset.

 

[MacDermott]

Are you working in Access 97?
If you're using anything later, there's a Replace() function which will do
what your "home-made" one seems to want to do.
And you should be able to apply it in an update query, without having to use
DAO coding at all...