Problem to modify an open ADODB recordset in VBA

[Atlas123]

I load the recordset with this qry which is strsql in the vba below

select

' ' as UltimateParent, .....etc etc from.....

Then once the field UltimateParent is in the recordset and it is open I try to modify it like this:

Set Rs1(RsLevel, RsIndex) = New ADODB.Recordset

With Rs1(RsLevel, RsIndex)

.CursorLocation = adUseClient

.CursorType = adUseClient

.LockType = adLockOptimistic

.Open strSQL, ConnACDNV

End With



Do Until Rs1(RsLevel, RsIndex).EOF

Rs1(RsLevel, RsIndex).Fields("UltimateParent").Value = "test"

Rs1(RsLevel, RsIndex).Update

Rs1(RsLevel, RsIndex).MoveNext

Loop

The error comes when I try to set the value of the first record to "test"
The error is: Multiple - step operation generates errors. Check each status value.
How do I make a qry with a hard coded column made on the fly in the select statement and then modify the data in the column with VBA once it is in the open recordset?

 

[xenou]

Hmmm. Well, there's nothing simple about that one. Right off the bat your select query is very complex. It seems you are left joining to a nested query that is right joined back to the same table. Offhand, it doesn't look editable. I guess I'd start there - try some direct tests to see if it can indeed be updated (create a recordset without all the other code involved and just see if you can open the recordset and make an update).

 

[CharlesChuckieCharles]

[COLOR=#333333]Do Until Rs1(RsLevel, RsIndex).EOF[/COLOR]
[COLOR=#333333]Rs1(RsLevel, RsIndex).Fields("UltimateParent").Value = " a"[/COLOR]
[COLOR=#333333]Rs1(RsLevel, RsIndex).Update[/COLOR]
[COLOR=#333333]Rs1(RsLevel, RsIndex).MoveNext[/COLOR]
[COLOR=#333333]Loop[/COLOR]
[COLOR=#333333]Rs1(RsLevel, RsIndex).MoveFirst
[/COLOR]

It should be

[B][COLOR=#333333]Rs1(RsLevel, RsIndex).MoveFirst
[/COLOR][/B][COLOR=#333333]
[/COLOR][COLOR=#333333]Do Until Rs1(RsLevel, RsIndex).EOF[/COLOR]
[COLOR=#333333]Rs1(RsLevel, RsIndex).Fields("UltimateParent").Value = " a"[/COLOR]
[COLOR=#333333]Rs1(RsLevel, RsIndex).Update[/COLOR]
[COLOR=#333333]Rs1(RsLevel, RsIndex).MoveNext[/COLOR]
[COLOR=#333333]Loop[/COLOR]

 

[Atlas123]

Hi Charles. The RS is already in the first position when the loop starts. I set it back to the first position after the loop because I need it back at the first position later on in the code. According to your suggestion I tested to make sure that the RS was really in the first position before the loop starts and it was and I still got the same error. Hope it is clear now.

 

[xenou]

If the recordset is open and you can't edit it it is most likely that the recordset is not editable because the query you are using is not editable. That can happen for many reasons - aggregate queries are a common one. Queries that don't have primary keys (or unique keys) are another.

To test for sure, check first for the field value (to make sure the field exists and you are on the record). Then try to change it:

Do Until Rs1(RsLevel, RsIndex).EOF
    Debug.Print "Recordset Rs1(" & rsLevel & ", " & rsIndex & "): " & Rs1(RsLevel, RsIndex).Value [COLOR="#008000"]'//Check recordset[/COLOR]
    Rs1(RsLevel, RsIndex).Fields("UltimateParent").Value = " a" [COLOR="#008000"]'//Attempt to update[/COLOR]
    Rs1(RsLevel, RsIndex).Update
    Debug.Print "New Value: " & Rs1(RsLevel, RsIndex).Value & vbNewLine
    Rs1(RsLevel, RsIndex).MoveNext
Loop