Results 1 to 6 of 6

Thread: Using Arrays to Populate Listbox

  1. #1

    Using Arrays to Populate Listbox

    I am trying to populate a listbox I have on an Excel UserForm with data from a recordset.

    I want the fields to be the column headings and the records to be the data.

    I am having a terrible time. This is the code I have so far:

    Dim wrkJet As Workspace
    Dim dbs As Database
    Dim rstException As Recordset
    Dim sql As String
    Dim fstName, lstName As String
    Dim lstRow As String
    Dim x As Integer
    Dim fldname As String
    fstName = Worksheets("Sheet1").Range("c21").Value
    lstName = Worksheets("sheet1").Range("h21").Value
    sql = "SELECT tblExceptions.FName, tblExceptions.LName, tblExceptions.WorkType, tblExceptions.HiringMgr_FName, tblExceptions.HiringMgr_LName, tblExceptions.HiringMgr_userId, tblExceptions.CostCenter, tblExceptions.RequestDate, tblExceptions.Approver_Fname, tblExceptions.Approver_Lname, tblExceptions.ExceptionApproved, tblExceptions.BIStatus FROM tblExceptions " _
        & "WHERE (tblExceptions.FName)= '" & fstName & "' AND (tblExceptions.LName)= '" & lstName & "';"
    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    Set dbs = wrkJet.OpenDatabase("C:\Production\Exceptions_DB.mdb")
    Set rstException = dbs.OpenRecordset(sql, dbOpenDynaset)
    x = rstException.Fields.Count
    ListBox1.ColumnCount = x
    For i = 0 To x - 1
        MsgBox rstException.Fields(i).Name
    The value of x = 12 and then a message box displays the 12 field names.

    I want a listbox to display those 12 field names as the column headings or as the first row of data in the listbox and then show the other rows as the data from the recordset. Can someone please help me out here. I am struggling. I think I should be using an array but I can't understand the example of how to set the array up.

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Jun 2004
    Tulsa, Oklahoma
    Are the X values the first row on the spread sheet? If they are I think you could use the rowsource property of the listbox...

    If you could post your workbook it would save us having to reproduce it.
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    VBAX Regular Ebrow's Avatar
    May 2007

    Smile An Example that you can work form


    Here is an example that you can run from.

    Basically you want to build up an array (as your question) and then using the object.additem myArray method once your array has been built.

    Save the database to C:\ root directory or change the address in the VBA code to suit.

    Let me know your thoughts.

    Attachment 5985
    Nothing is impossible, just it hasn't been thought of yet.

  4. #4
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    May 2004
    Hi Talytech,
    What references are you using for Workspace?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Perferct! OMG .. I am so grateful. Thank you. I knew there was a way. Thanks for helping to understand the array function as well.

  6. #6
    VBAX Regular Ebrow's Avatar
    May 2007
    No Problem at all. Glad to help :-)
    Nothing is impossible, just it hasn't been thought of yet.

Posting Permissions

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