Results 1 to 19 of 19

Thread: inserting data in combo box

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Lightbulb inserting data in combo box

    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

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: inserting data in combo box

    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.

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: inserting data in combo box

    Well we do have the DataCombo:

    Name:  sshot.png
Views: 234
Size:  4.9 KB

    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.
    Attached Files Attached Files
    Last edited by dilettante; Jan 20th, 2018 at 10:18 PM. Reason: Updated attachment and screenshot

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: inserting data in combo box

    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.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: inserting data in combo box

    Allowing Null entry only requires minor user interface changes. Works just fine as you can see in the updated screenshot above.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: inserting data in combo box

    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

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: inserting data in combo box

    Quote Originally Posted by nn92 View Post
    I am using excel ...
    Then you have posted in the wrong forum. Office macro scripting questions get answered in the Office forum here.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: inserting data in combo box

    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"

  9. #9
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,852

    Re: inserting data in combo box

    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.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: inserting data in combo box

    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.

  11. #11
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: inserting data in combo box

    Thread moved to Office Development, which is the place for VBA questions.
    My usual boring signature: Nothing

  12. #12
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: inserting data in combo box

    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

    BOFH Now, BOFH Past, Information on duplicates

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

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: inserting data in combo box

    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

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: inserting data in combo box

    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

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: inserting data in combo box

    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

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: inserting data in combo box

    Quote Originally Posted by westconn1 View Post
    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

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: inserting data in combo box

    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

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    21

    Re: inserting data in combo box

    Yes, I am using two monitors... Does it make a difference?

    btw, Thanks I will test the code tommorow morning...

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: inserting data in combo box

    Does it make a difference?
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width