Efficient loading of interdependent combo box row sources?

[mscertified]

I have a search criteria form with several combo boxes. I want to postpone
loading the rowsources until I need to because they could possibly be several
thousands of rows each.
The contents of the combo boxes depends on the other search criteria.
If a user does not use a combo box I don't want to load the rowsource at
all. If they do use it, I want to load the rowsource at that point based on
the rest of the search criteria (to minimize the rows).
After a combo box rowsource is loaded, if the user changes some other search
criteria, I may need to rebuild the rowsource and requery it.
This all seems rather complex. Is there a 'best practice' way to do this?
Thanks.

 

[Allen Browne]

If the combo's bound column is the display column (not zero-width), you can
just use its Enter event to set its RowSource. The display value is always
present, and the RowSource records are only needed when the combo is the
active control (since that's when the user is trying to pick from the list.)

If the bound column is not the display column, the combo will go blank (even
if it does have a value) whe you restrict the RowSource such that Access
does not have the value to display. And if this is a continous form or
datasheet, it will go blank in all the rows where the display value is not
in the RowSource.

You also need to avoid using NotInList when the list is restricted.

Where there are too many items to load into the list, there is a technique
to delay-load the combo. You wait until the user has typed 3 or 4
characters, and then load the RowSource with records that start with those
characters. Example in:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

 

[SteveM]

Use the AfterUpdate event of each combo. You can set the rowsource of related
combos there.

Steve

 

[Pendragon]

Allen,

Good info to have - your note on blank rows points to my problem in the post
"combo box problem on subform".

Are you saying here that in order to correct the blank row problem, the
specific field must be included in the rowsource? For example, my subform is
based on a table containing only DuesID, CategoryID and DuesAmount. The
problem combo box is set to CategoryID, CategoryDescription and DuesAmount
from a database system table, and only the CategoryID is stored to the table.
In scrolling through records on the parent form, the subform is displaying
the corresponding records properly except that the combo boxes are blank. Do
I need to change the subform's source from the table to a select statement
with a join between the subform table's CategoryID and the database system
table's CategoryID, and then include the Description in the select statement?

Thanks.

 

[Allen Browne]

Yes: if the combo's display colummn is not the bound column, and its
RowSource limits it the display column does not contain the values needed
for *all* rows in the continuous form, some rows will appear blank.

Changing the RowSource from the table name (where it can get all values) to
a query (where the criteria restrict it) could create the problem.