Populate a comboxbox in a userform with an Access-recordset

[XL-Dennis]

In this example I will show how we can populate a combobox in a userform with data from a database.

What we need:
* MS Windows 2000 or above
* MS Excel 2000 or above
* MDAC 2.5 or above (ADO Library)

Step 1
Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.

Step 2
Insert following code in a standardmodule:

Option Explicit 
 
Sub Populate_Combobox_Recordset() 
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stDB As String, stConn As String, stSQL As String 
    Dim xlCalc As XlCalculation 
    Dim vaData As Variant 
    Dim k As Long 
     
     'In order to increase the performance.
    With Application 
        xlCalc = .Calculation 
        .Calculation = xlCalculationManual 
        .EnableEvents = False 
        .ScreenUpdating = False 
    End With 
     
     'Instantiate the Connectionobject.
    Set cnt = New ADODB.Connection 
     
     'Path to and the name of the database.
    stDB = ThisWorkbook.Path & "\" & "Test.mdb" 
     
     'Create the connectionstring.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & stDB & ";" 
     
     'Create the SQL-statement.
    stSQL = "SELECT * FROM tblData" 
     
    With cnt 
        .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
        .Open stConn 'Open connection.
         'Instantiate the Recordsetobject and execute the SQL-state.
        Set rst = .Execute(stSQL) 
    End With 
     
    With rst 
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
        k = .Fields.Count 
         'Populate the array with the whole recordset.
        vaData = .GetRows 
    End With 
     
     'Close the connection.
    cnt.Close 
     
     'Manipulate the Combobox's properties and show the form.
    With frmData 
        With .ComboBox1 
            .Clear 
            .BoundColumn = k 
            .List = Application.Transpose(vaData) 
            .ListIndex = -1 
        End With 
        .Show vbModeless 
    End With 
     
     'Restore the settings.
    With Application 
        .Calculation = xlCalc 
        .EnableEvents = True 
        .ScreenUpdating = True 
    End With 
     
     'Release objects from memory.
    Set rst = Nothing 
    Set cnt = Nothing 
End Sub 

The most notable thing is that we need to transpose the horizontal array vaData to an vertical to add it to the combobox.

Happy dataretrieving

[calicho]

Hi Dennis,

How are you? I was wondering if you can help me out.

I used your code to populate a combobox and I made some modifications to it.

Basically, my table has 3 fields (last name, first name and customer Id). What I want is to show only the last name and first name in the combo box, then when the user selects a person, I want to use the customer ID as a value that is stored to do some other stuff. What I change was the following:

With .cmbLastName 
    .Clear 
    .BoundColumn = 3 
    .List = Application.Transpose(vaData) 
    .ListIndex = -1 
    .ColumnCount = 2 
End With 

The code works perfectly when I have 2 or more records in the table, but when I go down to 1 record, it is all screwed up...

can you please provide some guidance?

thanks a lot,

Carlos

[calicho]

Dennis,

I found the solution. There is a bug in the code you posted... if you change one line from the code, everything works fine.

 
With .cmbLastName 
    .Clear 
    .BoundColumn = 3 'this uses the CustomerID as the field to save
     ' WRONG!! -> .List = Application.Transpose(vaData)
    .column = vaData 'Use this and the problem works even with 1 record!!
    .ListIndex = -1 
    .ColumnCount = 2 'this makes appear the two columns on the combobox
End With 

that way, the code can read even when there is 1 record...

bye,

Carlos

[johnny44]

Would it be possible to set the list box headins with the field headings from the query ?

[Dave Hawley]

Members, please keep all question in the question forum only. Thanks.