Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

population combo with database values.

Posted on 2006-04-26
4
Medium Priority
?
259 Views
Last Modified: 2010-05-01
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.
0
Comment
Question by:Aiysha
  • 2
4 Comments
 
LVL 4

Assisted Solution

by:g_johnson
g_johnson earned 1000 total points
ID: 16546281
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
 
LVL 4

Expert Comment

by:g_johnson
ID: 16546304
cbo1.additem "whatever" is the correct syntax
0
 
LVL 8

Accepted Solution

by:
Leo Eikelman earned 1000 total points
ID: 16546305
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
 

Author Comment

by:Aiysha
ID: 16546556

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question