How do i fill Combobox using ADO from access database

How do i fill Combobox usiing ADO from access database

When i will select the the particular value from combo box corresponding value (Multicolumn )against that column should be populates

thanks :eek2:

Hi!!!

How do i fill Combobox usiing ADO from access databa

If your using the ado data control (adodc), specifiy the data source to point to the ado data control and simply change the DataField property to select to a particular field..

Or you may use the ADODB.Connection and a recordset to populate the combo box...What I mean is you have code this and use the AddItem method to populate the combo box...

Example:

VB Code:

cboSupplier.AddItem(rstSupplier.Fields("Supplier_Name"))

:) :) :)

There are a lot of examples, tutorials on the web how to do that.

VB Code:

Option Explicit
 
Dim cn As ADODB.Connection
Dim rsPlaats As ADODB.Recordset

VB Code:

Private Sub Form_Load()
On Error GoTo ErrHandler
 
    Set cn = New ADODB.Connection
        cn.Provider = "Microsoft.Jet.OLEDB.4.0"
        cn.ConnectionString = "Data Source = C:\Test.mdb;" & "Persist Security Info = False"
        cn.Open
 
Set rsPlaats = New ADODB.Recordset
        rsPlaats.Open "SELECT DISTINCT Fieldnumber from NameDatabase", cn, adOpenForwardOnly, adLockReadOnly
 
Do Until rsPlaats.EOF
            Combo1.AddItem (rsPlaats!Fieldnumber)
            rsPlaats.MoveNext
        Loop
 
        rsPlaats.Close
    Set rsPlaats = Nothing
Exit Sub
 
ErrHandler:
    MsgBox Err.Description
End Sub

VB Code:

Private Sub Combo1_Click()
On Error Resume Next
 
    Set rsPlaats = New Recordset
        rsPlaats.Open "SELECT * FROM NameDatabase WHERE Fieldnumer = '" & Combo1.Text & "'", cn, adOpenForwardOnly, adLockReadOnly
 
If Not rsPlaats.EOF And Not rsPlaats.BOF Then Text1.Text = rsPlaats.Fields("Field2").Value
        If Not rsPlaats.EOF And Not rsPlaats.BOF Then Text2.Text = rsPlaats.Fields("Field3").Value
 
        rsPlaats.Close
    Set rsPlaats = Nothing
End Sub

This is just a very simple example. Hope you can use it.

Here's my function...

VB Code:

Public Sub PopulateData(objObject As Object, ByVal pSQL As String, ByVal FieldName As String, Optional ByVal WithNull As Boolean = True)
    objObject.Clear
    Dim rsRecords   As ADODB.Recordset
    Set rsRecords = New ADODB.Recordset
    With rsRecords
        .Open pSQL, connImport, adOpenForwardOnly, adLockReadOnly
        If Not (.EOF And .BOF) Then
            Do While Not .EOF
                objObject.AddItem .Fields(FieldName)
                .MoveNext
            Loop
        End If
        If WithNull = True Then
            objObject.AddItem vbNullString
        End If
        .Close
    End With
    Set rsRecords = Nothing
End Sub

Quote:

---

Originally Posted by vaishali

When i will select the the particular value from combo box corresponding value (Multicolumn )against that column should be populates

thanks :eek2:

---

:confused: Multicolumn combo box?

I think he means a listbox or somesort of control. ;)

hi,
I am using adodb.connection. I am able to fill the combo with the data.
My Query:
I have State_Code,State_Name.
In the combo, i want to display state_name and store the state_code of the selected state_name in the combo because only showing the state_code will not have a meaning.
pl suggest the best way.

regards

I think that you need a grid for that, 'cause you have to Fields. One for the State_Name and one for the State_Code. Unless you know how to add a new row in a Listbox. Do not use a Combobox for this problem. Well..... that wouldn't be my solution.

If the State_Code is numeric, you can put it into the hidden "ItemData" property of the combo, which works in the same way as the "List" property.

To add ItemData (associated with the latest 'AddItem'), you can use this:

Code:

---

With combo1
  .AddItem "Number 1"
  .ItemData(.NewIndex) = 1
End With

---

..and to read it back again you can use this:

Code:

---

With combo1
  MsgBox "List data: " & .List(.ListIndex)
  MsgBox "ItemData: " & .ItemData(.ListIndex)
End With

---

Any idea how to do this for .accdb database? Access 2010...

It doesn't matter if the database is .mdb or .accdb , the methods are the same... assuming of course that you are using the same language.

However, based on your thread inserting data in combo box I see that you are using a different language to that above. While VBA is similar to VB6 in many cases, controls are one of the areas where things are different, so the same code is unlikely to work.

If the VB6 code above doesn't work for you then the best thing to do is ask in the Office Development (VBA) forum, as you are already doing.