First record not getting updated... 1

[Johnny42]

I'm using this snipet to update records in an MSDE database. The code works fine, however it wont update the first record in the loop any reason why ?

rs.Open "tblMaterial", cn, adOpenKeyset, adLockOptimistic, adCmdTable

    r = 2 
    Do While Len(Range("A" & r).Formula) > 0
    
   With rs
    strSQL = "select * from tblMaterial where refMaterial = " & Int(Range("A" & r).Value) 'Item1'"
    On Error Resume Next
    .Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText
    On Error GoTo 0
    
    If .State = adStateOpen Then ' successfully opened the recordset
     if .EOF Then ' no records returned
    MsgBox "????"
    Else ' one (or more records returned)
    descp = Null
    descp = Range("F" & r) & " EA OF : " & Trim(Range("G" & r))
    
    If Range("I" & r) <> "" Then
    If Range("H" & r) <> "" Then
    descp = descp & vbCrLf & Trim(Range("H" & r)) & " EA OF : " & Trim(Range("I" & r))
    Else
    descp = descp & vbCrLf & Trim(Range("I" & r))
    End If
    End If
        ' edit existing record
            
            .Fields("Material") = Range("B" & r).Value
'            .Fields("Type") = Range("B" & r).Value
            .Fields("Finish") = Range("e" & r).Value
            .Fields("Unite") = "KIT" 'Range("D" & r).Value
'            .Fields("Rouleau") = Range("B" & r).Value
'            .Fields("Marque") = Range("D" & r).Value
            .Fields("Modele") = Range("c" & r).Value
            .Fields("Remarque") = descp 'Range("H" & r).Value
            .Fields("Actif") = 1 'Range("B" & r).Value
'            .Fields("Price") = Range("E" & r).Value
'            .Fields("SupplierName") = Range("F" & r).Value
        .Update ' stores the new record
        udr = udr + 1
    End If
    .Close ' close the recordset
    End If
End With
        r = r + 1 ' next row
    Loop
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    MsgBox udr & " Records UPDATED !"

 

[Golom]

The first line

rs.Open "tblMaterial", cn, ...

opens the recordset and then

.Open strSQL, cn, adOpenKeyset, ...

attempts to open it again. You can't open an already open recordset in ADO (unlike DAO). It works after that because you do a .Close at the end of the loop.