talytech
06-12-2007, 09:58 AM
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.
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.