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 = NothingEdit: 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!!!! :afrog:

[Powell]

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