ADO - Array - Ordering

[dynamictiger]

This is difficult to explain so bear with me. I have a report that is populated via code with a series of unknown values (unknown to me at development time).

Each of these values is measured against another value being the target value. So far so good.

If the unknown value is outside the target value then we need to add some unknown instruction. This much I can get done.

However, the order of instruction is not necessarily the order of comparison. This is where I am stuck.

So how does it work?

I have set up some loops in the report code to walk through the unkown values and compare them to the target range. If they fail, the value of the failing Unknown is passed as an ID to a recordset that searches a query and finds the appropriate instruction (assuming there is one).

This is then added to a recordset array. In the following manner:

'Set up the array as we have not been here before
If intFirst = 0 Then
    
        Set rstArray = New ADODB.Recordset
    
        'Append fields to the array for later access
    
        rstArray.Fields.Append "TreatOrder", adInteger
        
        rstArray.Fields.Append "Introduction", adVarChar, 250
        
        rstArray.Fields.Append "ChemicalName", adVarChar, 250
        
        rstArray.Fields.Append "Conclusion", adVarChar, 250
        
        rstArray.Index = "TreatOrder"
        
        rstArray.Sort = "TreatOrder DESC"
               
        intFirst = 1
        
    End If

However, it fails to be sorted in any order when I test print as below:

'Testing onlyxxxxxxxxxxxxxxxxxxxxxxx
        
        'commented out tried earlier rstArray.Sort = "TreatOrder Desc"
        
        Debug.Print rstArray.Fields("TreatOrder") & "  " & rstArray.Fields("Introduction") & rstArray.Fields("Conclusion")
        
        rstArray.MoveFirst
        
        Do Until rstArray.EOF
        
        strHolddps = rstArray.Fields("TreatOrder") & "  " & rstArray.Fields("Introduction") & rstArray.Fields("Conclusion")
        
        Debug.Print strHolddps
        
        Call dpsPopulatePrint
                        
        strHolddps = ""
        
        rstArray.MoveNext
        
        Loop
        'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Does anyone know how to force the array to print in the order specified?

 

[cmmrfrds]

I don't think you need to set the index since ADO will set it automatically on the sort field. I am not sure what this causes ADO to do.
?? rstArray.Index = "TreatOrder"


The sort would need to be done after the recordset is filled up not at the point of defining.

rstArray.Sort = "TreatOrder DESC"

Are you sure that TreatOrder has values??

 

[cmmrfrds]

Run this function to show the sort working.


Public Function FabricatedRS()

Dim rs As ADODB.Recordset
Dim varArray As Variant
Set rs = New ADODB.Recordset

With rs.Fields
.Append "myid", adInteger
.Append "mydesc", adVarChar, 50, adFldIsNullable
End With

varArray = Array("myid", "mydesc&quot

With rs
.Open
.AddNew varArray, Array(1, "first desc&quot
.AddNew varArray, Array(2, "second desc&quot
.AddNew varArray, Array(3, "third desc&quot
.AddNew varArray, Array(4, "fourth desc&quot
End With
rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Sort = "myid DESC"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = "myid = 3"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = adFilterNone

rs.Close
Set rs = Nothing

End Function