Combobox with database values

[antoniomiranda]

Hello,

I've one table with the fields DISTRICT, where this field have many duplicated items.

How can i send only one copy of each item to one combobox.

Best regards,
António Miranda.

 

[ZmrAbdulla]

Hi,
Add a word "Distinct" after the "Select" in ComboRowsource
something like
SELECT DistinctTblDistrict.DistName
FROM TblDistrict;
regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[ZmrAbdulla]

Hi,
Add a word "Distinct" after the "Select" in ComboRowsource
something like
SELECT Distinct TblDistrict.DistName
FROM TblDistrict;
there was samll typo in the first post
regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[antoniomiranda]

I don't understand.

 

[jajinder]

What is the code u use to import the table?
I use an AccesDatabase and import the DB with the following code:

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"  'Blad 1 = SourcePage of the Acces-DB
        .Open
    End With

    [b]Dim tTable[/b]

    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            [b]If rs!plaats <> tTable Then[/b]
                Combo1.AddItem rs!Place
                Combo2.AddItem rs!ShortCut
                [b]tTable = rs!Place[/b]
            End If
            rs.MoveNext
        Loop
    End If

    rs.Close
    Set rs = Nothing
End Sub

The Bold text shows how to hide the duplicated records. It worx fine with me.

---------------------------------------
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]

If you use the same code try this:

If rs!plaats <> tTable Then
      Combo1.AddItem rs!DISTRICT
   tTable = rs!Place

---------------------------------------
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]

I see I made a few mistakes... oké... Í realy am a newbee:

Your code would be:

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 = C:text.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"  'Blad 1 = SourcePage of the Acces-DB
        .Open
    End With

    [b]Dim tTable[/b]

    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            [b]If rs!DISTRICT <> tTable Then[/b]
                Combo1.AddItem [b]rs!DISTRICT[/b]
                [b]tTable = rs!DISTRICT[/b]
            End If
            rs.MoveNext
        Loop
    End If

    rs.Close
    Set rs = Nothing
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

 

[jajinder]

Forgot the rest.. oops:

Private Sub Combo1_Click()

    Set rs = New ADODB.Recordset

    rs.Open "SELECT * FROM Blad1 WHERE DISTRICT = '" & 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!...'enter fieldname
                'Text1.AddItem rs!...'enter fieldname
                'Label1.AddItem rs!...'enter fieldname
            End If
            rs.MoveNext
        Loop
    End If
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

 

[ZmrAbdulla]

Sorry for that. I was thinking that I am replying to MS Access forum


Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[jajinder]

:lol:

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