none
Populate combobox with Access table RRS feed

  • Question

  • I’ve looked high and low – the only examples I can find are for cascading comboboxes or user forms.  My need is simpler:

    I have a Word 2010 template with a form (in the document; not a userform).  On the form is a legacy drop-down (ddBusinessCode).  I would like to populate this drop down with an access table.  (R:\!datasources\BusinessCodes.accdb; tblBusinessCodes). The access table has two fields: Code and Description.  I want the drop-down to show both fields, but only place the Code on the form.

    I would appreciate any help!

    Thanks,

    Sandi

    Monday, September 16, 2013 2:08 PM

All replies

  • Hi,

    According to your description, I think you want a program to populate the ComboBox “ddBusinessCode” with the records of a table in an .accdb file.

    What do you mean by “I want the drop-down to show both fields, but only place the Code on the form.”?

    In fact, there is only one property of the addItem method of the ComboBox, so what shows in the ComboBox means the value of it.

    I write a VBA programming for your reference. In the sample, the value of the ComboBox  shows as “Code-Description”.

    Private Sub main()
        On Error GoTo Main_Err
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=C:\Documents\BusinessCodes.accdb"
        rst.Open "SELECT tblBusinessCodes.[Code], tblBusinessCodes.[Description] FROM tblBusinessCodes;", _
                 cnn, adOpenStatic
        rst.MoveFirst
        With Me.ddBusinessCode
            .Clear
            Do
                strInfo = rst(0) & "-" & rst(1)
                .AddItem strInfo
                rst.MoveNext
            Loop Until rst.EOF
        End With
    Main_Exit:
        
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    
    Main_Err:
        MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
        Resume Main_Exit
    End Sub


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 18, 2013 12:04 PM
    Moderator
  • Thank you very much for your reply.  Yes, you understand exactly what I am trying to accomplish. (populate the ComboBox “ddBusinessCode” with the records of a table in an .accdb file).

    What I mean by “I want the drop-down to show both fields, but only place the Code on the form.”?  Is this:

    When the user clicks the drop down, he should see both the code and the description, e.g.,:

    1100 | Agriculture, Forestry, Fishing and Hunting

    2100 | Mining, Quarrying, and Oil and Gas Extraction

    And when he makes a selection, just the code (1100) is placed in the field (in the document, not a user form).

    I placed the code you gave me under Modules.  It gave me an “Invalid use of Me” error @ decompile.  I removed the Me. and had no error @ decompile, but a '424': Object Required Using DAO” at run time.  I think I am putting the code in the wrong place, or do not have the correct Reference.  Can you help me out with this? 

    References I currently have are

    • Visual Basic for Applications
    • MS Word 14.0 Object Library
    • OLE automation
    • MS Office 14.0 Object Library
    • MS Forms 2.0 Object Library
    • MS ActiveX Data Objects 6.1 Library
    • MS DAO 3.6 Object Library

    And I’m not seeing anything that looks like Microsoft.ACE.OLEDB.12.0 in my list of available references.

    Thanks very much for your time!

    Wednesday, September 18, 2013 1:46 PM
  • Hi,

    I think you will meet the error “Invalid use of Me Keyword” because you place the code in the module.

    The Me keyword can't appear in a standard module because a standard module doesn't represent an object.

    Here is more information for your reference: Invalid use of Me keyword.

    You could try to place the code in the “ThisDocument” instead of the module as the picture below.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 19, 2013 11:33 AM
    Moderator
  • I moved it to This Document and am getting a compile error: method or object not found.  Does Me. refer to objects on a userform?  This is not a userform; It is a dropdown/combobox.
    Thursday, September 19, 2013 12:57 PM
  • Hi Sandi

    Yes, I think Luna's code refers to an ActiveX control, either on the document surface or in a UserForm.

    What kind of dropdown do you mean by "legacy"? There would be two possibilities, a form field dropdown or an ActiveX.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, September 30, 2013 6:54 PM
    Moderator