ListBox Headings Nightmare!

[wm2005]

Please please help me, Im banging my head against the wall on this...

I want to use the MSForms (version 2) ListBox control, on a form, but I cant set the headings via code. I can only populate the rows.

I add a row thus:
Me.lstTest.AddItem "Hello"

And turn on headings:
Me.lstTest.ColumnHeads = True

But how the blazes do I get the value in the heading (without binding the control to data)?

Please help.

Respectfully yours,
WM

 

[Eupher]

Why use other than Access' native listbox control? I just tested the following code in a generic listbox in Access 2002 and it works:

Private Sub Form_Open(Cancel As Integer)
Me!List0.RowSourceType = "Value List"
Me!List0.ColumnCount = 2
Me!List0.ColumnWidths = "1 in;1 in"
Me!List0.ColumnHeads = True
Me!List0.AddItem Item:="Name;Title", Index:=0
Me!List0.AddItem Item:="'Bossman, Ima';CEO", Index:=1
Me!List0.AddItem Item:="'Fant, Sicko';Flunky", Index:=2
Me!List0.AddItem Item:="'Fones, Ringy';Receptionist", Index:=3
Me!List0.AddItem Item:="'Geek, Ira';CIO", Index:=4
End Sub

HTH,

Ken S.

 

[gol4]

If using 97 don't think you can. See this

http://support.microsoft.com/kb/q164343

 

[TheAceMan1]

How are ya wm2005 . . . . .

Do not feather yourself!

Base the [blue]RowSource[/blue] of the listbox on a query and set the [blue]Caption Property[/blue] of the specific fields in the query to what you desire!

See Ya! . . . . . .

 

[wm2005]

Thanks for your input chaps.

Eupher: Long story... The native ListBox is limited to 2048 characters when in ValueList mode.

Gol: Im in Access 2000 using MS Forms 2.0 Object Library.

AceMan: I dont want to bind to a recordsource (I guess thats the long story, and I've probably programmed myself into a corner here, but it just has to be possible to display headings when using an unbound listbox2 control...hasnt it?

WM

 

[Eupher]

wm2005,

Setting a listbox's ControlSource property makes the control bound. Setting the RowSource property does NOT.

Ken S.

 

[wm2005]

Good point. What I should have said is that I am populating the data manually (call me crazy) via an ado connection from a remote database.

For control of data flow, and more importantly near bullet-proof security I dont want any link to the data in the client application except for what is in code (which will be in an mde and therefore practically inaccessible).

So my frontend doesnt have tables or queries I can use as a RowSource (tried doing it from an ado recordset - doesnt work).

 

[wm2005]

Why on earth have I not used the ListView control before...

Dim l As MSComctlLib.ListView

Set l = Me.lvwTest.Object

With l
    .View = lvwReport
    
    'Add Headings
    .ColumnHeaders.Add , , "Name"
    .ColumnHeaders.Add , , "Surname"
    .ColumnHeaders.Add , , "Address"
        
    ' Add the normal text
    .ListItems.Add , , "Fred"
    .ListItems.Add , , "Sarah"
    .ListItems.Add , , "Paul"
    
    ' Add a value to the second column to the first item on the list ("Fred")
    ' (1) = First item on list
    .ListItems(1).SubItems(1) = "Crowley"
    .ListItems(2).SubItems(1) = "Ives"
    .ListItems(3).SubItems(1) = "Smith"
    
    ' Add a value to the third column to the first item on the list ("Fred")
    ' (1) = First item on list
    .ListItems(1).SubItems(2) = "16 Liverpool Lane"
    .ListItems(2).SubItems(2) = "102 England Street"
    .ListItems(3).SubItems(2) = "1 Baker Street"

End With

Sweeet!

 

[Eupher]

Hi, wm2005,

Just brainstorming here... How about a temp table definition in code for the purpose of populating your listbox... you could even make it hidden... then destroy the tabledef when the form closes. Would that be secure enough?

And the rowsource of a listbox doesn't have to be a saved query or table, it can be a SQL string... which I presume could be a pass-through query statement... IOW, it seems to me if you can get at the data with an ADO connection, you could probably also get it with some sort of SQL statement... but then I don't have much experience with remote databases...

Ken S.