Hide duplicated records Combo

[jajinder]

Hi,
How do I hide duplicated records in a ComboBox?

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King

 

[HarleyQuinn]

Hi,

How is the combobox being populated??

Is it from a recordset or from user input etc.??

Harleyquinn

 

[jajinder]

Sorry... You are right...

Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
    
    Set cn = New ADODB.Connection
    With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = _
            "Data Source = U:\AFD. FINANCIERING\R.A.D. 2005\Collega.mdb;" _
                & "Persist Security Info = False"
        .Open
    End With

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseServer
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Source = "Blad1"
        .Open
    End With

    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            Combo1.AddItem rs!plaats
            Combo2.AddItem rs!afkorting
            rs.MoveNext
        Loop
    End If

    rs.Close
    Set rs = Nothing
End Sub

Private Sub Combo1_Click()
List1.Clear
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM Blad1 WHERE Plaats = '" & Combo1.text & "'", cn, adOpenDynamic
End Sub

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King

 

[HarleyQuinn]

Hi,

What records does your recordset return??

Do you need to add two items to the combobox for each reocord could you combine the the columns into one??

Harleyquinn

 

[HarleyQuinn]

Hi,

I have noticed that in Combo1_Click your select only looks for the Plaats column in the database. However you have added data form the plaats AND afkorting into the combo box.

If it only needs the plaats in the combo you could try using a query as the recordset to populate it. Something along the lines of:

"SELECT DISTINCT PLAAT FROM BLAD1"

As the query should work.

That will only show you one instance of each plaat value.

Hope this is a help.

Harleyquinn

 

[HarleyQuinn]

Hi,

Sorry, got all confused there I had not seen that you were populating a second combobox. Doh!

You can still use the suggestion as above but change the query to read:

"SELECT DISTINCT PLAAT, AFKORTING FROM BLAD1"

Again, sorry about my original oversight.

Harleyquinn

 

[jajinder]

That's ok.... :lol:
I will trie te get it right. Tanx for the info so far.

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King

 

[jajinder]

Stil not working... (8S)

rs.Open "SELECT * FROM Blad1 WHERE Plaats = '" & Combo1.text & "'", cn, adOpenDynamic

The Combo1 fills with records from the database from the table "PLAATS". Combo2 is not relevant.

I tried to create: rs.Open "SELECT DISTINCT PLAAT FROM BLAD1" But Then I get a fault. Or don't I get it.

Sorry for al the questions. Newbee.

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King

 

[HarleyQuinn]

Hi,

Instead of DISTINCT you could use GROUP BY.

Try something like:

"SELECT PLAAT FROM BLAD1 GROUP BY PLAAT"

That should work and have the same effect i.e. returning unique values.

Hope this helps.

Harleyquinn

 

[ts2032]

You could also order the query by plaats that way all the duplicate plaats would be together.

"Select PLAATS, AFKORTING FROM BLAD1 where PLAATS = '"& combo1.text & "' order by PLAATS"

then in your do loop check if it is the same PLAATS and if it is ignore it.

dim tPlaat as (whatever)
If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst
Do While Not rs.EOF
if rs!plaats <> tplaat then
Combo1.AddItem rs!plaats
Combo2.AddItem rs!afkorting
tplaat=rs!plaats
end if
rs.MoveNext
Loop
End If

tsmith

 

[jajinder]

Ok.... I tried a lot of things. Combined your codes.. But It did not work.
The code I got after a long time is:

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()

    Set cn = New ADODB.Connection
    With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = _
            "Data Source = U:\AFD. FINANCIERING\R.A.D. 2005\Collega.mdb;" _
                & "Persist Security Info = False"
        .Open
    End With

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseServer
        .CursorType = adOpenDynamic
        .LockType = adLockReadOnly
        .Source = "Blad1"
        .Open
    End With

    Dim Tplaats   '<-----

    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            If rs!plaats <> Tplaats Then  '<-----
                Combo1.AddItem rs!plaats
                Combo2.AddItem rs!afkorting
                Tplaats = rs!plaats  '<-----
            End If
            rs.MoveNext
        Loop
    End If

    rs.Close
    Set rs = Nothing
End Sub

Private Sub Combo1_Click()

    List1.Clear

    Set rs = New ADODB.Recordset

    rs.Open "SELECT * FROM Blad1 WHERE Plaats = '" & Combo1.text & "'", cn, adOpenDynamic
    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            If rs!plaats = Combo1.text Then
                List1.AddItem rs!naam
            End If
            rs.MoveNext
        Loop
    End If
End Sub

It worx now very fine. Many thanks everyone, for sharing the information. Seems I have to learn much and much more.

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King