I used the following code to update a database record field (dblParentTagID) when a treeview node had been moved. It worked fine with DAO and when I updated it to ADO I got problems. I found it updates multiple records (any that have the same dblParentTagID) to the new dblTagID, even though the SELECT statement only returns one record (In fact the dblTagID is unique). The solution is to include dblTagID in the SELECT string (As shown thus for readability {@@@ dblTagID @@@})
I guess this gives the cursor something more fixed to work with.
My question is why does this happen? and why didn't it happen using DAO?
Public Function ModDatabaseUpdate(nodX As Node) As Boolean
On Error GoTo ErrorTrap
Dim db As ADODB.Connection
Dim rsFind As ADODB.Recordset
Set db = New Connection
db.CursorLocation = adUseClient
Set rsFind = New ADODB.Recordset
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & sProj
strSQL = "SELECT {@@@ dblTagID @@@},dblParentTagID FROM tblEquip WHERE dblTagID = " & Val(nodX.Key)
''--------------------------------------------------------
If rsFind.State > 0 Then rsFind.Close
rsFind.Open strSQL, db, adOpenDynamic, adLockOptimistic, adCmdText
''========================================================
With rsFind
.Fields("dblParentTagID") = Val(nodX.Parent.Key)
.Update
.Close
End With
Set rsFind = Nothing
db.Close
Set db = Nothing
ModDatabaseUpdate = true
ExitError:
Exit Function
ErrorTrap:
ModDatabaseUpdate = False
Resume ExitError
End Function
