Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Populate a comboxbox in a userform with an Access-recordset

  1. #1
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,451

    Populate a comboxbox in a userform with an Access-recordset

    In this example I will show how we can populate a combobox in a userform with data from a database.

    What we need:
    * MS Windows 2000 or above
    * MS Excel 2000 or above
    * MDAC 2.5 or above (ADO Library)

    Step 1
    Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.

    Step 2
    Insert following code in a standardmodule:

    VB:
    Option Explicit 
     
    Sub Populate_Combobox_Recordset() 
        Dim cnt As ADODB.Connection 
        Dim rst As ADODB.Recordset 
        Dim stDB As String, stConn As String, stSQL As String 
        Dim xlCalc As XlCalculation 
        Dim vaData As Variant 
        Dim k As Long 
         
         'In order to increase the performance.
        With Application 
            xlCalc = .Calculation 
            .Calculation = xlCalculationManual 
            .EnableEvents = False 
            .ScreenUpdating = False 
        End With 
         
         'Instantiate the Connectionobject.
        Set cnt = New ADODB.Connection 
         
         'Path to and the name of the database.
        stDB = ThisWorkbook.Path & "\" & "Test.mdb" 
         
         'Create the connectionstring.
        stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
        "Data Source=" & stDB & ";" 
         
         'Create the SQL-statement.
        stSQL = "SELECT * FROM tblData" 
         
        With cnt 
            .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
            .Open stConn 'Open connection.
             'Instantiate the Recordsetobject and execute the SQL-state.
            Set rst = .Execute(stSQL) 
        End With 
         
        With rst 
            Set .ActiveConnection = Nothing 'Disconnect the recordset.
            k = .Fields.Count 
             'Populate the array with the whole recordset.
            vaData = .GetRows 
        End With 
         
         'Close the connection.
        cnt.Close 
         
         'Manipulate the Combobox's properties and show the form.
        With frmData 
            With .ComboBox1 
                .Clear 
                .BoundColumn = k 
                .List = Application.Transpose(vaData) 
                .ListIndex = -1 
            End With 
            .Show vbModeless 
        End With 
         
         'Restore the settings.
        With Application 
            .Calculation = xlCalc 
            .EnableEvents = True 
            .ScreenUpdating = True 
        End With 
         
         'Release objects from memory.
        Set rst = Nothing 
        Set cnt = Nothing 
    End Sub 
    
    
    The most notable thing is that we need to transpose the horizontal array vaData to an vertical to add it to the combobox.

    Happy dataretrieving
    Last edited by Craig Ottley; June 9th, 2011 at 08:08.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  2. #2
    Join Date
    27th May 2005
    Posts
    14

    Re: Populate a comboxbox in a userform with an Access-recordset

    Hi Dennis,

    How are you? I was wondering if you can help me out.

    I used your code to populate a combobox and I made some modifications to it.

    Basically, my table has 3 fields (last name, first name and customer Id). What I want is to show only the last name and first name in the combo box, then when the user selects a person, I want to use the customer ID as a value that is stored to do some other stuff. What I change was the following:

    VB:
    With .cmbLastName 
        .Clear 
        .BoundColumn = 3 
        .List = Application.Transpose(vaData) 
        .ListIndex = -1 
        .ColumnCount = 2 
    End With 
    
    
    The code works perfectly when I have 2 or more records in the table, but when I go down to 1 record, it is all screwed up...

    can you please provide some guidance?

    thanks a lot,

    Carlos
    Last edited by royUK; February 7th, 2006 at 16:17. Reason: add code tags, please use when posting codes & change title

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    27th May 2005
    Posts
    14

    Re: Populate a comboxbox in a userform with an Access-recordset

    Dennis,

    I found the solution. There is a bug in the code you posted... if you change one line from the code, everything works fine.

    VB:
     
    With .cmbLastName 
        .Clear 
        .BoundColumn = 3 'this uses the CustomerID as the field to save
         ' WRONG!! -> .List = Application.Transpose(vaData)
        .column = vaData 'Use this and the problem works even with 1 record!!
        .ListIndex = -1 
        .ColumnCount = 2 'this makes appear the two columns on the combobox
    End With 
    
    
    that way, the code can read even when there is 1 record...

    bye,

    Carlos

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th April 2006
    Posts
    13

    Re: Populate a comboxbox in a userform with an Access-recordset

    Would it be possible to set the list box headins with the field headings from the query ?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Populate a comboxbox in a userform with an Access-recordset

    Members, please keep all question in the question forum only. Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Populate ComboBox From RecordSet
    By BobbyBob in forum Excel and/or Access Help
    Replies: 2
    Last Post: February 5th, 2008, 17:51
  2. Moving A Recordset From Access
    By Shippwreck in forum Excel and/or SQL Help
    Replies: 1
    Last Post: December 10th, 2007, 18:45
  3. count recordset in Access
    By mbh in forum Excel and/or Access Help
    Replies: 1
    Last Post: April 19th, 2006, 14:28
  4. Create QueryTable and populate with ADO Recordset
    By XL-Dennis in forum Advanced Excel Integration
    Replies: 0
    Last Post: March 27th, 2005, 02:03
  5. Replies: 3
    Last Post: December 17th, 2004, 16:43

Bookmarks

Posting Permissions

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