Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Order a recordset by looping through it?

Posted on 2004-10-12
17
Medium Priority
?
1,187 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
16 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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 2000 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 34

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 34

Expert Comment

by:Mike Eghtebas
ID: 12294023
correction...

Select * From tblMapPoint Order By fnSort(Fields(24))
0
 
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 34

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

581 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