Recordset does not sort when using SORT "Field" ASC

I have created a summary recordset by combining data from multiple SQL tables. This bit of code takes this recordset and uses it to populate a dropdown box for the user to select the material they want to plan.

The Sort command is commented out as it causes and error saying this function cannot be used here

The display I get ,without the sort , shows mutiple records for the same material types and I want it to have just one entry for each qualifying record.

I hope you can help

Code is:-

'Call function to populate material selection combo.
MaterialsNeedingPlanning


'Populate the material selection combo box
With rsNetCOReqmts
    If .State = adStateClosed Then .Open
    If .RecordCount > 0 Then
        '.Sort = "Material ASC"
        .MoveFirst
        Do While Not .EOF
            strMatl = !Material
            If strMatl <> strLastMatl And strMatl <> "" Then
                cmbMaterialSelected.AddItem .Fields("Material")
                strLastMatl = strMatl
            End If
            .MoveNext
        Loop
        .Close
    Else
        MsgBox ("There are no items needing planning at this time")
    End If
End With


Many thanks
Will
WilliamReidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RogueSolutionsCommented:
The ComboBox has it's own Sort property and since you are populating the box with Material (which is your sort criteria) it should achieve the same ends.

The alternative would be to add an ORDER BY Material ASC to the SQL statement (not seen in your code)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jpontaniCommented:
"The display I get ,without the sort , shows mutiple records for the same material types and I want it to have just one entry for each qualifying record."

If I understand this correctly, you need a GROUP BY clause in your SQL statement.  If you do "SELECT Material FROM Table GROUP BY Material"
0
RogueSolutionsCommented:
"The display I get ,without the sort , shows mutiple records for the same material types and I want it to have just one entry for each qualifying record."

Sorry, missed that part of your question.  Doh

jpontani's solution is definitely better than mine.  Your code won't need to check strLastMat then either as they'll be unique.

Either the ComboBox's property or the ORDER BY clause will then sort the materials alphabetically.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

WilliamReidAuthor Commented:
Hi,

Thanks for your replies. I didn't make it clear that, although I start by reading SQL tables, at this point in the code I am dealing with disconnected recordsets and these SQL methods don't seem to apply or I haven't been able to make them work. The only option from the combo properties is .sorted and this just checks to see if they are sorted, it does not sort them.

the recordset

With rsNetCOReqmts has been built up is the previous function by appening the results of several queries to it. This is the final stage. Each on the append queries could have added a number of Materials that qualify. I now need to remove any repeated materials and present them in ASC order.

I hope that makes things a bit clearer

Will
0
RogueSolutionsCommented:

William

The .sorted help for my combobox (in VB6) says it does sort the entries as they are added.  

It says "Returns a value indicating whether the elements of a control are automatically sorted alphabetically."

The automatically bit is what makes the combo do the sorting.

---

To de-dup the list of materials check the combo through each time looking for a match (I am assuming .sorted property is true).  If you don't find a match, or the item being checked is higher alphabetically we know we need to add it.

'Populate the material selection combo box
With rsNetCOReqmts
    If .State = adStateClosed Then .Open
    If .RecordCount > 0 Then
        '.Sort = "Material ASC"
        .MoveFirst
        Do While Not .EOF
            strMatl = !Material

            bFound = False
            For intFound = 0 To cmbMaterialSelected.ListCount - 1
                If LCase(cmbMaterialsSelected.List(intFound)) = LCase(strMatl) Then
                    bFound = True
                    Exit For
                ElseIf LCase(cmbMaterialsSelected.List(intFound)) > LCase(strMatl) Then
                    Exit For
                End If
            Next
            If Not bFound Then
                cmbMaterialsSelected.AddItem strMatl
            End If
           
            .MoveNext
        Loop
        .Close

    Else
        MsgBox ("There are no items needing planning at this time")
    End If
End With
0
jpontaniCommented:
"With rsNetCOReqmts has been built up is the previous function by appening the results of several queries to it. This is the final stage. Each on the append queries could have added a number of Materials that qualify. I now need to remove any repeated materials and present them in ASC order."

That's what I was pointing at earlier.  In your initial SQL query, you might have something similar to:

SELECT * FROM (select stuff), (select stuff) GROUP BY Material ORDER BY Material ASC

The ending 2 clauses will group your results by Material, and order them alphabetically.  After this you shouldn't have any problem adding them to the combobox alphabetically as long as you read your recordset sequentially (starting at record 1 and going down the list).
0
atheekurrahmanCommented:
Hi,

U could also use DISTINCT in the select statement. it removes any duplicate records
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.