Remove or hide double items in listbox 1

[HydraNL]

Hi,

Got a small problem (I hope)

Can Anyone tell me how to remove or hide double items from a listbox?

This is what I already have:

Private Sub Combo1_Click()
    List1.Clear

    Set rs = New Recordset

    rs.Open "SELECT DISTINCT * FROM POSTCODE WHERE GEMEENTE = '" & Combo1.Text & "'", cn, adOpenDynamic

    Do Until rs.EOF
        List1.AddItem rs!Plaats  '<----
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
End Sub

Greetings J. Radjesh Klauke (Netherlands)

 

[gmmastros]

You select distinct is probably not working the way expect it to because of other columns that are being returned.

Change

SELECT DISTINCT * FROM POSTCODE

To

SELECT DISTINCT [red]Plaats[/red] FROM POSTCODE

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[HydraNL]

Didn't work. But I think I gave to little info.
There are also 2 textboxes filled with data from the recordset.

This is the full code:

Private Sub Combo1_Click()

    List1.Clear

    Set rs = New Recordset

    rs.Open "SELECT * FROM POSTCODE WHERE GEMEENTE = '" & Combo1.Text & "'", cn, adOpenDynamic

    If Not rs.EOF And Not rs.BOF Then Text1.Text = rs.Fields("BBRNR").Value
    If Not rs.EOF And Not rs.BOF Then Text2.Text = rs.Fields("GSDNR").Value

    List1.SetFocus

    If rs.EOF Or rs.BOF Then
        If Combo1.Text = "" Then
            Exit Sub
        End If

        If MsgBox("Cannot find the city you entered." & vbNewLine & _
            "Try again please.", _
            vbOKOnly + vbInformation, "Ongeldige plaatsnaam") = vbOK Then
            Combo1.Text = ""
            Combo1.SetFocus
        End If
    End If

    Do Until rs.EOF
        List1.AddItem rs!Plaats  '<--
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
End Sub

Greetings J. Radjesh Klauke (Netherlands)

 

[ClulessChris]

I would think the way to go would be to adapt your SELECT DISTINCT stament to pull only the data you require, however a bit of a fiddle would be to create a string variable and fill it with a CSV style string of all rs!Plaats. then check this string with instr prior to adding to the list.

good luck

Everybody body is somebodys Nutter.

 

[gmmastros]

Can you sort the recordset on Plaats?

rs.Open "SELECT * FROM POSTCODE WHERE GEMEENTE = '" & Combo1.Text & "' [red]Order By Plaats[/red]", cn, adOpenDynamic

You can add a temp variable to your code...

Dim strPlaats as String

Then...

    Do Until rs.EOF
        If strPlaats <> RS!Plaats Then
          List1.AddItem rs!Plaats  '<--
        End If
        strPlaats = RS!Plaats
        rs.MoveNext
    Loop

This will only check for duplicate Plaats if they appear on consecutive rows. This method will only work if you can sort the recordset on the Plaats field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[HydraNL]

THANK YOU!!!!! Have a shiny. So simple, yet so elegant.

Greetings J. Radjesh Klauke (Netherlands)

 

[gmmastros]

> So simple, yet so elegant.

That's my motto. Glad it's working for you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom