Announcement

Collapse
No announcement yet.

Populate a comboxbox in a userform with an Access-recordset

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Populate a comboxbox in a userform with an Access-recordset

    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:

    Code:
    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
    Last edited by Craig Ottley; June 9th, 2011, 08:08.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  • #2
    Re: Populate a comboxbox in a userform with an Access-recordset

    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:

    Code:
         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
    Last edited by royUK; February 7th, 2006, 17:17. Reason: add code tags, please use when posting codes & change title

    Comment


    • #3
      Re: Populate a comboxbox in a userform with an Access-recordset

      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.

      Code:
       
      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

      Comment


      • #4
        Re: Populate a comboxbox in a userform with an Access-recordset

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

        Comment


        • #5
          Re: Populate a comboxbox in a userform with an Access-recordset

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

          Comment

          Working...
          X