Solved

Order a recordset by looping through it?

Posted on 2004-10-12
17
1,159 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:DanielAttard
  • 7
  • 3
  • 3
  • +2
17 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 12293359
Hi DanielAttard,
  have you tried

 objRS.orderby = "fieldname1, fieldname2"

regards
:)-j-
0
 

Author Comment

by:DanielAttard
ID: 12293431
Thanks for the input, but when I try that I get:

"Method or data member not found" on the OrderBy
0
 

Expert Comment

by:ee_ai_construct
ID: 12293512
{{post to wrong question removed - ai, cs admin}}
0
 

Author Comment

by:DanielAttard
ID: 12293601
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
 
LVL 32

Accepted Solution

by:
jadedata earned 500 total points
ID: 12293661
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
 

Author Comment

by:DanielAttard
ID: 12293919
Thanks jadedata.  I'll give it a shot and post back once i've got it figured out.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 12294009
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 12294023
correction...

Select * From tblMapPoint Order By fnSort(Fields(24))
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Expert Comment

by:Alan Warren
ID: 12294297
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12294325
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
 

Author Comment

by:DanielAttard
ID: 12297361
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 12301641
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
 

Author Comment

by:DanielAttard
ID: 12301887
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
 

Author Comment

by:DanielAttard
ID: 12303229
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12304136
Hi Daniel,

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

Alan
0
 

Author Comment

by:DanielAttard
ID: 12308409
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now