[RESOLVED] Update ADO recordset error 3265

[westconn1]

i am trying to do a basic update of a few fields

this works

    tmp(0) = Text1(0)
    tmp.Update

so i have no real problem, just would like to know why i am getting the error

rather than updating each of the fields, would prefer to use

    tmp.Update tmp.(0), Text1(0)

where the fields and values should be able to be arrays (as with addnew, which works fine)
but testing either individual fields or arrays give the error 3265
"Item cannot be found in the collection corresponding to the requested name or ordinal"

i have also tried tmp.fields(0) and tmp("fieldname")

i am able to access all fields in select queries
any suggestions?

[dilettante]

I think this might be the trouble: The two optional arguments must be Variant.

Private Sub cmdUpdate_Click()
    'Show use of RS.Update to update a single field at a time by ordinal.
    Dim F As Long

    With RS
        For F = 0 To Label1.UBound
            'Update by ordinal F:
            .Update CVar(F), CVar(Text1(F).Text)
            'This (by name) works as well:
            '.Update CVar(.Fields(F).Name), CVar(Text1(F).Text)
        Next
    End With

Be wary of relying on default properties of objects too. There are lots of places where that can get you into trouble.

[westconn1]

explicitly converting the fields and values to variant made no difference

your demo works perfectly, even without explicitly converting fields to variant, the only difference i see is the cursor location (useserver) in mine, so i tried changing that, but no difference

i will follow up more tomorrow

[dilettante]

Yes, depending on what you pass you may sometimes get away without explicitly converting to Variant.

But I wonder if you are tripping over the default property problem?

You have this above:

tmp.Update tmp.(0), Text1(0)

But that should not even compile. maybe you really have this?

tmp.Update tmp(0), Text1(0)

That is passing the .Value of Fields(0), not the .Name so maybe:

tmp.Update tmp.Fields.Item(0).Name, Text1(0).Text

I think this is also a reasonable thing to use, and should work as well:

tmp.Update tmp(0).Name, Text1(0).Text

[westconn1]

tmp.Update tmp.(0), Text1(0)

that is a typo in the post only, at that point i had tmp.fields(0), and i failed to remove the .with fields

i also tried

    af = Array(tmp(0))
    av = Array(tmp(1))
    tmp.Update af, av

which of course is not the desired result, but the same error occurred anyway

[dilettante]

You are calling tmp.Update passing the value of Field(0) and the value of Field(1) there. What do you think that's going to do?

The 1st argument is a field name or ordinal (or array of them). Passing random crap will produce exactly the exception you are seeing.

[DEXWERX]

Update Method

Saves any changes you make to the current record of a Recordset object.

Syntax

recordset.Update Fields, Values

Parameters

Fields Optional. A Variant representing a single name or a Variant array representing names or ordinal positions of the field or fields you wish to modify.

Values Optional. A Variant representing a single value or a Variant array representing values for the field or fields in the new record.

....

maybe try

    af = Array(0,1)
    av = Array(Text1(0).Text,Text1(1).Text)
    tmp.Update af, av

[westconn1]

thank you for your inputs i have now figured what i was doing wrong, so this specific error is resolved, i now have another error, so i will stay with editing the fields, then update without arguments