Access 2007 - Max items in listbox

[MrDeej]

I have just created a listbox function which fills different listboxes with data. Before we used a SQL wich appered in the listbox. But since we have experienced locking issues with this we converted the rowsource to valuelist and added items to the listboxes instead.

But now i have a different challende, it seems like it is a max size of the items added to the listbox. Since we have so many columns it only posts around 100 rows to the listbox before it quits. I would prefer around 1000 (from a table with 1 200 000 rows, to give you the perspective).

Is there a workaround for this challenge?

[TheSmileyCoder]

This is probably not the best solution, its a simple workaround. Change the listbox to a subform, with a checkbox to indicate the selected items. You will then have to write some code to store/set the selected items.

[MrDeej]

TheSmileyOne: I'm sorry, but i didnt understand your post

[TheSmileyCoder]

I presume the purpose of the listbox is to allow you to select multiple items?

If you make a continous subform, on your main form, to replace the listbox, and then add a checkbox which can indicate whether or not the particular record is selected, you will be able to select multiple items.

Now you would need some logic to keep track of which of the subrecords is selected, and store it somehow. If you want to go with an approach like this (which is really a workaround) and I understand your need for having such a listbox, then I can help you with some code.

Maybe you should start be describing why you need to have 1000 items shown in a listbox. What are you trying to accomplish?

[MrDeej]

Ok.

The form with the big listbox on it is used to display all products-transactions that have been done. We have 900 different produkts and each product have 10 different lokations which they can be in our production process.

The yellow fields is used to filter the list, either based on [field].number, [field].[product name], [field].[type of product],.........,[field].[transaction timestamp]

Like this for example

This we use to track the product if there are any abnormalities to the process from ordering to finished production. When we do this we mostly use the product number to se all the transactions that have been done, sometimes we have to be able to search trough alot of rows to find the cause, and sometimes we just want to se the last ones. We also sort by lokation instead of product number often.

This is the main function of the list, and i have never seen a form which gives the same kind of advantages in information viewing like this.

We also can dobbelclick on the list and a product information form appears which shows all data on the spesific product:

[TheSmileyCoder]

Could you not make the list as a datasheet, and then add filtering options to a set of textboxes? (I don't really know Access 2007 and whats added in that, so maybe there is an easier way)

Im not sure if this limit your seing in your valuelist can be worked around within the valuelist, or if the correct choice is to use a datasheet, or convert your list back to being SQL based, and solve the locking issue.

With what I understand your need to be:
1) The ability to view and filter several records
2) The ability to select a single record, and view further details about that in a SEPERATE form

With my knowledge of Access 2003, I think you should be using a datasheet type form, instead of a listbox, unless there are some fancy new features in 2007 for listboxes.