ADO Sort Idiosyncrasy

[gray]

Hi All

Access 2002/2007
WinXP Pro SP2

I have a strange issue with the sorting of an ADO recordset.

Background
-------------
I have a form with an unlinked subform. The subform is populated by the main form using an ADO recordset and within a begintrans/endtrans. The SQL statement used to populate it includes a Join... table aliases are used in the Join because some field names are common to both tables.

SQLLine = "SELECT TBL1.*, TBL2.* FROM ((User_Preferences AS TBL1) INNER JOIN Captions AS TBL2 ON TBL1.Captions_Unique_No=TBL2.Unique_No) 
WHERE blah blah"
 
Set Edit_Cnn = CurrentProject.Connection
Edit_Cnn.BeginTrans
 
Set Edit_rstADO = New ADODB.Recordset
 
With Edit_rstADO
        Set .ActiveConnection = Edit_Cnn
        .Source = SQLLine
        .LockType = adLockOptimistic
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .Open
        .Sort = "[Field_Selected] DESC,[TBL1.List_Order]"
End With

The form/subform allows management of users' print fields... it permits selection/de-selection of fields plus the movement up or down relavant to the other fields.

The leftmost table, TBL1, has

1. Unique_No - PK Autonum
2. Field_Selected - Boolean
3. List_Order - Int
4. Cntcts_Unique_No - Long - Logged-in Users' IDs

The ADO is sorted at form-open with:-

.Sort = "[Field_Selected] DESC, [TBL1.List_Order]"

All is well in the form.

Problem
---------
In case the List_Nos get out of step, I have a simple re-order routine which loops around the ADO Rs and, starting with 1 at the first record, increments the List_Nos by 1. Easy enough:-

With Edit_rstADO
        're-apply sort
        .Sort = "[Field_Selected] DESC, [TBL1.List_Order]"
        .MoveFirst
 
       Do Until .EOF
            MsgBox "Loop No= " & List_Order_No & ",  Rec No=" & ![TBL1.Unique_No]
            If ![Cntcts_Unique_No] = Curr_User_Unique_No Then
                If ![TBL1.Description] = Mode_Parm Then
                    If ![TBL1.Deleted] = False Then
                        List_Order_No = List_Order_No + 1
                        ![TBL1.List_Order] = List_Order_No
                        .MoveNext
                    Else
                        ![TBL1.List_Order] = "-1"
                        .MoveNext
                    End If
                End If
            End If
        Loop
    End With

There are a number of records which have duplicate List_Nos ... e.g. see records 799 and 697 on attached jpg.... so I call the re-order routine.

However, with the above sort applied, the msgbox for "Loop No and [TBL1.Unique_No]" reveals that duplicate[List_Nos] are missed... notice that 697 and 799 are not included in (attached) List_Loop_v01... and as a result are not re-nunbered.

If I simply change the sort to:-
.Sort = " [TBL1.Unique_No]"
then 697 and 799 are included in the loop... see (attached) List_Loop_v02...

Any ideas anyone please?

 

[boblarson]

Again, I don't understand why you are complicating matters by trying to do this with ADO instead of just using the SQL Statement to sort. Put an ORDER BY clause in your SQL Statement and then there is no need to sort using ADO.

 

[gray]

Hi Bob

I do that in most of my routines.. I've even built some management code to permit users to use the front-end sorts and filters which are then applied using that precise method.

In this case though it's not so straightforward. The RS is part of an SQL Transaction. The Rs will get sorted initially OK but after adding records into and/or moving them around, the sort needs to be re-applied. Because it's part of an SQL transaction, requerying (to pick up the re-arrangement in the display) cannot be done. Worse still, after two RS re-orderings (when using the ORDER BY method) Access crashes.... take the ORDER BY out and it doesn't (Catch 22 I think you say States-side?)

thanks

 

[gray]

sorry to bump this but i am utterly baffled...

 

[jdraw]

Please provide a more detailed description of the SQL transaction. I'm a bit confused on the addition of records and what seems multiple sorts. Just trying to get familiar with your situation.
I would have thought the SQL sort (Order By) would apply.

 

[Galaxiom]

One of the things that I don't understand:

![TBL1.List_Order] = "-1"

This line indicates the List_Order field is text yet you are sorting and performing arithmetic operations on it.

 

[gray]

Hi

Thanks for looking at this and apologies for delayed reply... It was getting late here and I crashed out.

1.

This line indicates the List_Order field is text yet you are sorting and performing arithmetic operations on it.

You are absolutely right, it's an INT and should have read List_Order= -1 ... it now does...thanks for picking that up.

2.

Please provide a more detailed description of the SQL transaction. I'm a bit confused on the addition of records and what seems multiple sorts. Just trying to get familiar with your situation.
I would have thought the SQL sort (Order By) would apply.

Here's how the resources are declared:-

Public Edit_rstADO As ADODB.Recordset
Public Edit_Cnn As ADODB.Connection

This is how I populate the RS and declare the Transaction:

SQLLine = "SELECT TBL1.*, TBL2.* FROM ((User_Preferences AS TBL1) INNER JOIN Captions AS TBL2 ON TBL1.Captions_Unique_No=TBL2.Unique_No) 
WHERE blah blah"
 
Set Edit_Cnn = CurrentProject.Connection
Edit_Cnn.BeginTrans
 
Set Edit_rstADO = New ADODB.Recordset
 
With Edit_rstADO
        Set .ActiveConnection = Edit_Cnn
        .Source = SQLLine
        .LockType = adLockOptimistic
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .Open
        .Sort = "[Field_Selected] DESC,[TBL1.List_Order]"
End With

Here's why I sort at the RS level and not the SQL resultset:-

The Rs will get sorted initially OK using ORDER BY but after adding records into and/or moving them around, the sort needs to be re-applied. Because it's part of an SQL transaction, requerying (to pick up the re-arrangement in the display) cannot be done. Worse still, after two RS re-orderings (when using the ORDER BY method) Access crashes.... take the ORDER BY out and it doesn't?

3.

I'm a bit confused on the addition of records and what seems multiple sorts

The aim is to allow users select/de-select fields, to add further fields into the RS (I use .addnew and .update to do so) and to change their position (List_Order) in the list. When any of these are done the RS needs to be resorted so that the records are displayed in the new order.

Thanks Gents