populate list box with recordset 1

[qjd2004]

Hiya,

Using VBA, I'm trying to fill a list box with the contents of a recordset. I'm being a thick as it's putting each field from the rset into a new item on this list. How do you put each field into a new column ???

ReDim Arr(1 To rs.Fields.Count - 1)
ReDim Lbox(1 To UserForm6.DisplayBox.ColumnCount - 1)

'use additem & vbtab & item, etc
    rs.MoveFirst
        For i = LBound(Arr) To UBound(Arr)
                If Not IsNull(rs.Fields(i)) Then
                    UserForm6.DisplayBox.AddItem rs.Fields(i).Value
                End If
                rs.MoveNext
        Next i

here's wher I am so far (not very! lol).

Any help much appreciated.

 

[Tranman]

gjd2004,
Here's a snippet that does pretty much the same thing you're trying to do.

The trick is to build up the item with semicolons between the column values, have the right number of columns defined for the listbox, and have the Row source of the listbox be "Value List".

Private Sub Form_Open(Cancel As Integer)
Dim rs As New ADODB.Recordset
Dim strItem As String
Dim intCtr As Integer
rs.Open "select * from cust", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do While Not rs.EOF
strItem = ""
For intCtr = 0 To 5
Select Case IsNull(rs.Fields(intCtr))
Case True
strItem = strItem & "NULL" & ";"
Case False
strItem = strItem & rs.Fields(intCtr) & ";"
End Select
Next
List2.AddItem (strItem)

rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Sub


Now if you would do a favor in return, please tell me how you created the little code window in your post.

Thanks,
Tranman

 

[PHV]

To post in code window like this:

My code snippett

do this:
[ignore]

My code snippett

[/ignore]
The Process TGML checkbox must be checked.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Tranman]

Thanks, PHV, now I can make my posts look like the big boys'.

Tranman

 

[qjd2004]

Tranman,

Thanks for getting back to me. I'm having some trouble implementing your suggestions.

It's a problem for me to have the right number of columns defined for the listbox, as the user can specify the SQL query that builds the recordset (therefore the number of colunms can change a lot). Also, to have the Row source of the listbox to be "Value List". I don't know how to do that, as when I try to put anything in the row source box of the VBA editor it makes an error.

I don't mind setting a general number of columns (it's okay if some of them are empty sometimes) but how do I get the row source to be recognised?

 

[Tranman]

Hi,
To change the row source to Value List:

Open your form in design view.
Right click on your list box.
Select "Properties".
Select the "Data" tab.
Double-click on the words, "Row Source Type"
Each time you double-click, the value in the box will
toggle to either "Value List", or "Field List",
or "Table/Query". Just stop double-clicking when it says
"Field List".

To set the default column count for the list box:

Open the form in design view.
Right click on your list box.
Select "Properties".
Select the "Format" tab.
Enter the default column count on the line marked "Column Count".

Let me know if you keep having problems.

BTW, you *can* manipulate the number of columns in your list box with code by changing the ColumnCount property of the list box, but then you can run into problems with sizing the form and the list box; etc.

Tranman

 

[qjd2004]

Hi,

I'm using VBA and not VB6. The "Row Source" option in the properties window for the ListBox accepts text but has no presets and nothing happens when I double click on it.

Setting the default column count is ok though.

Any suggestion what I'm doing wrong?

 

[Tranman]

Hi,
The double-clicking functionality is the same in VB6 or VBA.

Are you double-clicking the words "Row Source", or double-clicking the words "Row Source Type", or double-clicking the text area?

It's the words, "Row Source Type"(second entry in the list) that you want to double-click(there's also a dropdown over at the right side of the text area if you want to use that instead of double-clicking). Then leave the "Row Source" blank because you will be filling it from your VBA code at runtime.

Hang in there, we'll get you where you want to be.

Tranman

 

[qjd2004]

I'm sorry.

I think I'm being so stupid, but there's no Row Source Type option in the properties box for my list box.

 

[qjd2004]

Sorry, I just can't find it.

I can change this in the code though I guess:

      With UserForm6.DisplayBox
           .RowSourceType = whatever
      End With

Can you tell me how to add the column headings? I know there is a value for rs.Fields(Col).Name, how do I assign that to the columns using code?

I know there is a UserForm6.DisplayBox.ColumnHeadings (or columnnames) param, but it seems to be a boolean. How do I assign the headers from the recordset to the columns?

Thanks for all your help so far by the way! Really appreciate it.

 

[Tranman]

Hi,
I'm feeling sort of thick as a brick myself, but I think I know what is going on now.

There are several different kinds of listbox that you can use, and we are apparently not talking about the same class. So, when I'm telling you that all you need to do is ________, you can't do it because the type of listbox you have doesn't support that property/method. Either that, or we are talking about the same listbox, but you have an earlier version that does not expose the same properties.

I just checked my form, and I have 4 different classes of listbox available, all of which have different properties/methods available.

So, as a way of trying to narrow this down to which type of listbox you are trying to use (which I may or may not have available on my machine, depending on what class libraries we each have registered), why don't you tell me as much as you can about your environment? Access97/2K/XP/2003? Service Pack Level (1/2/3)? Then open the properties window for your listbox, click on the data tab, and list the properties you see listed there.

All 4 of my listboxes classes have different data properties in the list, so if yours matches one of those, we should be able to discover which one you're using.

Regarding Column Headings--you are correct that the ColumnHeads property is a boolean. When it's set to True, that means that column headings will be displayed. Setting it to False hides the column headings. The headings themselves come from the table/query if the RowSourceType is set to "Table/Query", or they come from the first item in the list if RowSourceType is set to "Value List". Back to RowSource again . You can check the behavior by saying <controlname>.Columnheads = True in your form open event(or in the properties box). The column headings will then be displayed, according to the rules above, and if you try with your mouse, you won't be able to select the top row in the listbox. It will also not scroll vertically when you try to do that.

Just for grins, you might also try putting a line like this somewhere near the top of the form open event:

<listboxname>.RowSourceType = "Value List"

You should not be hesitant to set properties in code if, for some reason, they are just not being exposed in the properties window.

I know this is all kind of discouraging, but remember that you will learn way more when you have to dig than when everything goes right.

Let me know how it goes.

Tranman

 

[qjd2004]

Hiya,

I'll have a look at .RowSourceType = "Value List" later on 2day.

meanwhile, here's my spec:

Microsoft XP Professional 5.1.2600 Service Pack1 Build 2600
Microsoft Excel 2002 (10.4302.4219) Service Pack2

ListBox type is "Microsoft Forms 2.0 ListBox"

In the Data Section of Properties for the list box

BoundColumn
ColumnCount
ColumnHeads
ColumnWidths
ControlSource
ListStyle
RowSource
Text
TextColumn
TopIndex

I wish I knew how to use the vsFlexString1 grid instead (I do have a copy of that component) but alas I don't.

 

[Tranman]

WELLLLLLLLL,
That certainly clears everything up. You were working in Excel and I was helping you in Access.

Here's a sub that opens a recordset, configures your kind of listbox, and loads the fields with the contents of the recordset.

Sorry I didn't ask sooner. You will need to check your rs.Fields for Nulls because the listbox won't like them. Let me know if this works OK.

Tranman

Private Sub UserForm_Click()
Dim intRow As Integer
Dim intCol As Integer
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ACCESS_XP_DBs\DUMMY.mdb;Persist Security Info=False"
rs.Open "select * from cust", con, adOpenDynamic, adLockOptimistic

ListBox1.ColumnCount = rs.Fields.Count

Do While Not rs.EOF
ListBox1.AddItem
ListBox1.AddItem
For intCol = 0 To rs.Fields.Count - 1
ListBox1.List(intRow, intCol) = rs.Fields(intCol)
Next
intRow = intRow + 1
rs.MoveNext
Loop

rs.Close
con.Close
Set rs = Nothing
Set con = Nothing

End Sub

 

[Tranman]

Sorry, I put in two additems. There should only be one.

 

[qjd2004]

Hi Tranman,

Thanks for your assist, it works perfectly! I've modified to check for nulls.

rs.Open strSQL, con, adOpenDynamic, adLockOptimistic

UserForm6.DisplayBox.ColumnCount = rs.Fields.Count

        Do While Not rs.EOF
            UserForm6.DisplayBox.AddItem
            For intCol = 0 To rs.Fields.Count - 1
                If Not IsNull(rs.Fields(intCol)) Then
                    UserForm6.DisplayBox.List(intRow, intCol) = rs.Fields(intCol)
                End If
            Next
            intRow = intRow + 1
            rs.MoveNext
        Loop

One last question. How do I autosize the column widths so that they fit the contents?

 

[qjd2004]

Also, another quick question:

How do I include column headings as well as autosize?

I'm aware of a .CoulumnHeads property, but this is a boolean and I can't see any other way of doing it. When I try to set RowSourceType property from the code I don't know what value to assign to it?

 

[Tranman]

Hi,
You can't really autosize the columns. I think that datagrids will do that, but not listboxes.

After thinking it over for a few minutes, I came up with a way to do it. It's kind of ugly, but it does work. What is involved is putting a label control on your form with the autosize property set to true. Then you drop every piece of data into the label and if the label width (times a factor) is larger than any previous width, it stores the new width in an array. After all of the data is loaded, it goes through the array and sets the column widths to the highest values it encountered. Like I said, it's kind of ugly.

There are a couple of gotchas. You need to make sure that the wordwrap property on the label is set to false. You need to also be sure that the label and the listbox both have the same font. You can play with the "factor", and maybe get the columns to be a little narrower.

As to the column headers, I played with them for about 30 minutes and was never able to get any text to be displayed in them. If you do Listbox1.ColumnHeaders = True, the headers are displayed, but they are blank. I just don't know how to get data into them. Tried every trick I could think of...When I do the same thing on an Access form, the listbox uses the first row of data as the header info and everything works like you'd expect. I will think this over, and if I come up with anything new, I'll send you a message.

Anyway, here's the code that "autosizes" the column width:

Private Sub CommandButton1_Click()
Dim aryColWidth() As Single
Dim strColWidth As String
Dim intRow As Integer
Dim intCol As Integer
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ACCESS_XP_DBs\DUMMY.mdb;Persist Security Info=False"
rs.Open "select * from cust", con, adOpenDynamic, adLockOptimistic

ListBox1.ColumnCount = rs.Fields.Count

ReDim aryColWidth(rs.Fields.Count - 1)
intRow = ListBox1.ListCount

Do While Not rs.EOF
ListBox1.AddItem

For intCol = 0 To rs.Fields.Count - 1
'Store appropriate column width
Label1 = rs.Fields(intCol)
If Label1.Width * 1.2 + 6 > aryColWidth(intCol) Then
aryColWidth(intCol) = Label1.Width * 1.2 + 6
End If
ListBox1.List(intRow, intCol) = rs.Fields(intCol)
Next

intRow = intRow + 1
rs.MoveNext
Loop

'Set appropriate column widths
strColWidth = ""
For intCol = 0 To UBound(aryColWidth)
strColWidth = strColWidth & aryColWidth(intCol) & ";"
Next

ListBox1.ColumnWidths = strColWidth

Label1.Visible = False

rs.Close
con.Close
Set rs = Nothing
Set con = Nothing

End Sub

Anyway, I'll try to find some time to play with the headers and maybe get the things to work.

Let me know if this does what you want.

Tranman

 

[Tranman]

qjd,
Bad news...ColumnHeads do not work on listboxes in Excel unless the Listbox is based upon a range on a sheet. Just won't work with a recordset. This is according to Microsoft.

One site I visited suggested that you could create a label for each column, and put the label up above the listbox and fill in the labels with the column names. Sounds like a lot of work to me--especially with the different-sized columns that you'll have.

Sorry I could not help you with the headers.

Tranman

 

[PHV]

Why not dump the recordset (with field names first) in an hidden sheet and set the RowSource property to this range address ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[qjd2004]

Hi Tran,

thanks for all your assistance man! I've gone with the autosize suggestion, the headers I'm leaving for now. Boy, I gotta learn how to use datagrids man!

Many thanks for all your help on this. A well deserved stars on it's way!

Q