Listbox not showing all records

[BigJimSlade]

Hey, Big Jim here:

I have a listbox whose RowSource is set to a query. When I press a button on the form, the RowSource changes and the listbox should fill with thousands (7,000+) rows. However, it only populates the first 20 to 30 rows.

Is there anyway to force the listbox to populate with every single record in the RowSource query?

Thanks in advance....

Big Jim

 

[Fuga]

I have a listbox whose RowSource is set to a query. When I press a button on the form, the RowSource changes and the listbox should fill with thousands (7,000+) rows. However, it only populates the first 20 to 30 rows.

Are you sure the rowsource is actually changed and your form is refreshed if necessary (me.refresh)?

If so, if you run your query, does it return 7000+ records?

Fuga.

 

[BigJimSlade]

Hi Fuga,

Yes, the query is a functional query, I have tested it. Also, if I click the mouse on the scrollbar inbetween the down arrow and the bar itself (the part that moves up and down the scrollbar) I am able to eventually populate all records. Otherwise, the records do not populate.

Thanks again!

Big Jim

 

[Pat Hartman]

That is the way Access works. Forms are displayed as soon as enough data to fill a form has been retrieved even though the bound recordsources are not yet completely populated.

7,000 is a lot of rows for a combo. Can you cut it down some. Perhaps by using a cascading combo. For example select a letter from the first combo (26) entries and use that to limit the set of data pulled for the second combo.

 

[BigJimSlade]

Thanks Pat. I always appreciate when you spell out the limitations of Access. It helps Big Jim design better in the future.

I do think it is interesting however that in VB, you can populate listboxes row by row as opposed to using a Table\Query Rowsource, and the listbox will show all appended.

Thanks again!

 

[Pat Hartman]

Access is "better" than VB in that regard since Access supports bound combos. What you are working with is a bound combo. Even though combo's support 64k, their performance is certainly impacted by the number of rows in their rowsource.

In Access, if you want to populate a listbox or combo manually, on-the-fly, you would use a call-back function.

Here's a sample db (A97) with two forms that use a callback function. I can't explain it. I barely understand it. The reports form lets you select reports to be run and adds them to the right side listbox. It keeps track of what parameters are necessary and when you press the run button, pops up a parameter box so you can enter parameters. Of course, that's where the example stops working since none of the rest of the objects exist in this sample. If you need similar functionality, you can import the two forms plus the table that they use and modify them as necessary.

 

[BigJimSlade]

Thanks, Pat.

I will take a look at it first thing in the morning. I must admit that I am scared when there is something in Access that you "barely understand". That means the rest of us should stop trying.

Thanks again!

Big Jim

 

[Pat Hartman]

Hi Jim, I ran across an easy answer to your problem today while browsing through my favorite book (Access Cookbood). All you have to do is to make Access tell you how many rows the combo contains. This will force the entire recordset to load when the form loads.

Private Sub Form_Load()
    Dim lngDummy As Long
    lngDummy = cboFast.ListCount
End Sub

 

[BigJimSlade]

Wow!

Let me say that again......Wow!

Big Jim loves the easy answers! Thanks, Pat! There will be many people at my company quite relieved that they can see their entire lists at one time.

Thanks again!

Big Jim Slade