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:

Const Pathname As String = "C:\Users\Susy\Desktop\test.accdb"
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    Dim rCount As Long
    Dim SQL As String
 




    Set xlBook = ActiveWorkbook
    Set xlSheet = xlBook.Worksheets(1)
 
    xlSheet.Range("A2:A10000").ClearContents
   
 
    Set db = OpenDatabase(Pathname)
   
    SQL = "SELECT DISTINCT First_Name "
    SQL = SQL & "FROM Names "
        
    Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
    
 
    
    If rs.RecordCount = 0 Then
        MsgBox "No data"
       
    Else
        rs.MoveLast
        rCount = rs.RecordCount
        rs.MoveFirst
    End If
   
    xlSheet.Range("A2").CopyFromRecordset rs

Can you help me with this?

Regards and thanks again

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

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

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.

FirstNames = rs.GetRows()   
    ComboBox1.Clear
    
    Do Until rs.EOF
        With ComboBox1
            .AddItem
            For i = 0 To UBound(FirstNames)
                .List(.ListCount - 1, i) = rs.Fields(i)
            Next
        End With
        rs.MoveNext
    Loop

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.

Do Until rs.EOF

Why?

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 ?????

Sub cbFirstNames()


    Const DbLoc As String = "C:\Users\Susy\Desktop\test.accdb"
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    Dim recCount As Long
    Dim SQL As String
    Dim FirstNames As Variant




    
    Set xlBook = ActiveWorkbook
    Set xlSheet = xlBook.Worksheets(1)
   
    Set db = OpenDatabase(DbLoc)




    SQL = "SELECT DISTINCT First_Name "
    SQL = SQL & "FROM Names "
    
    Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)




    If rs.RecordCount = 0 Then
        MsgBox "No data retrieved from enet"
        
    Else








    Firstnames = rs.GetRows()




    ComboBox1.Clear




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




    End If
   
rs.Close
Set rs = Nothing




End Sub








Sub cbLastNames()








On Error GoTo SubError
    Const DbLoc As String = "C:\Users\Susy\Desktop\test.accdb"
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    Dim recCount As Long
    Dim SQL As String
    Dim LastNames As Variant




    
    Set xlBook = ActiveWorkbook
    Set xlSheet = xlBook.Worksheets(1)




   
    Application.StatusBar = "Connecting to an external database..."
    Application.Cursor = xlWait




    Set db = OpenDatabase(DbLoc)




    SQL = "SELECT DISTINCT Last_Names "
    SQL = SQL & "FROM Names "
    SQL = SQL & "WHERE First_Name = " & ComboBox1.Value & " "
    
    Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
   
    If rs.RecordCount = 0 Then
        MsgBox " Combobox with First Names is empty"
        
    Else




    LastNames = rs.GetRows()




ComboBox2.Clear




    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




    End If








rs.Close
Set rs = Nothing




End Sub

Re: vba excel combobox loading values from query ms access

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

Do While not rs.EOF

the code runs and stops here...

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

This is really weird.

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:

.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...

.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!

If rs.RecordCount = 0 Then
        MsgBox "Please Select a First Name" 
        GoTo SubExit


    Else


        LastNames = rs.GetRows()


        ComboBox2.Clear


        If rs.RecordCount = 1 Then
    
            ComboBox2.Value = LastNames(0, 0)
        Else
    
            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
        End If
    End If

Re: vba excel combobox loading values from query ms access

You are welcome KjBox.

This way we can all learn!

:dance:

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

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....

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

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