population combo with database values.

HOw can I populate a combobox in vb with data from a field in a database. The query I want to use is "Select distinct("Field1") from dbTable"

I want to know how to open the database and get the data into combo.

Thank you.
AiyshaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

g_johnsonCommented:
dim c1 as adodb.connection
dim rs1 as adodb.recordset

set c1 = new adodb.connection
c1.connectionstring = "whatever"
c1.open

set rs1 = new adodb.recordset
set rs1.activeconnection = c1

rs1.open "SELECT DISTINCT(field_1) [f] FROM table ORDER BY field_1

do while not rs1.eof
   cbo1.additem rs1.fields("f").value  <-------- can't remeber the combox add method and don't have a project open right now, but i'll get it to you

   rs1.movenext
loop

rs1.close
set rs1.activeconnection = nothing
set rs1 = nothing
c1.close
set c1  = nothing
0
g_johnsonCommented:
cbo1.additem "whatever" is the correct syntax
0
Leo EikelmanDirector, IT and Business DevelopmentCommented:
Below code will do what u want.  Don't forget to put in the user name and password and paths, etc.. to match your database.

Option Explicit

Public Conn1         As ADODB.Connection
Public Rs1           As New ADODB.Recordset
Dim SQLStmt          As String
Dim Cmd1             As ADODB.Command

Sub Form_load()


'    #### OPEN CONNECTION TO DATABASE ## REMEMBER TO PUT IN YOUR NAMES##
       AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & "Dbq=DATABASE NAME;" & "DefaultDir=" & App.Path & ";Uid=;Pwd=;"
       
       Conn1.ConnectionString = AccessConnect
       Conn1.Mode = adModeReadWrite
       Conn1.IsolationLevel = adXactCursorStability
       Conn1.CursorLocation = adUseClient
       Conn1.Open

'   Create the ADO Command object and link to the active connection

    Set Cmd1 = New Command
    Cmd1.ActiveConnection = Conn1
   
    SQLStmt = "SELECT Distinct Field FROM dbTable"
   
    Cmd1.CommandText = SQLStmt
    Set Rs1 = Cmd1.Execute
   
'    Populate Combo Box
   
    Do While Not Rs1.EOF
            Combo1.AddItem Rs1("Field")
            Rs1.MoveNext
    Loop
   
End Sub


NB REMEMBER to include MICROSOFT ACTIVEX DATAOBJECTS 2.n LIBRARY in your references.


Cheers,

Leo
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AiyshaAuthor Commented:

The following worked perfect. I still appreciate your input.


Dim conn
Dim rec


Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database.mdb;Persist Security Info=False"
esql = "select Distinct STATE from Dataset"
rec.Open (esql), conn, adOpenStatic, adLockReadOnly
If rec.RecordCount > 0 Then
   rec.MoveFirst
    Do Until rec.EOF
        FacilitySelection.CmbState.AddItem rec("STATE")
        rec.MoveNext
    Loop
End If
conn.Close
Set conn = Nothing
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.