Populating the headers in a listbox

[tomric3589]

Hi, I have a list box being populated from an external database. There are 6 columns in the list box. My code goes out to a database runs a query and populates a list box. I have the ColumnsHeads property set to true, it shows the column head boxes but it won't populate them. My code is as follows:

Private Sub UserForm_Initialize()

Dim sdate As Date
Dim fdate As Date

fdate = ActiveProject.CurrentDate
fdate = Format(fdate, "Ddddd")

txtend.Value = fdate
txtstart.SetFocus

End Sub

Public Function test(sdate, fdate) As String

Dim cmd As ADODB.Connection
Dim rs As ADODB.Recordset
Dim qry As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim prm1 As ADODB.Parameter
Dim strconn As String
  Set cmd = New ADODB.Connection
  
strconn = "Provider= microsoft.ace.oledb.12.0;" & "Data Source = Z:\TRS Commercial\TRS Prod\data bases\parts status1.accdb"
cmd.Open strconn

Dim k As New ADODB.Command
Set qry = New ADODB.Recordset
Set rs = New ADODB.Recordset

cmd.CursorLocation = adUseClient

Dim tskdat() As String
Dim i As Integer
Dim objfield As ADODB.Field
Dim icol As Integer
Dim ifld As Integer
Dim n As Integer
Dim m As Integer

'opening and running the query
Set k.ActiveConnection = cmd
k.CommandType = adCmdStoredProc

k.CommandTimeout = 8000
k.CommandText = "updatetest"

Set prm = k.CreateParameter
prm.Type = adDate
k.Parameters.Append prm
k.Parameters(0).Value = sdate

Set prm1 = k.CreateParameter
prm1.Type = adDate
k.Parameters.Append prm1
k.Parameters(1).Value = fdate

Erase tskdat

qry.Open k

If Not qry.EOF Then qry.MoveLast
n = qry.RecordCount - 1
m = qry.Fields.Count - 1
ReDim tskdat(n, m)
qry.MoveFirst
    
' populating the list box
    i = 0
 
  Do Until qry.EOF
  'objfield.Name
  
    For icol = 0 To m
        tskdat(i, icol) = qry.Fields(icol)
    Next
    i = i + 1
    qry.MoveNext
    
Loop
 lbdata.ColumnHeads = True
 lbdata.List() = tskdat
 
cmd.Close
Set qry = Nothing
Set k = Nothing
Set cmd = Nothing
Set rs = Nothing

End Function

Can anybody please help me to get these coulmn heads to populate?

Thank you.

[zmbd]

You have to use the "Row Source" property of the listbox control.
You cannot do this using the "additem" method.

Two examples
1) Table or query
Set the row source to point to the table name, query name, or enter the SQL in the RowSource property directly.
Set your number of columns, formating etc...
Set the column headings to yes.
The first row will now have the field names as the header.
Use a query and alias the field names as the rowsource to alter the names displayed.

2) Data list
Once again you will use the rowsource property.
Easy example.
Insert a listbox and set up for two columns, both shown, and the headers property is set to true, then in the row source property enter: Header1, Header2; row1cell1, row1cell2; row2cell1, row2cell2; row3cell1, row3cell2; You will see that the the first entries are in the header row and the remaining in the subsequent row.

This has been vetted on my test forms in
ACC2003 WinXPEnterprise (had several like this)
ACC2010(32bit)W in7Enterprise(6 4bit)

[tomric3589]

Tried it, the list box is on a form in a microsoft project file, and it won't let me set the record source property. I get an "Invalid row source property" error.

[zmbd]

You didn't state you were using Microsoft Project. Which would have been most helpfull as this forum deals primarily with Access.

For MSP, you will not have a recordsource because it's not a database nor worksheet; therefor, there is no known way to populate the list box header row. If you spend a few days with the search engines you'll find that this is a very common frustration with VBA programmers since circa Win95 release.

There may be some API call that will work; however, that is a tad out of my knowledge base.

[zmbd]

After reading a littlebit on MS-Project I have an additional question as the Rowsource Property should be available...

Are you attempting to use the .additem method for the listbox?

If so, that will NOT work. You can use one method or the other but not both.

You also need to properly set the rowsource type to either list or table/query.

[tomric3589]

If you look at my code, i'm using a ADODB connection to open a access database and run a query. The parameters are sent to the query, I then take the data the query provides and populate a list box in project. I think i can use the objfield.name property, but I don't know how to go about it.

[zmbd]

Unfortunately I do not have an installation of MS-Project available; thus no way for me trouble shoot directly.

Yes I do see that how you've opened the database link.
I'm also assuming that tskdat is your listbox.

However, I think we're not using the same names for things; therefore, so that I have an idea as to how the UI for MSP is presenting the listbox control to you would you please do the following:

- In a new form (so that we do not damage any of your current work (°,°) )
- Insert a new listbox control, open the properties for the control:
-- Data tab: Please list for me the first four field names. For example:
[Data]![Control Source] = null
[Data]![Row Source]= entry1, entry2;
[Data]![Row Source Type]= Table/Query
Etc…
-- Format tab: Please list for me the first four and their settings as above; [Format]![Visible]=true, Etc…

It would be most helpful, if you would list those same properties and settings for the cotrol in your production form.