Listbox does not show all records from a query

[Bakema]

Hi there,

This is a really strange one, after a full day of working
on it, I become more puzzled the longer I look at it.

A have a form with a list box that can be populated by
different queries that are programmically set to the
listbox rowsource property through selecting items from
different comboboxes. The purpose is to make selections
on the overall recordset based on various criteria. So
the queries work on the same tables, but return more or
less records depending on the criterion choosen from the
comboboxes

The queries work fine, but once in a while not all
records appear in the listbox. I can't figure out exactly
why, sometimes they do all appear and sometimes not and I
have not been able to see any pattern in it. If I run the
same query several times by selecting the same item in a
combobox several times, the number of records shown
differs each time. Strange enough, when I export the
query recordset to Excel all records appear in the Excel
spreadsheet. Any idea why a listbox would not show all
records from a query?

Thanks for giving this some thought.

Bakema

 

[Marshall Barton]

A have a form with a list box that can be populated by
different queries that are programmically set to the
listbox rowsource property through selecting items from
different comboboxes. The purpose is to make selections
on the overall recordset based on various criteria. So
the queries work on the same tables, but return more or
less records depending on the criterion choosen from the
comboboxes

The queries work fine, but once in a while not all
records appear in the listbox. I can't figure out exactly
why, sometimes they do all appear and sometimes not and I
have not been able to see any pattern in it. If I run the
same query several times by selecting the same item in a
combobox several times, the number of records shown
differs each time. Strange enough, when I export the
query recordset to Excel all records appear in the Excel
spreadsheet. Any idea why a listbox would not show all
records from a query?

I've seen that sort of thing ocassionally when it was a long
list. In those cases, I added the line of code:

dummy = Me.listbox.ListCount

immediately after the line that sets the list box's
RowSource.

 

[jamie considine]

two thoughts...

first, make sure that all comboboxes that affect the
listbox have an afterupdate event that requeries the
listbox. sometimes its easy to forget an event on one of
the comboboxes. unlikely this is the problem since you
probably checked it already.

second, if the listbox query is set referentially, i.e.
refers to the value of a combobox in the listbox control
rowsource, sometimes the combobox value is not set until
the field is exited. Consider writing a sub that
concatenates the values from the comboboxes into an sql
statement and then set the listbox control rowsource
explicitly. this sub can be called from the afterupdate
event of each combobox.

good luck.

jamie

 

[Bakema]

Great, seems to work!, although I have honestly no idea
why.

Thanks

Bakema

 

[Marshall Barton]

Great, seems to work!, although I have honestly no idea
why.

Your problem was that Access thought the list box might take
too long to load all the rows, so it farmed it out as a
background tesk. Asking Access to tell you how many rows
are in the list box forces it to read all the rows
immediately
--
Marsh
MVP [MS Access].