ADO Error -2147217864 ???

Hi all,

Sometimes I get the above error (ie. Row cannot be located for updating
etc...) exactly on the RS.Update line below:

Private Function UpdateRow(ByVal sTable As String, oFields As clsFields) As
Boolean
On Error GoTo trap_err

Dim RS As ADODB.Recordset
Dim oField As ADODB.Field

Set RS = OpenRecordset("SELECT * FROM " & sTable & " WHERE id = " &
oFields("id"), adOpenKeyset, adLockPessimistic)
If Not RS Is Nothing Then
'Fill the recordset fields
For Each oField In RS.Fields
If oField.Name <> "id" Then
Debug.Print oField.Name, oFields(oField.Name).Value
If Not IsEmpty(oFields(oField.Name).Value) Then
Select Case oField.Type
Case adDate
If oFields(oField.Name).Value = vbNullString
Then
oField.Value = Null
Else
oField.Value = oFields(oField.Name).Value
End If

Case adSmallInt, adTinyInt, adUnsignedBigInt,
adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt
oField.Value = Val(oFields(oField.Name).Value)

Case Else
oField.Value = oFields(oField.Name).Value
End Select
End If
End If
Next oField

'Update the recordset
RS.Update
UpdateRow = True
End If

trap_err_exit:
Set oField = Nothing
Set RS = Nothing
Exit Function

trap_err:
UpdateRow = False
msError = "(" & Err.Number & ") " & Err.Description
#If DEBUGLOG = 1 Then
If Not oField Is Nothing Then
LogWrite "Field: " & sTable & "." & oField.Name & " - " &
msError
Else
LogWrite "Table: " & sTable & " - " & msError
End If
#End If
Resume trap_err_exit

End Function

It happens only in particular occasions and I can't figure out why. Anybody
encountered this problem before?

Thanks,
Ivan

The code is missing relevant details, where is AddNew called, and is Update
called for the same record at any other point before the record pointer is
moved? If so you need to set the dynamic property

RS.Properties("Change Inserted Rows") = True

after creating but before opening the recordset.


-Mark





"Ivan Debono" <ivanmdeb RemoveThis @hotmail.com> wrote in message
news:uxQOdLNEFHA.1296@TK2MSFTNGP10.phx.gbl...
 > Hi all,
 >
 > Sometimes I get the above error (ie. Row cannot be located for updating
 > etc...) exactly on the RS.Update line below:
 >
 > Private Function UpdateRow(ByVal sTable As String, oFields As clsFields)
 > As Boolean
 > On Error GoTo trap_err
 >
 > Dim RS As ADODB.Recordset
 > Dim oField As ADODB.Field
 >
 > Set RS = OpenRecordset("SELECT * FROM " & sTable & " WHERE id = " &
 > oFields("id"), adOpenKeyset, adLockPessimistic)
 > If Not RS Is Nothing Then
 > 'Fill the recordset fields
 > For Each oField In RS.Fields
 > If oField.Name <> "id" Then
 > Debug.Print oField.Name, oFields(oField.Name).Value
 > If Not IsEmpty(oFields(oField.Name).Value) Then
 > Select Case oField.Type
 > Case adDate
 > If oFields(oField.Name).Value = vbNullString
 > Then
 > oField.Value = Null
 > Else
 > oField.Value = oFields(oField.Name).Value
 > End If
 >
 > Case adSmallInt, adTinyInt, adUnsignedBigInt,
 > adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt
 > oField.Value = Val(oFields(oField.Name).Value)
 >
 > Case Else
 > oField.Value = oFields(oField.Name).Value
 > End Select
 > End If
 > End If
 > Next oField
 >
 > 'Update the recordset
 > RS.Update
 > UpdateRow = True
 > End If
 >
 > trap_err_exit:
 > Set oField = Nothing
 > Set RS = Nothing
 > Exit Function
 >
 > trap_err:
 > UpdateRow = False
 > msError = "(" & Err.Number & ") " & Err.Description
 > #If DEBUGLOG = 1 Then
 > If Not oField Is Nothing Then
 > LogWrite "Field: " & sTable & "." & oField.Name & " - " &
 > msError
 > Else
 > LogWrite "Table: " & sTable & " - " & msError
 > End If
 > #End If
 > Resume trap_err_exit
 >
 > End Function
 >
 > It happens only in particular occasions and I can't figure out why.
 > Anybody encountered this problem before?
 >
 > Thanks,
 > Ivan
 ><!-- ~MESSAGE_AFTER~ -->