Recordset does not sort when using SORT "Field" ASC

"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.

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

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

"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).

Hi,

U could also use DISTINCT in the select statement. it removes any duplicate records