Order a recordset by looping through it?

I'm hoping someone can help me modify this code.  The code below loops through a Microsoft MapPoint recordset, and assigns a sequentially numbered Pushpin symbol to each record in the recordset.  This code works perfectly fine, except that the recordset is not in the proper order that I need, and there doesn't seem to be a method available to sort the recordset object.  I need my recordset to be ordered according to this field:  objRS.Fields(24).Value

Is there a way that I can loop through the recordset to find the lowest value for "objRS2.Fields(24).Value", then assign the numbered pushpin symbol, then loop through the recordset again to find the next lowest value in that same field and then assign the next numbered pushpin symbol.  I guess the structure that makes the most sense to me would be some sort of nested loop, but I can't seem to figure out how to structure it.  Thanks so much.

    Dim objRS As MapPoint.Recordset
    Dim objDataSet As MapPoint.DataSet

    Set objRS = objDataSet.QueryAllRecords
    Set objDataSet = objApp.ActiveMap.DataSets.Item(2)

    intSymbol = 208
    objRS.MoveFirst

    Do Until objRS.EOF
       objRS.Pushpin.Symbol = intSymbol
       intSymbol = intSymbol + 1
       objRS.MoveNext
    Loop
DanielAttardAsked:
Who is Participating?
 
jadedataConnect With a Mentor MS Access Systems CreatorCommented:
Arrays are variables that can hold multiple elements of data.
  Dim ArrayName(15) as String
holds 15 pieces of string data with each identified as ArrayName(1), arrayname(2) etc.

There are also multi-dimensional arrays like

  ArrayName(3,3)

  Think of this double dimension array as looking like the below.  (hmmmm kinda looks like a table, ay?)
  [  ]  [  ]  [  ]
  [  ]  [  ]  [  ]
  [  ]  [  ]  [  ]

Usually these start at 0 element and work up from there (zero based integer)

See help for commands like Split(), Array() Redim and Dim for just some of the tools needed to work with arrays
0
 
jadedataMS Access Systems CreatorCommented:
Hi DanielAttard,
  have you tried

 objRS.orderby = "fieldname1, fieldname2"

regards
:)-j-
0
 
DanielAttardAuthor Commented:
Thanks for the input, but when I try that I get:

"Method or data member not found" on the OrderBy
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
ee_ai_constructCommented:
{{post to wrong question removed - ai, cs admin}}
0
 
DanielAttardAuthor Commented:
Thanks for these ideas.  They make sense to me, except I'm not that familiar with working with arrays.  Can you point me in the right direction?  
0
 
DanielAttardAuthor Commented:
Thanks jadedata.  I'll give it a shot and post back once i've got it figured out.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
If you can put it in the following form:

Select *, fnSort(Fields(24)) As SortOrder From tblMapPoint Order By fnSort()

I can give you

Public Function fnSort(SortField As Variant) As Long

code to do the job.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
correction...

Select * From tblMapPoint Order By fnSort(Fields(24))
0
 
Alan WarrenApplications DeveloperCommented:
0
 
Alan WarrenApplications DeveloperCommented:
DAO
Sort Property example
Applies To   Dynaset-Type Recordset object, Recordset object, Snapshot-Type Recordset object.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A2FA.asp

Alan
0
 
DanielAttardAuthor Commented:
Thanks to everyone for the input.  I've managed to fill the array from the recordset, but now I need to split/sort(?) the array.  How would I do that?

    Set objRS = objDataSet.QueryAllRecords
    Dim CompCount As Integer
    CompCount = objDataSet.RecordCount
    ReDim CompArray(CompCount) As Integer
    Dim strArray As String
    Dim i As Integer
    objRS.MoveFirst
    For i = 0 To CompCount - 1
      CompArray(i) = objRS.Fields(24).Value
      strArray = strArray & objRS.Fields(24).Value & ", "
        objRS.MoveNext
    Next
Debug.Print strArray
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Hi DanielAttard,

I guess Jack had a solution using Array.  
------------
If Alan or Jack, or yourself could include the data in a select string like:

Select * From tblMapPoint Order By fnSort(Fields(24))

I would be able to help you.  If there is no way to access a table like tblMapPoint, you can put the data in a temp table to work with my solution.  I know there should be a way, using TableDef or QueryDef, to do this without use of temp table, but I don't know how.  

If all fails, make a temp table using the code you have, I handle the rest.

Mike
0
 
DanielAttardAuthor Commented:
Jadedata - I've progressed half-way through the suggestions you gave me.  I first filled the array from the recordset.  Then I ordered the array.  This is all working.  I just don't know how to do the next steps you mentioned which are:

"assign the intSymbol in the array
iterate thru the rs and find the matching element in the array for the intSymbol"

Can you explain how I would do this?
0
 
DanielAttardAuthor Commented:
I think I got it figured out now, although not completely working right.  I'll post the code here later once it is working right so others can benefit
0
 
Alan WarrenApplications DeveloperCommented:
Hi Daniel,

did you get the recordset sorted in the order you wanted?

Alan
0
 
DanielAttardAuthor Commented:
I did manage to get it sorted, but I am still having difficulty dealing with duplicate values in the array.  

See http://www.experts-exchange.com/Databases/MS_Access/Q_21167748.html for a description of my current problem.  Flavo has justed posted a potential solution which I will look at now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.