Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Order a recordset by looping through it?

Posted on 2004-10-12
17
Medium Priority
?
1,184 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

597 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