Populate listbox with varying number of records and column count

General
1

Hi. I’m looking for a way to populate a listbox. The number of records will vary from as little as 10 and up to hundreds. I want the number of rows to be limited to 25, and then the next column would contain the next 25 records.

Say the number of records is 60. I would calculate the number of columns to be 3:

LBColCount = (data.RecordCount / 25) + 1

I want the first column to be records 1 to 25, the second column to be records 25 to 50, and the third would be 51 to 60 (there would be some empty fields in the last column, and I am ok with this)

How can this be set up to choose the first 25, the next 25, and so on? I’m getting the records from a database and then appending to an array. I just cannot figure out how to select x amount and then move onto the next amount without the array going out of bounds.

This is ultimately going to be used to create a PDF table using MBS DynaPDF. I already have the function to make a PDF table, but the problem is the report would be several pages long with a source of 100+ records, and I wanted to try and get them all onto one page if possible. If anyone has a better solution (possibly not even using a listbox and PDF table) for the report, please advise.

Thanks!

2

Hum… I would try to prototype what it will look like using a spreadsheet, before coding that. If it were me, my little simplistic brain would have difficulty with having three different records on the same line.

Now, to address your question. If you define a two-dimensional array, you can easily read your database records and fill, say, A(0,0) to A(24,0) for the first 25 records, A(0,1) for the next 25 records, A(0,2) to A(24,2) for the next 25. If you have more than 75 records, then you return to the first column of the array: A(25,0) to A(49,0) and so on. The key is to dimension your array with the correct number of "rows"and “columns”. The number of “columns” would be what you calculated as you already indicated. If you want, you can also redimension on the fly. It is a slow operation, so dimensioning correctly at the start is better. You manage the array with a “row” variable and a “column” variable that you use as counters. You already have the number of records, so calculating the size of the array should be pretty easy. It is the upper rounded value of the number of records divided by the number of columns. (minus 1 if you want to be very strict) If you go out of bounds with this approach, verify that your database (driver) returns a correct recordcount property.

Unless you are going to display on-screen, there is not really a need for a listbox. That said, if you want to display, then you add rows to the listbox by simply counting through the array. Something like:

For RowCount 0 to TotalRows Listbox1.AddRow A(RowCount,0), A(RowCount,1), A(RowCount,2) Next
This is just an example, I did not run the code in the IDE to make sure it actualy is correctly written. You will have to make sure TotalRows is the right number, that all variables are correctly declared, etc.

So, it is all pretty simple. As I said earlier, I am very unsure about the usability of the design. As a user, I would find it very difficult to understand and to use if I had several unrelated records on the same line. But you know your data and your users. You know best.

3

You want no more than 25 rows

So :

[code]dim numberrows as integer = 25
dim numbercolumns as integer = 1

if totalrecords < 25 then numberrows = totalrecords[/code]

Lets assume there are more than 25 rows.
How many columns do you need?

numbercolumns= totalrecords div 25

Now set the listbox to have the right number of columns:

myListbox.columns = numbercolumns

Add 25 rows:

for x = 1 to 25 myListbox.addrow "" next

Then fill in the cells:

for x = 1 to totalrecords mylistbox.cell(x div 25, x-1) = thearr(x) next

untested, but give it a go…?

4

Thank you both for the replies. I used Jeff’s method and modified it a bit to get to work properly. Adding below if anyone else needs to do the same.

With this method, the number of columns needed is calculated based on the total rows of data. 25 rows are created first, then the end method fills the first column with the first 25 records, and then resets the intRow to 0 and adds 1 to the intCol to move to the next column. This continues until the data has reached the end. The data is taken from a db table which then fills the CodeArray

[code] dim numberrows as integer = 25
dim numbercolumns as integer = 1
dim x, y, intRow, intCol as Integer

	if data.RecordCount < 25 then 
			numberrows = data.RecordCount
	end if
	
	'How many columns do you need?
	numbercolumns = (data.RecordCount / 25) + 1
	
	'Now set the listbox to have the right number of columns:
	Listbox3.ColumnCount = numbercolumns
	
	'Add 25 rows:
	for x = 1 to 25
			Listbox3.addrow ""
	next
	
	intRow = 0
	intCol = 0
	
	'Then fill in the cells:
	for y = 0 to CodeArray.Ubound
			Listbox3.cell(intRow, intCol) = CodeArray(y)
			
			intRow = intRow + 1
			if intRow = 25 then
					intRow = 0
					intCol = intCol + 1
			end if
	next[/code]
5

Be careful to stay under the maximum number of columns for a listbox, I think it’s 62 or 63.

6

Reference for John’s note, http://documentation.xojo.com/index.php/ListBox.ColumnCount

7

huh
that ones wrong too
its 255 visible columns or so but there is a bug if you have headers where they dont draw once the total column width is > 32767

8

Thanks for the tips guys. I didn’t know there was a maximum number of allowable columns. Though, the max I think I would need for this purpose is only up to 7 columns, so I will be ok. Appreciate the help!