Printable View
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?
thanks in advance
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:
They make some references to other functions that aren't supplied, but I can give them to you if you'd like them.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
Good Luck,
Elroy
Well we do have the DataCombo:
Attachment 155485
It also works bound to just a RowSource (i.e. only semi-bound).
This demo uses fabricated Recordsets though it works fine with a database as well.
But a lot of people here seem to fear and loathe data binding. It does help to have taken the VB6 classes or at least to have read the books.
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.
Allowing Null entry only requires minor user interface changes. Works just fine as you can see in the updated screenshot above.
Hi guys,
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)
THanks in advance
Then you have posted in the wrong forum. Office macro scripting questions get answered in the Office forum here.Quote:
Originally Posted by nn92
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
I am working in excel developer. I am not even opening the sheets from excel.
The problem is that i am using work computer and I don't have VB installed. And also I have to code it from macro enabled excel.
Thread moved to Office Development, which is the place for VBA questions.
Debug it with breakpoint. make sure your connection is open and that the recordset has returned something
Also use code tags around your code
Possibly because you are adding a description but have no id column in the combo...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
Shouldnt matter.
Vaguely remember set rst = cnn.execute...? rather than rst.open (which is DAO isnt it?)
edit: ms help page link
i can see no reason, from your code, why you combbox has no data unless the recordset contains no records
as a test
you should then see a list of all records in immediate window (ctrl G or view menu)Code:With Me.ComboBox1
.Clear
Do
debug.print rst![Department]
.AddItem rst![Department]
rst.MoveNext
Loop Until rst.EOF
End With
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
Hi, thanks for the answer.Quote:
Originally Posted by westconn1
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
the combobox was populated as expectedCode: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
are you by any chance using multiple monitors?
Yes, I am using two monitors... Does it make a difference?
btw, Thanks I will test the code tommorow morning...
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 thingQuote:
Does it make a difference?