Size limitation in a list box 2

[byurow]

Hey,

I have a list box that is only showing the first 16 records (of 46) is there a size limitation on list boxes that anyone is aware of?

I pull the data into the list box from a query. When I run the query I get all 46 records, but the list box only shows the first 16!

Any info would be greatly appreciated!

Thanks!

Brenda

 

[SteveR77]

Brenda,

Can you page down through the options the listbox shows?

If memory serves the limit for the number of items in a listbox is 32K.


HTH,

Steve

 

[byurow]

Hey Steve,

Thanks for the quick reply, I am home now, so I will have to check tomorrow! I'll let you know, but that does make sense. I will try deleting one of the columns the list box is bringing in to see if more data shows up.

Thanks again, and I will let you know tomorrow morning.

Brenda

 

[TheAceMan1]

How are ya byurow . . . . .

I believe what your interested in is the List Rows property of the CB. Here you set the number of rows that show (in view) in the listbox portion. A scroll bar will appear if the number of records from the Row Source exceeds the List Rows property.

Note: the default is 8 rows.

Note: your really only limited to how many rows you can see before the ListBox is expanded the full height of the screen. The more rows you specify, the more the ListBox elongates until it spans the height of the screen.

Look to the Format Tab of the control to find it!

TheAceMan

 

[byurow]

Hey AceMan and Steve,

Thanks for the replies, sorry for the delay but I spent yesterday putting out fires. So, I have now come back to my problem. I am looking at the ListBoxes properties and don't see anything that would indicate either a limitation on size or rows. As to my data, I can only scroll down so far. The number of rows I am allowed to see varies depending on the data I am pulling. For example, I have 7 columns pulled into the UNBOUND listbox. For some data, all 7 columns are filled in in others only 4 or 5 columns have data. So, depending on the data being pulled I may only see 17 records or I may see up to 30 records, but I am not seeing all of my records.

This is what I am trying to do....maybe you have a better idea.

I have a form that tracks training classes. The class information (date, location etc.) is entered on one tab and the attendee information (name, company worked for, address, phone ect.) is entered on the second form. The classes all have different numbers of attendees. For example on Feb. 11 we had a class that had 10 attendees. On Feb. 12 we had a class that had 46 attendees. Sometimes the class rosters are filled in accurately (meaning all columns are filled in) others we only get name and company name. So, my data is already a mess. Now what I have done is create an unbound form with 2 tabs as described above. When an attendee is entered and the user clicks save, the attendee's information is loaded into the list box so that the user can see all attendees who attended the class. Now, if the user needs to edit the attendees information (fix a typo or whatever) all he/she has to do is double click on the record in the list box and the information fills into the text/combo boxes where he/she can do the editing. What I have found is that not all records are being displayed in the list box! I have created a work-around by creating another form where the user selects the class from a combo box then selects the attendee from a combo box and is then able to fix the data. This is pretty combersome and cludgy!

So, does all this make sense and do you have any suggestions?!?

Thanks!

Brenda

 

[TheAceMan1]

byurow . . . . .

Sorry for getting back so,late. Hungup in solving comitments to other threads. I apologize . . . I'd thought SteveR77 who is certainly one of the Guru's here at Tek-Tips, would have picked you up. Rest assured he's doing the same thing . . . resolving comitments to other threads. So I'll do what I can in his absence . . . . .

First, your explanation of what you want to do appears to me to be academic as far as your post origination is concerned . . . .

So I ask this important question: Are you saying . . . . the query you use as the row source for the listbox, returns 46 records, but despite scrolling in the listbox you can only see 16 records?

Despite your answer, post the query if you can, so I can perform an analysis for resolution! . . . . .

TheAceMan

 

[byurow]

AceMan,

Yes, you are correct. My query pulls up all 47 rows, but only the first 16 appear in the list box. Below is my code from the query:

SELECT TblParticipantList.TrainingCourseID, TblParticipantList.ParticipantListID, TblParticipantList.ParticipantName, TblParticipantList.TitlePosition, TblParticipantList.CompanyNameOrAgency, TblParticipantList.CompanyAddress, TblParticipantList.CoCity, TblParticipantList.CoState, TblParticipantList.CoZip, TblParticipantList.CoPhone, TblParticipantList.ParticipantObserver, TblParticipantList.CellPhone, TblParticipantList.FaxNumber, TblParticipantList.PagerNumber, TblParticipantList.EmailAddress, TblParticipantList.Description, TblParticipantList.Training_Date, TblParticipantList.Training_Name, TblParticipantList.LLS, TblParticipantList.HTUA, TblParticipantList.OLDID, TblParticipantList.PS, TblParticipantList.PM, TblParticipantList.HTUAID, Nz([TblHTUA].[City]+", "+[TblHTUA].[State]) AS HTUA
FROM TblHTUA RIGHT JOIN TblParticipantList ON TblHTUA.HTUAID = TblParticipantList.HTUAID
WHERE (((TblParticipantList.TrainingCourseID)=[TrainingCourse_No]));

The code to populate the list box is as follows:

Public Sub FillLstParticipantInfoListBox()

Dim qdfParticipant As QueryDef
Dim rsParticipant As Recordset
Dim blnNumParticipant As Boolean

plngTrainingCourse_No = Me.NumTrainingCourseID

On Error Resume Next
Set qdfParticipant = CurrentDb.QueryDefs("QryFindParticipant")

qdfParticipant.Parameters("TrainingCourse_No").Value = plngTrainingCourse_No
Set rsParticipant = qdfParticipant.OpenRecordset
blnNumParticipant = rsParticipant.BOF And rsParticipant.EOF
Me.LstParticipant.RowSource = Empty
DoCmd.Hourglass True

Do While Not rsParticipant.EOF
Me.LstParticipant.RowSource = Me.LstParticipant.RowSource & """" & rsParticipant!ParticipantListID & """;" _
& """" & rsParticipant!ParticipantName & """;" _
& """" & rsParticipant!CompanyNameOrAgency & """;" _
& """" & rsParticipant!CoCity & """;" _
& """" & rsParticipant!CoState & """;" _
& """" & rsParticipant!ParticipantObserver & """;"


rsParticipant.MoveNext
Loop

rsParticipant.Close

Set rsParticipant = Nothing

DoCmd.Hourglass False

End Sub


Any help would be greatly appreciated! By the way, using the same query, if I populate a combo box with the same query and same columns all of the data shows up, not just the first 16 records! Also, if I remove some of the columns from the list box I get more records displayed. It really seems to be a size limitation of the list box properties. I hope there is some way to increase the default setting if that is the case.

Thanks again!

Brenda

 

[TheAceMan1]

Hang in there byurow . . . .

I may not be able to look at it until this evening . . . . but rest assured I will get back to ya! . . . .

TheAceMan

 

[byurow]

Thank you!

 

[TheAceMan1]

OK byurow . . . . .

Wow! . . . . on first analysis, your going around the world 100 times to accomplish what you can do across the street!

If the query gives you what you want, then set the [blue]Row Source[/blue] for the ListBox to the query! Disable your code (Need to see what happens here) and give it a whirl.

Let me know what happens . . . . .

See Ya! . . . . . .

 

[byurow]

OK, I gave it a try and it is still only showing the first 16 records. I really think that it is a size limitation on List boxes.

Any other suggestions?????


Thanks!!!

Brenda

 

[TheAceMan1]

byurow . . . . .

Yes I have other sugestions. But first understand, I have databases with listboxes that show 25 rows in view, scrollable to the limit of rows in the query. In general for a single field view they average 12K of data.

So I can't help but believe that something is wrong.

[blue]If it were me, at this point I would delete and reconstruct the listbox (again only use the query for the RowSource & disable your code . . . you wanna simply see if the listbox does what its suppose ta).[/blue] [red]Sometimes things don't go as planned the first time around in Access.[/red]

As a question of curiosity, are you setting the RowSource to the query or an SQL statement? It could matter . . .

Don't give up here . . . . [purple]this thing should be resolvable![/purple]

See Ya! . . . . . .

 

[byurow]

Sorry for the delay in responding...this db had to be put on the backburner for other more pressing issues. Anyway, I have pretty much decided it is a size limitation. I have tried everything you suggested all to no avail. This is what happens. After having deleted and reconstructed the list box, turned off my code and set the rowsource to the query. I still only get the first 16 rows. So, I began playing with the data. The more columns I remove from the query, the more rows appear in my list box. If I go back to the original query (using all rows) and delete much of the data in the rows, again, more rows appear in the list box. All things point to there being a limitation on the size of the data in the list box.

As to my rowsource I am pointing to a query.

If you have any other suggestions I would love to hear them.

Thanks again for all your help!

Brenda

 

[TheAceMan1]

Hay byurow . . . . your alive! ;-)

Your last post certainly suggests a depenedency on data load. For all my years I've never run into this. Gotta check this out in greater depth. Will get back to ya with results.

By the way . . . . [blue]excellent testing on your part.[/blue] Very Informative! Worth more that a measly star.

See Ya! . . . . . .

 

[byurow]

I've got to say, that I have been using access for over 5 years and I have never encountered this problem either. Unfortunately, due to the nature of the application, I have no other method of displaying the data. I am still trying to find a better solution. Right now, I am pulling the data into a combo box and that is REALLY cumbersome. Oh well. Let me know what you discover!

Thanks again!

Brenda

 

[TheAceMan1]

OK byurow . . . . .

First I have to apologize for not reading your post of Mar 17 and your code of Mar 19. I was so bent on solving this unbelievable problem I missed it all! (its not my MO on Tek-Tips to skip details) . . . . I could have told ya a long time ago what the problem was. So, since I owe ya, here we go. . . . .

Your code for stuffing the data into the listbox is what alerted me to what the problem was:

[blue]In a listbox, if the [purple]Row Source Type[/purple] property is set to [purple]Value List[/purple], the [purple]Row Source[/purple] is [purple]limited to 2048 characters[/purple] of data![/blue]

Without going into detail,the list box was simply trimming to this limit!

Now . . . . for what your trying to do . . . . It appears your using the listbox as a precursor to determine if a record needs editing in anyway. I wouldn't quite use a listbox this way, but since you already have it I'll go with that. However a few questions first.

You said:

The class information (date, location etc.) is [blue]entered on one tab[/blue] and the attendee information (name, company worked for, address, phone ect.) is [blue]entered on the second form.[/blue]

It appears your using the Access Tab Control. . . In any case, [purple]just what is the structure here?[/purple] (A main form with a subform on each of two tabs) . . . . what?

Your query shows only one table but two ID's. Is this a compound PK or what!

See Ya! . . . . . .

 

[byurow]

I am still alive! Sorry about not responding earlier...as you can see this is just not a priority right now, so I REALLY appreciate the help you are giving me!

The format....I have an unbound form with 2 tabs that use the tab ctrl. The data pulls from several tables. The 2 main tables are TblTrainingCourse and TblParticipantList. Because of the amount of data I am pulling in, I found that it was just soooooo much faster to have unbound forms.

TblTrainingCourse houses the actual training course information such as name of course, date of course and location of course (among other stuff).

TblParticipantList contains the participant info such as name, company they are with, company address ect.

TblParticipant contains it's own ID (ParticipantListID) plus the Training Course table's ID (TrainingCourseID) with a many to one relationship between the 2 tables.

As you can imagine, I only want to see the participants for the class I selected (from a drop down box - this box contains the training course date and it's location). In addition to the paricipant list box, I also have 2 other list boxes which is why I found it to be much faster if I unbound those list boxes.

Thank you so much for the information on value lists - that is what I was afraid of! You definitely deserve a star for that one!

So given the structure I just gave you, how would you design this form? The list box is just an easy way to view all of the participants in one glance. I have worked around the issue by putting it into a combo box with a couple of columns. It's ugly but it works. Right now the form is just for my use and one other person, so it is functional. However, I can see that in the future the client we actually created this for (all he needs is reports) may want to see the whole db and I would be pretty embarrassed for him to see it this way! I would offer to send you the db but it is for a DOD contract and therefore I am not allowed to show it to anyone.

Some background on the db. I took over this data a few weeks ago. Up until now, it was all housed in an excel spreadsheet. The spreadsheet was updated everytime a class occurred. The client would then call and ask for statistics such as how many classes, how many participants, what demographics (cant go into detail here sorry) ect. As more and more classes occurred it became harder and harder to get those numbers. So, I then took over and was given 2 weeks to get a working db - including data! I literally took 1.5 days to create the db the rest of the time I dumped the 50,000 records into the db! So, that is why the db is such a mess! It does however give all statisics the client is looking for and can pretty much pull the numbers anyway the client comes up with as I have tried to anticipate all his needs (so far so good).

So any thoughts on how to work around the list box limitation?

Thanks again for all the help!

Brenda

 

[TheAceMan1]

Hay byurow . . . . Sorry to get back so late . . .

As a single father of three, lately they've been more demanding on time . . . . . and I have ta be there.

I havn't finished my critique on your last post (and the rest of this thread). Stil going over the schema I will present to you.

[blue]So any thoughts on how to work around the list box limitation?[/blue]

With the listbox, your only way out is to use query instead. Make a query for the listbox that returns the data you want and use that for the RowSource. Use SQL in VBA if ya need that kind of control. You can stuff an SQL statement in the RowSource as well. Don't forget to assign [purple]Table/Query[/purple] to the [purple]Row Source Type[/purple]. This will take care of the list box.

Give me a little more time and I'll get back to ya with schema. Lets say by Friday . . . .

While you designing, bear in mind, no one will mind if somethings a little slow if it works efficiently and has an easy interface. Program efficiency first, get it working, then work on boosting speed if its necessary. The more you program the more you'll initially program for speed.

For instance, Queries are always precompiled and always run faster than SQL(SQL is parsed then compiled). So I initally program with queries, then convert to SQL where needed or necessary. Bear in mind, although SQL is slower, its very powerful because its just about the only way to dynamically change query schema during [blue]run time[/blue].

See Ya! . . . . . .

 

[TheAceMan1]

Hay byurow . . . . . OK!

As an Idea, I'll tell you about by largest DB, as far as record count is concerned. It has to date just over 700k records. Remember were talking bound here!

With large DBs, of course you never load all records in a form at one time. Even with fast machines its still too slow. To cricumvent speed, I always deal with groups of data which load faster.

The DB has two major modes; data entry and edit. These major modes are selectable by tool bar buttons.

Data Entry mode is the default open mode. The RowSource is set to an SQL statement with criteria which looks for a PK of 0(a non exsistent record). So the query return no records, yet functionally,the form still allows the adding of new records. Since no records are returned by the query, it opens very fast. And as long as you do'nt close the DB, the newly added records are all you see.

Edit mode has two sub modes(selectable by a pop-up form). Whereby selection you can load a single record or group of records.

Also indexing is very important where a large record count is concerned. Set indexs to your most commonly used fields, espcially if your sorting/finding.

Your also faced with problem of selecting a record with within large record counts. I'm sure you've found scroll through 50K records in a combo/list in an extremely course afair. I either hunt for a specific record or hunt according to the selection from a group of of textboxes, tagged the same as a directory lettered A thru Z.

All this I do by dynamically changing the RowSource of the form. There are quite a few queries, but speed has never been a problem working in this way.

What ever you do, bear it mind ways to keep you data load down. (If all you want to see is a certain Class, no since in having all Classes loaded). This easily accomplised in a query with criteria!

Good Hunting!

See Ya! . . . . . .

 

[byurow]

Interesting. I had never thought of setting the PK to 0! Anyway, I can't seem to get around the fact that my employer or client want things as user friendly as possible. They like everything to be on one form (or as close to one form as possible). Due to the nature of the data in all cases so far - many, many to one relationships, I have had to make the forms unbound. I found that loading the subforms just takes too much time. By unbinding the forms I am only pulling in the specific record they wish to see, based on a selection from a combo box, or they can add a new record. The data populates through queries, just as yours does and I also have many indexes on the tables. I know that there have been alot of threads on the bound vs unbound philosophies but I can never get bound forms to work quickly and still be very user friendly. Most of my clients are not computer people and don't like to be trained. They want the db to be intuitive and simple. The only way I have been able to achieve this is through LOTS of code!

Oh well. Thanks for all of the help I really do appreciate it! One of these days I will sit down and actually play with a bound form and see if I can't get it to run faster even with lots of subforms.

Thanks again!

Brenda