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:



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

  • 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
    1. With .cmbLastName
    2. .Clear
    3. .BoundColumn = 3
    4. .List = Application.Transpose(vaData)
    5. .ListIndex = -1
    6. .ColumnCount = 2
    7. 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

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


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


    bye,


    Carlos