Using Arrays to Populate Listbox

[talytech]

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)
'
rstException.MoveLast
rstException.MoveFirst
 
x = rstException.Fields.Count
ListBox1.ColumnCount = x
 
For i = 0 To x - 1
    MsgBox rstException.Fields(i).Name
Next

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.

[lucas]

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.

[Ebrow]

Hi,

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

[mdmackillop]

Hi Talytech,
What references are you using for Workspace?

[talytech]

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

[Ebrow]

No Problem at all. Glad to help :-)