Strange Loop on ADODB Form Recordset

[gray]

Hi

Access 2007

I've noticed an issue when looping thru' an ADODB Rs?

I build an SQLLine string based on various requirements and then open the ADODB Rs with:-

Set Edit_rstADO = New ADODB.Recordset
 
With Edit_rstADO
        Set .ActiveConnection = Edit_Cnn
        .Source = SQLLine
        .LockType = adLockOptimistic
        .CursorType = adOpenStatic   'for adds
        .CursorLocation = adUseClient
        .Open
        .Sort = "[Field_Selected] DESC,[TBL1.List_Order]"
End With

Then I attach it to my form thus:-

    Set Cntrlbx.Form.Recordset = Edit_rstADO

You will notice in the .Sort above that I have 'Field_Selected' and 'List_Order' columns (the TBL1. prefix is needed on the latter cos' the SQL String has got a table join in it). There is an autonum prinary key called 'Unique_No' in the Rs.

All well and good.

The field_selected and list_order columns allow users to select various records and to 'human-sort' them in their preferred order... great.. no problem there.

Now because users might give two records the same 'list_order' number, I do an 'automatic' re-order of the list_orders in the Rs to make sure they are shipshape.

So:-

'First detach from form
Set Cntrlbx.Form.Recordset = Nothing
 
With Edit_rstADO
        .MoveLast
        .MoveFirst 'make sure all records are collected
 
        .Sort = "[Field_Selected] DESC,[TBL1.List_Order]"
        .MoveFirst
        Do Until .EOF
            If ![Cntcts_Unique_No] = Curr_User_Unique_No Then
                List_Order_No = List_Order_No + 1
                ![TBL1.List_Order] = List_Order_No
                .Update
            End If
       .MoveNext
        Loop
End With
 
're-attach to form
Set Cntrlbx.Form.Recordset = edit_rstAdo

This works happily until there are two list_orders with the same number... so.... if there are 10 records total and someone marks 3 of those 10 records with, say, list_order=4... the Do Until .EOF loop updates the first list_order=4 with it's newly auro-calulated list_order but then seems to miss the 2nd and 3rd instances of list_order=4.... the result is that some of the records do not get a newly auto-calculated 'list_order'....

Of source, the whole point of the auto-re-order is to make sure each record has it's own unique list_order but it fails under these circumstances.

Any ideas anyone please?

Thanks.

 

[gray]

Is it OK if I bump this one please?... still stuck with it....

 

[spikepl]

Are you assuming that the records do not get resorted after you change the value on which you sort?

 

[gray]

Hi - thanks for the reply.

So when (having updated 1x a list_order) I do the '.update' does the Rs get re-sorted immediately automatically by Access?

If so, I suppose I could load all the PK unique_nos into an array and then use that array in a 'find' to work thru' therecords in-sequence?

 

[spikepl]

I have no idea - but if your code otherwise is logically correct, then I would check whether the sort is retained at all times. One could argue both ways. Surely the docs say something.

Or you could also try updating the records from the last to the first in stead: sometimes messing with records inside a loop has unexpected results.

 

[gray]

Hi

I've a feeling this is to do with the cursor type in the form's RS... going to dabble a little to see if that will cure it....

 

[gray]

Hi

Hmmm.... With the connection for my RS set to :-

Set Edit_Cnn = CurrentProject.AccessConnection

The loop thru' the Rs works without missing records and re-numbers them properly....

With the connection for my RS set to:-

Set Edit_Cnn = CurrentProject.Connection

It does not. Great I'll simply change it.... no I won't because if I set it to the former I cannot do and try an .addnew... I get:-

"3251 - Object or provider is not capable of performing requested operation."

I despair... I really do.....

 

[gray]

OK for anyone else out there, this is how I've tackled it.

I open my RS with

Set Edit_Cnn = CurrentProject.Connection
Set Edit_rstADO = New ADODB.Recordset

With Edit_rstADO
Set .ActiveConnection = Edit_Cnn
.Source = SQLLine
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With

This allows me to carry out .addnew .... but leaves the issue of looping aound the Rs and updating my list_order column.

How I got around that is to:-

1. set a new adodb Rs to my form's recordset.clone (note: recordset.clone rather than recordsetclone)
2. sort the new Rs as req'd then .movefirst
3. loop thru' the new Rs and store the PK autonumbers in an dynamic array
4. close the new rs and set it to 'nothing'
5. loop around the array, grab the next number in the sequence and do a .find on the form's Rs for it
6. when the .find positions the form's RS cursor, update my list_order field, do a .update and then a .movefirst before the next array loop

What a parlaver!