Hello guys,
I would like to know if it is possible load data from access into ccombo 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?
I use the DAO, and you may be using the ADO or some other method. And I tend to do it in two steps. Also, I have several tables in my database with a single "Name" field in it. These are to be loaded into ComboBoxes. Here are the two procedures I use. Maybe they'll give you some guidance on getting this done the way you'd like:
Code:
Public Sub LoadRecordsetNameIntoStringArray(rsName As String, s() As String)
' [Name] is the field loaded. dbTheDatabase is the database used.
Dim l As Long
Dim k As Long
Dim rs As DAO.Recordset
'
OpenTable rs, rsName
If Not bHasRecords(rs) Then
ReDim s(1 To 1)
rs.Close
Exit Sub
End If
'
rs.Index = "PrimaryKey"
rs.MoveLast
rs.MoveFirst
ReDim s(1 To rs.RecordCount)
For l = 1 To rs.RecordCount
If LCase$(Trim$(rs![Name])) = "none" Then
For k = l To 2 Step -1
s(k) = s(k - 1)
Next k
s(1) = Trim$(rs![Name])
Else
s(l) = Trim$(rs![Name])
End If
rs.MoveNext
Next l
rs.Close
End Sub
Public Sub LoadAndSetupNamesComboBox(cbo As Control, ArrayVals() As String, DropdownLines As Long)
Dim l As Long
'
cbo.Clear
For l = 1 To UBound(ArrayVals)
If LCase$(Trim$(ArrayVals(l))) = "none" And TypeName(cbo) = "ComboBox" Then
cbo.AddItem ArrayVals(l), 0
Else
cbo.AddItem ArrayVals(l)
End If
Next l
'
If TypeName(cbo) = "ComboBox" Then
ComboLinesForDropdown cbo, DropdownLines
ComboAdjustWidth cbo
End If
End Sub
They make some references to other functions that aren't supplied, but I can give them to you if you'd like them.
Good Luck,
Elroy
Last edited by Elroy; Jan 20th, 2018 at 05:02 PM.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
I learned to loath data binding very early on. I just couldn't control it the way I wanted to. I know there's a work-around for this one, but a trivial example is the addition of that "none" option in the way I do things above.
Also, I know others make good use of data-binding. It's just never been for me.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
I did tried to implement your advices but it was to complex for me...
Could you please provide simpler code, or at least one with explanations\comments?
I am using excel with macros\vba, and I have table with many collumns in access, and I would like to import data from one sepcific collumn (eg. collumn named "CollumnA") into combo box, and order it by occurences (if possible)
yes, but it is the same coding. I am working at developer part of excel, with VBA
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"
Where is this ComboBox? Are you designing forms in the Excel VBA? If that's the case, you're sort of in the wrong forum. There's an "Office Development" forum in the main section of these VBForums. Personally, I get around just fine in VBA code, but I don't often actually develop user interfaces (i.e., Forms, Controls, etc) from the VBA. I've done it enough to know it's somewhat different from doing it in VB6 (but not monumentally different).
Also, just for clarification. You say you're in Excel. But, you're not using an Excel spreadsheet (worksheet) for anything, correct? We're just talking about getting data from a MS-Access table into the dropdown list of a combobox.
You might consider doing your VBA work from Access rather than Excel. That way, you could rather trivially design a form with ComboBoxes that were easily bound to any table's field. When you add one of these combos to a form, the wizard immediately asks you which table and field is to be used for the dropdown data. Can't get much easier than that.
Good Luck,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
Debug it with breakpoint. make sure your connection is open and that the recordset has returned something
Also use code tags around your code
Code:
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
Possibly because you are adding a description but have no id column in the combo...
Shouldnt matter.
Vaguely remember set rst = cnn.execute...? rather than rst.open (which is DAO isnt it?)
edit: ms help page link
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
i can see no reason, from your code, why you combbox has no data unless the recordset contains no records
as a test
Code:
With Me.ComboBox1
.Clear
Do
debug.print rst![Department]
.AddItem rst![Department]
rst.MoveNext
Loop Until rst.EOF
End With
you should then see a list of all records in immediate window (ctrl G or view menu)
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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:
Code:
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
what message in the error handlers?
shouldn't the provider now be ACE?
without knowing your database and tables hard to guess
if you want to zip up your workbook and some sample data in database, some one can test for you
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
what message in the error handlers?
shouldn't the provider now be ACE?
without knowing your database and tables hard to guess
if you want to zip up your workbook and some sample data in database, some one can test for you
Hi, thanks for the answer.
I created a simple access database .accdb via Office Access 2010. and I want that one column from this populates my combo box.
When I run code written above, get no error or anything, just an empty combo box which is not populated
i don't have access, just tested this using a workbook as a ADO database
Code:
Private Sub UserForm_Initialize()
Dim cn As Connection, Rs As Recordset, rs2 As Recordset, cel As Range
Set cn = New Connection
Set Rs = New Recordset
Set rs2 = New Recordset
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties='Excel 8.0;hdr=yes'"
'"Extended Properties=""Excel 8.0;hdr=yes;IMEX=1;"""
.Open
End With
Rs.Open "select distinct f1 from [sheet5$]", cn, adOpenStatic
Do Until Rs.EOF
ComboBox1.AddItem Rs("f1")
Rs.MoveNext
Loop
End Sub
the combobox was populated as expected
are you by any chance using multiple monitors?
Last edited by westconn1; Jan 24th, 2018 at 02:26 AM.
Reason: code tags not working this morning
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
sometimes, as even when i tested the code as posted, the combo can look empty, because the dropdown list is displayed on the primary monitor top left, not the one with the combobox, just an excel thing
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete