Loading access data in combo box vba

[nn992]

Hello guys,
I would like to know if it is possible load data from access into combo box?

I have bunch of columns in access, each of them is one word, and they repeat, so I would like to include e.g. 20 most frequent ones in combo box - to get something like a drop down menu?

Do you understand my issue, or I would specify it more?

thanks in advance

I wrote this code below, but it is still returning me the empty combo box, without even giving me an error...

"Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Databases\StaffDatabase.mdb"
rst.Open "SELECT DISTINCT [Department] FROM tblStaff ORDER BY [Department];", _
cnn, adOpenStatic
rst.MoveFirst
With Me.ComboBox1
.Clear
Do
.AddItem rst![Department]
rst.MoveNext
Loop Until rst.EOF
End With
UserForm_Initialize_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub"

 

[alansidman]

crosspost: https://www.excelforum.com/excel-pr...554-loading-access-data-in-combo-box-vba.html

 

[VBA Geek]

What is in your recordset after you execute the query?

should not your query be?

SELECT TOP 20 tblStaff.Department FROM tblStaff GROUP BY tblStaff.Department ORDER BY Count(tblStaff.Department) DESC

 

[Joe4]

crosspost: https://www.excelforum.com/excel-pro...o-box-vba.html

Note that while we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[nn992]

Hi, thanks a lot for very fast answer...
I realized that my code is OK, there is some other issue, even if I give him non-existing path to the database file, software opens me the empty combo box as usual???!!! How is this possible? VBA is not even considering my code...

I tried to enable all necessary items in references tab... Anyone with idea?

also: I am using .accdb file, it is a slight change in the code. now it is:

Private Sub ComboBox1_Change()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

On Error GoTo UserForm_Initialize_Err
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\XXX\desktop\XXX.accdb"
rst.Open "SELECT DISTINCT nikola FROM nikola ORDER BY nikola ;", cnn, adOpenStatic

sSql = "SELECT DISTINCT nikola FROM nikola ORDER BY nikola ;"
With rst
    Set .ActiveConnection = cnn
    .CursorLocation = adUseClient
    .Open sSql
End With


With Me.ComboBox1
    .Clear

    While Not rst.EOF
        .AddItem rst.Fields("nikola").Value
        rst.MoveNext
    Wend
End With

UserForm_Initialize_Exit:
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub

UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub