Solved

Order a recordset by looping through it?

Posted on 2004-10-12
17
1,160 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

22 Experts available now in Live!

Get 1:1 Help Now