list box size restraints

[dogdays]

Win 7 32 bit pro, Access 2003
I have a list box with 404 rows, total size of the value list I am using for the rowsource is 42,059 characters. I get Error 2176, The setting for this property is too long. What are the maximum for the number of rows and length of the value list used to populate the list box?
Google tells me max rows is about 65K, no mention of value list size.

tia Jack

 

[Norie]

Jack

Why are you using a value list of that length to populate the listbox?

Where is the data coming from?

I know in earlier versions of Access you had to use a value list sometimes if there was no data source, but for some time the AddItem method has been available.

Perhaps you could use that to populate your listbox?

 

[dogdays]

Norie:
The additem method requires the listbox to use a value list. I suspect that the same size constraints will apply. I will try it out and get back to you. I used VBA code to populate the list box with data from a table. I had to massage the data and convert some data so I figured VBA was the way to go.

Jack

 

[Norie]

Jack

You have to use Value List for the Row Source property but you can add the items individually.

There might be limitations but I was able to add well over 1,000 items - I stopped the loop after about 5,000.

Mind you the more I think about it I'm wondering what exactly you are populating the listbox with?

Also, what data massaging/pampering are you doing?

Anything you couldn't do in a query?

 

[dogdays]

There are 11 columns in the listbox. I tried using additem but it died at row 304. The conversions are numeric codes -> alpha descriptions, pampering is combining first and last names. I used VBA because I am more comfortable with it than with queries. Guess I will have to get friendly with queries. Thanks for your input.

Jack

 

[Norie]

Jack

If you can do that sort of stuff in VBA you should be able to do it in queries.

Queries use the VBA string functions/operators, eg Mid, Left, & etc

Which should cover the names I think.

The 'conversion' should almost certainly be done in a query - that's the sort of thing they are made for.

Of course that does depend on how your data is structured.

For example is there a table of codes with their text descriptions?

How were you using AddItem anyway?

 

[dogdays]

Norie:

The conversions are quite straight forward, a numeric code converted to a description. I used the additme by building a row with interspersed semi colons and an ending semi colon, then added that string. The results looked a bit strange but since it failed with the size message I did not bother to pursue it. I am currently trying to learn about queries.

Jack

 

[Norie]

Jack

How do you know what code to convert to what description?

Listboxes in Access really are meant to be used with some sort of data source.

I don't think I've ever used a value list to populate a listbox with entire records, which is what it appears you are sort of doing.

You really should learn about queries, the do play quite a large part in databases.

 

[dogdays]

Norie:

A bit of background. This is a volunteer project for The Redwood Library, a privaet membership library, one of 18 in the US. With a market of only 18 no one is rushing to write software for them. The particular area I am having problems with is Acquisitions. How libraries get books (other than from donated books). There are 2 methods, purchase and lease, most are leased, with a buyout option at the end of the lease. I have a table tblAcquisitions which controls this, there are fields for price, title etc. There are also vendor codes, fund codes (which funds are paying to lease/buy books) and publisher codes (these must be converted).
One concern I have about queries is that I allow the user to SELECT and ORDER BY at run time. I am not sure how to do that in a query. E.G. there status code column which the user can select New. Open, Received, Closed acquisitions multiple selection is allowed. The user may select one of 5 fields to sort on or the user can be presented with a list of the columns in the tblAcquisitions and can build any combo of columns and ASC or DESC for the ORDER BY clause.

The list box is populated from tblAcquisitions, using other tables to decode the fund,vendor and publisher codes. When the list box is populated the user may add a new acquisition or edit an existing or delete an existing.

Jack

 

[boblarson]

The listbox row source should NOT be a Value List UNLESS the items are extremely static - like Yes, No, Maybe or Male, Female, Undefined, etc.

If the items can be added to, they should be in a table. So, what is the reason for using a listbox instead of, say a subform, so that you can enable editing there. But doing a listbox you can manipulate the SQL any way you want in code and a good place for some code which will help with that (changing the Sort Order or other PARTS of the SQL string is here - thanks to Access MVP Armen Stein at J Street Technology):
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
Select the J Street SQL Tools (there are more good tools on this page too).