vba excel combobox loading values from query ms access

  • Hi everyone!


    I need your help again. Unfortunately I was not able to find what I was looking for on the internet.


    I would like to populate an activex object (Combobox) with values from a table in Access...


    For example: I have a database "test.accdb" with one table [Names] and one field (First_Name) and I want to connect the data from the database with excel.


    For retrieving data from Access to Excel I already found a nice macro. BUT I cannot populate my combobox in Excel from the data in Access..


    The idea is to click the combobox and the macro retrieve everytime the data from ms access.


    The code to retrieve data from access and paste it in excel:



    Can you help me with this?


    Regards and thanks again

  • Re: vba excel combobox loading values from query ms access


    Try adding a dynamic named range on the sheet where the RecordSet is placed.


    The formula for the new named range, assuming that Cell A1 contains a header value, should be:


    =OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1)


    Name the new defined range "FirstNames"


    Then place this in the worksheet Object Module for the sheet which contains the ActiveX ComboBox


    Code
    Private Sub ComboBox1_DropButtonClick()
        ComboBox1.ListFillRange = "FirstNames"
    End Sub


    Change "ComboBox1" in that code to whatever your ComboBox is named (if not "ComboBox1").

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba excel combobox loading values from query ms access


    Thanks KjBox for your comment. Unfortunately I didn't explain myself well enough.
    The code above retrieve the data direct from access and paste it in excel as in the code.
    What I am trying to achieve is to fill the combo box directly from access by using the logic of
    my code above (db connection, SQL query, MoveLast, EOF, etc)


    Thanks anyway. I still learned a lot from your approach.


    Does someone have an idea how to populate the combo box directly from access on a change event?

  • Re: vba excel combobox loading values from query ms access


    Try replacing

    Code
    xlSheet.Range("A2").CopyFromRecordset rs


    with

    Code
    FirstNames = rs.GetRows
    For i = 1 To UBound(FirstNames, 1)
        Sheets("Name of your sheet that has the ComboBox").OLEObjects("ComboBox1").Object.List.AddItem FirstNames(i, 1)
    Next


    Off the top of my head I am not sure whether a single column of data in a RecordSet will result in a 1 or 2 dimensional array, so if the above fails try this

    Code
    FirstNames = rs.GetRows
    For i = 0 To UBound(FirstNames)
        Sheets("Name of your sheet that has the ComboBox").OLEObjects("ComboBox1").Object.List.AddItem FirstNames(i)
    Next


    Again, change "ComboBox1" if your ComboBox is named something else, and enter the correct sheet name (not sure where this code is situated so just "ActiveSheet" may be sufficient).

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba excel combobox loading values from query ms access


    Hi KjBox,
    thanks again. I just tried both versions but it is still not working, unfortunately... UBound(Firstnames) = 0. It does not even start the loop.
    But I feel it, it is getting closer.

  • Re: vba excel combobox loading values from query ms access


    hmm, I will look at this again in the morning (11:20PM here and I am calling it a day!)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba excel combobox loading values from query ms access


    Hi KjBox,


    I found this but I don't exactly where to put it... should I put it after the Else statement or after...?


    Please take a look and let me know, if you can.



    Is there something like ComboBox onClick event? The idea is not to call a Sub procedure to get the combobox to fill the names, but just by clicking on the combobox... like an event change.


    Greetings!

  • Re: vba excel combobox loading values from query ms access


    This is weird... if query only has one value then the expression below is true... and the code doesn't run anymore.

    Code
    Do Until rs.EOF


    Why?

  • Re: vba excel combobox loading values from query ms access


    Put this in the Worksheet Object Module of the sheet that has the ComboBox.

    Code
    Private Sub ComboBox1_DropButtonClick()
        'your code to get the first names and load them into the combobox here
    End Sub


    When the little down arrow on the combobox is clicked the list of first names will load and be displayed.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba excel combobox loading values from query ms access


    Thanks again...


    Our code is now below... unfortunately I am having the problem that combobox2 is not working because combobox1 is now filled with only one value.
    "Do Until rs.EOF" is in this case TRUE... It seems that one recordset (from the previous combobox1) is not enough...


    Ideas of how to solve this issue ?????





  • Re: vba excel combobox loading values from query ms access


    When change the "Do Until rs.EOF" for this:

    Code
    Do While not rs.EOF


    the code runs and stops here...


    Code
    .List(.ListCount - 1, i) = rs.Fields(i)


    This is really weird.

  • Re: vba excel combobox loading values from query ms access


    Why not use a single combobox that has 2 columns, first column for First Name and Second Column for Last Name?


    Or maybe first column for Surname and second column for First Name might be better.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba excel combobox loading values from query ms access


    Hi KjBox,


    I could do that; however, I would rather do it separately... I am still trying to understand why the line of code below is not working... :smash:

    Code
    .List(.ListCount - 1, i) = rs.Fields(i)
  • Re: vba excel combobox loading values from query ms access


    Hi KjBox,


    I could do that; however, I would rather do it separately... I am still trying to understand why the line of code below is not working...

    Code
    .List(.ListCount - 1, i) = rs.Fields(i)
  • Re: vba excel combobox loading values from query ms access


    Finally........ it is not pretty but it works, for now :music:


    Thank you all!



  • Re: vba excel combobox loading values from query ms access


    Well done :)


    Thanks for sharing the final solution.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba excel combobox loading values from query ms access


    It seemed good, almost too good. :hammerhe:


    Unfortunately, the code has a big bug!!!!



    These lines

    Code
    For i = 0 To UBound(FData)
                        .List(.ListCount - 1, i) = rs.Fields(i)
                    Next
                    End With
                    rs.MoveNext
                Loop


    are forgetting the one value from this line....


    Code
    ComboBox2.Value = FData(0, 0)



    So at the end, if there are more than one recordset, then I get all of them minus 1 (n-1)... which is not good at all!!!!


    If anyone has an idea how to fix this, I will be really thankful!


    Thanks again.

  • Re: vba excel combobox loading values from query ms access


    I am not sure where the variable "FData" comes from, are you using that instead of "LastNames"? If so then why do still have "Ubound(LastNames)" in the code?


    Instead of

    Code
    Do Until rs.EOF
        With ComboBox2
            .AddItem
            For i = 0 To UBound(LastNames)
                .List(.ListCount - 1, i) = rs.Fields(i)
            Next
        End With
        rs.MoveNext
    Loop


    try

    Code
    For i = 0 To UBound(LastNames)
        ComboBox1.AddItem LastNames(i)
    Next


    In fact you should be able to use

    Code
    For i = 0 To rs.Count
        ComboBox1.AddItem rs.Fields(i)
    Next


    and do away with the FirstNames array altogether, a RecordSet is, itself, an array, so you should be able to use it directly.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vba excel combobox loading values from query ms access


    Sorry, Fdata was just a test... I am going to try with your code...Thanks again!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!