Fill Listbox with 2-D Array (1 Viewer)

ShredDude · Jan 26, 2009

I'm struggling to find a way to fill a Listbox on an Access Form with the contents of an Array that I'd build in a VBA routine.

Through searches, I've gotten as far as building a long string and assigning it to the rowsource for the Listbox. Not pretty IMO for my final solution.

I made a Listbox with 5 columns then I do this:

Code:

Private Sub Form_Load()
Dim r As Integer, c As Integer
Dim strRowsource As String
For r = 1 To 10
    For c = 1 To 5
    strRowsource = strRowsource & (r * c) & ", "
    Next c
Next r

Me.lboxBreakdown1.RowSource = strRowsource

End Sub

It works, but my real list will be close to 400 rows long.

Is there a way to assign a two-dimensional array to the Listbox in one statement. I can do this on an Excel Userform, but am struggling with my inexperience in Access.

I tried similar routines as above, building an array full of values and then trying to assign it to control source and rowsource to no avail. I tried looping through the listbox.column(c-1,r) with no luck.

The Data that will comprise the 400 rows is an extrapolation of a set of static variables out through time. Building the results with some simple looping in VBA is quite effective for the task. I just can't seem to figure out how to display my results in a listbox on an access form so that the user can scroll through the list.

Any pointers most welcome.

Thanks,

Shred.

ByteMyzer · Jan 26, 2009

Check out the following link:

ACC: How to Fill a Multiple-Column List Box Using Code

DCrake · Jan 26, 2009

Just tested the below without any issues

Code:

Dim r As Long, c As Long
Dim strRowsource As String
For r = 1 To 400
    For c = 1 To 5
    strRowsource = strRowsource & (r * c) & ", "
    Next c
    Me.lboxBreakdown1.AddItem strRowsource
    
    strRowsource = ""
Next r

David

jal · Jan 26, 2009

ByteMyzer said:
Check out the following link:

ACC: How to Fill a Multiple-Column List Box Using Code

Looks useful, but I don't understand the code. Anyone care to clarify it?

jal · Jan 26, 2009

Ok, I'm beginning to understand the code but I don't see how to customize it (suppose I want to dynamically populate the listbox using code with new data obtained at runtime).

jal · Jan 26, 2009

Shred, I can't recall if there is a way to do this in Access (i.e. bind an array to a listbox in one line). I'll check my notes. In the meantime, other options:
(1) You can bind a recordset to a listbox and the RS can be either tied to a table or disconnected.
(2) You can bind a query object or a SELECT statement to a listbox.

I'm guessing that binding tends to populate the listbox faster than looping.

jal · Jan 26, 2009

ByteMyzer said:
Check out the following link:

ACC: How to Fill a Multiple-Column List Box Using Code

Ok, I think I now understand how to use this code - some of the weirdest code I've ever seen. Can anyone think of a good reason to use this weird approach as opposed to more common methods?

jal · Jan 26, 2009

Shred, I don't think Access let's you bind an array directly to a listbox in one line of code. I know this can be done in other Office apps such as Excel.

ShredDude · Jan 26, 2009

I too was somewhat perplexed by that code. One thing I found was that my VBA / Access2007 environment does not support rowsource type 5 (array) as referred to in that post. That appears to be a feature within VB?

I have yet to find a way to populate the listbox in an Access form with an array as I can do in an Excel Userform. I had thought of the Recordset idea but hadn't tried it yet. My limited understanding of Recordsets was that you could create the record set in code to assemble a collection of record's/fields in the manner you needed. Similar to a query.

My need however is to calculate the values that need to appear in the array. These values are not stored in any records, just a few static variables are in tables that would be used to initiate the calculations. I can do the calculations in VBA, and create the array of results with no problems. I'm just looking for a way to publish this array to a User in a tabular form they can scroll through. No editing of the data presented is required. It'd be review only. Envision 7-8 columns of data, about 400-500 rows long. Easy to do in a Spreadsheet. How do I display this sort of thing in Access??? Flex Array controls??? Can you embed an Excel object?

Maybe there's another way than a Listbox? Anyone have any ideas?

jal · Jan 26, 2009

ShredDude said:
I too was somewhat perplexed by that code. One thing I found was that my VBA / Access2007 environment does not support rowsource type 5 (array) as referred to in that post. That appears to be a feature within VB?

I have yet to find a way to populate the listbox in an Access form with an array as I can do in an Excel Userform. I had thought of the Recordset idea but hadn't tried it yet. My limited understanding of Recordsets was that you could create the record set in code to assemble a collection of record's/fields in the manner you needed. Similar to a query.

My need however is to calculate the values that need to appear in the array. These values are not stored in any records, just a few static variables are in tables that would be used to initiate the calculations. I can do the calculations in VBA, and create the array of results with no problems. I'm just looking for a way to publish this array to a User in a tabular form they can scroll through. No editing of the data presented is required. It'd be review only. Envision 7-8 columns of data, about 400-500 rows long. Easy to do in a Spreadsheet. How do I display this sort of thing in Access??? Flex Array controls??? Can you embed an Excel object?

Maybe there's another way than a Listbox? Anyone have any ideas?

You're unlikely to find alternatives, because Access is table-oriented and recordset-oriented by design. It is not array-oriented.

DCrake provided you some code that does string concatenation in a loop - I mentioned recordsets because string concatenation can be slow (depending on how much data) - I don't know if you have enough data for it to factor here. My point is that the code that you will use to populate the array could perhaps be used to populate a recordset instead which you could then bind to a listbox. Here's some sample code (hope I got this right).

Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Fields.Append "FirstName", adLongVarChar, 25 'SIZE REQUIRED !!!
rs.Fields.Append "LastName", adLongVarChar, 25
rs.Open , , adOpenStatic, adLockOptimistic
rs.AddNew
rs("FirstName") = "John"
rs("LastName") = "Smith"
rs.Update 'to solidify the newly added record
set listbox1.RecordSet = rs

Well, actually I forgot there's more code that you should be aware of, such as

listbox1.ColumnCount = 2
listbox1.ColumnHeaders = True
listbox1.ColumnWidths = " 1.5in; 2.5in; "

ShredDude · Jan 26, 2009

DCrake:

Thanks for that. So, you can add to the rowsource with the additem method each time through your loop for each row... In my original version I thought I'd hit a constraint because I'd read that the rowsource can only be a string up to 255 characters when you have rowsource type set to Value list. that would never work for my approximately 2800 element array. Thanks for the mod to my initial attempt. I'll use that.

In reading the help file, I saw that you can assign a User Defined Function to the RowSource Type field. I tried assigning an array to my UDF output, put couldn't get that to work either. I then tried assigning a VBA UDF that has a single integer as an output, and that didn't seem to fly either.

Jal:

Building my "array" as a recordset seems to be a good option too. I'll probably try both methods, building the RS and appending the Record Source string with a loop, to see which I like better. I'll post back as to the results.

Thanks for the inputs.

jal · Jan 26, 2009

ShredDude said:
In reading the help file, I saw that you can assign a User Defined Function to the RowSource Type field.

That sounds like a reference to the weird code discussed above. I doubt very many developers would want to use such code.

ByteMyzer · Jan 27, 2009

jal said:
That sounds like a reference to the weird code discussed above. I doubt very many developers would want to use such code.

On the contrary, I've found it to be quite useful. I've attached an example in A2K format.

ShredDude · Jan 27, 2009

Awesome! Thanks for that. Using the acConstants in the Select Case I was able to follow along better than the MS page's example.

I guess I'm still not clear exactly though. I see that it works, but I'm trying to understand the why and how of it. In this code,

Code:

Private Function fRowSource( _
    fld As Control, _
    id As Variant, _
    row As Variant, _
    col As Variant, _
    code As Variant) _
    As Variant

Dim rs As Object
Dim X As Long, Y As Long

Select Case code
    Case acLBInitialize
        Y = 0
        ReDim var(1 To 5, 1 To 400)
        For Y = 1 To 400
            For X = 1 To 5
                var(X, Y) = X * Y
            Next X
        Next Y
        fRowSource = True
    Case acLBOpen
        fRowSource = Timer
    Case acLBGetRowCount
        fRowSource = UBound(var, 2)
    Case acLBGetColumnCount
        fRowSource = UBound(var, 1)
    Case acLBGetColumnWidth
        fRowSource = 800
    Case acLBGetValue
        fRowSource = var(col + 1, row + 1)
    Case acLBEnd
        Erase var
End Select

End Function

does this suggest that when Access is opening the form with the Listbox, that is iteratively passing arguments to the function you've placed in the rowsource type for that listbox, changing the"code"? And then based on the responses to the case of the col count and row count, iteratively queries the function with the acLBGetValue code until it has all the elements of the array?

I'm a little lost on how the arguments:
fld As Control, _
id As Variant, _
row As Variant, _
col As Variant, _
code As Variant)

arrive to the VBA function. For example, if I use a function for a controlsource on a textbox I might pass arguments myself, for example fGetTextValue([Date], [OtherThing]). But I don't see any such reference in the properties of the list box, just "fRowsource".

One other quick question. What if I wanted the column widths to be different by Column? Can I do this in the code? Or am I better off setting it once with a string containing the various widths?

Can you point me to some reading on this? I'd be interested in understanding it.

Thanks again,

Shred

jal · Jan 27, 2009

ByteMyzer said:
On the contrary, I've found it to be quite useful. I've attached an example in A2K format.

But why would anyone want less-readable code? If I can accomplish the same thing using traditional loop syntax, why choose counterintuitive coding?

That's all I was asking - and still waiting for an answer. Is there some big payoff here as yet unkown to me?

ByteMyzer · Jan 27, 2009

jal said:
But why would anyone want less-readable code? If I can accomplish the same thing using traditional loop syntax, why choose counterintuitive coding?

That's all I was asking - and still waiting for an answer. Is there some big payoff here as yet unkown to me?

The main advandage is... SPEED. No so much with the time it takes to populate the variable for the listbox, but the time involved in enumerating the listbox itself.

For example, if you were to use the .AddItem method to generate the listbox's RowSource, with the RowSourceType set to Value List, with 1,000+ records, scrolling through the listbox becomes extremely sluggish.

If, however, you use the custom RowSourceType as in my provided example, scrolling through the listbox becomes much quicker and easier.

ByteMyzer · Jan 27, 2009

ShredDude said:
Can you point me to some reading on this? I'd be interested in understanding it.

RowSourceType Property

RowSourceType Property (User-Defined Function) - Code Argument Values

LPurvis · Jan 27, 2009

I'd have to chip in that immediate "readability" of code isn't always particularly important.
Once you have working code in place (often in a standard function that you use from various locations) then IMO it can be as complex as it needs to be - you'll rarely, if ever, have to look at it again. (I don't feel particularly constrained in considering future readers of the code other than good commenting - if the function's complex comment for yourself in six month's time! ;-).

In this case concatenating a string (which is analogous to using AddItem) may look simple but I agree with ByteMyzer that string based lists are sluggish - it's one reason why I personally favour assigning a recordset to the list control.
(This also affords us the ability of subsequently aquiring that same recordset from the control for further manipulation without further creation and overhead - even though the CallBack should have the initial speed advantage).

For example you can easily re-sort the list controls list by accessing it's recordset and re-assigning it.

As a by-the-way, the limit for a Value List (since Access 2002 when recordset binding was introduced too) is approx 32K chars. Prior to that it was 2K :-s
(In which case I'm sure you'll see what a good option Callbacks offered).

Cheers.

gemma-the-husky · Jan 27, 2009

not sure what you looked at exactly

you may have looked at a list call back function

-------
i have used them to fill cboboxes with dates to select from, and to fill a list with discrete quantities, appropriate for varying box sizes

jal · Jan 27, 2009

ByteMyzer said:
The main advandage is... SPEED. No so much with the time it takes to populate the variable for the listbox, but the time involved in enumerating the listbox itself.

For example, if you were to use the .AddItem method to generate the listbox's RowSource, with the RowSourceType set to Value List, with 1,000+ records, scrolling through the listbox becomes extremely sluggish.

If, however, you use the custom RowSourceType as in my provided example, scrolling through the listbox becomes much quicker and easier.

Ah....I see. Very good. I'll make a note of that. Thanks!

Fill Listbox with 2-D Array (1 Viewer)

Registered User.

AWF VIP

Remembered

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

AWF VIP

Attachments

Registered User.

Registered User.

AWF VIP

AWF VIP

AWF VIP

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread