Listbox does not show all records from a query

  • Thread starter Thread starter Bakema
  • Start date Start date
B

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
 
Bakema said:
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?
Click to expand...

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.
 
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 said:
Great, seems to work!, although I have honestly no idea
why.
Click to expand...

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].

 
Back
Top