Populate ListBox List From SQL Query

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Populate ListBox List From SQL Query

Post by jstevens »

Hans,

Per your post here you mention one can populate the list box in one go (without the field names).

If I replace the code

Code: Select all

Me.ListBox1.AddItem rst.Fields(0).Name
    For j = 1 To CC - 1
        Me.ListBox1.Column(j, 0) = rst.Fields(j).Name
    Next j

    Do While Not rst.EOF
        i = i + 1
        Me.ListBox1.AddItem rst.Fields(0).Value
        For j = 1 To CC - 1
            Me.ListBox1.Column(j, i) = rst.Fields(j).Value
        Next j
        rst.MoveNext
    Loop

with 

Me.ListBox1.List = rst.GetRows
only the first record is populated.

Any idea as to why? If I leave the code as is, all records are populated to the ListBox as expected.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Populate ListBox List From SQL Query

Post by HansV »

My mistake - you should actually use

Me.ListBox1.List = Application.Transpose(rst.GetRows)
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Populate ListBox List From SQL Query

Post by jstevens »

Hans,

Thank you for the clarification.
Regards,
John

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Populate ListBox List From SQL Query

Post by rory »

You can also use:

Code: Select all

Me.ListBox1.Column = rst.GetRows
Regards,
Rory

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Populate ListBox List From SQL Query

Post by jstevens »

Thanks Rory!
Regards,
John