[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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
0
WilliamReid
Asked:
WilliamReid
2 Solutions
 
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
 
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
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.

 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now