VB script - Fill combo box from access db

[Powell]

Hi guys. This is my first time so here goes. I am coding forms using VB script on Outlook 2000. I am trying to populate this combobox from an access database. The problems is that the query that i am running by code is giving me dublicate results.

ex:

Data: Paul
Paul
Mark
Robert

I want to display all the names BUT i do not want to display dublicate names.

The coding i am using is this:

Public Sub FillSenderCMBSearch()   
    Dim rst         'Recordset
    Dim OBJdbConnection 'Database Connection
    Dim objInsp         'Inspector object
    Dim objApp          'Application Object
    Dim objItem             'Item Object
    Dim objNS           'Namespace Object
    Dim SendersArray
    Dim ArrayIndex
 
        'Fill Senders List
        set OBJdbConnection = CreateObject("ADODB.Connection")
        Set oPage = Item.GetInspector.ModifiedFormPages("Search")
        Set oCtrl = oPage.Controls("cmbSender")
        oCtrl.ColumnCount = 2
'       oCtrl.ColumnWidths = "1;0"
'       oCtrl.BoundColumn = 2
    
        'get length of array by counting number of records
        
        OBJdbConnection.open "Driver=Microsoft Access Driver (*.mdb); dbq= \\fms-server\scanning\E.D.M.S\Database_File\Postdb.mdb"
        set rst = CreateObject("ADODB.Recordset")
        rst.ActiveConnection = OBJdbConnection
        rst.CursorType = 2
        rst.LockType = 2
        rst.source = "SELECT Count(T_Post_Details.ContID) AS CountOfContID FROM T_Post_Details;"
        rst.open
            Redim SendersArray(CInt(rst.fields("CountOfContID")),2)
        rst.close
        OBJdbConnection.Close
        SendersArray(0,0) = ""
        SendersArray(0,1) = "----"
    
        ArrayIndex = 1
 
        OBJdbConnection.open "Driver=Microsoft Access Driver (*.mdb); dbq= \\fms-server\scanning\E.D.M.S\Database_File\Postdb.mdb"
 
        set rst = CreateObject("ADODB.Recordset")
        rst.ActiveConnection = OBJdbConnection
        rst.CursorType = 2
        rst.LockType = 2
        rst.source = "Select ContFileAs, ContID from [T_Post_Details]"
        rst.open
            do while not rst.eof
                SendersArray(ArrayIndex,0) = rst.fields("ContFileAs")
                SendersArray(ArrayIndex,1) = rst.fields("ContID")
                ArrayIndex = ArrayIndex+1
                rst.movenext
            loop
        rst.close
        OBJdbConnection.Close
        oCtrl.List() = SendersArray
    'Clearing objects to relase memory
    Set objNS = Nothing
    Set objApp = Nothing
    Set oOlFolder = Nothing
    Set oNamespace = Nothing

Edit: Added vbcode tags for clarity - Hack

[Hack]

Welcome to the Forums.

I have moved your question from the CodeBank to the VBA forum.

Thank you for joining our community.

[salvelinus]

Did you try SELECT DISTINCT?

[Powell]

In my SQL statement?

[Hack]

In my SQL statement?

Yes, SELECT DISTINCT is SQL Code, and instructs the database to return only one item if there are more then one that match the remaining SELECT criteria.

[salvelinus]

e.g., SELECT DISTINCT fldFoo FROM tblGoo.
If there are two records in tblGoo where fldFoo = "foobar", only one instance of "foobar" will be returned.
szalamny, another member, is an SQL guru, & doesn't like DISTINCT, forget why, but in a case like this it seems tailor made.

[Powell]

10x mate ill give it a try!!!!

[Powell]

It worked perfectluy guys!! thanks for yuor help and for your time.