Filtering comboboxes by criteria in its query

[Mishanya]

In my form I have 34 combo-boxes wich lists are filtered by using the
relative previous combo-boxes' values as a criteria in its queries (like
[Forms]![FormName]![PreviosControlName]).
Each of them also has OnEnter event Me.ControlName.Requery to filter its
lists on entry.
Each combo-box result value is stored in a table.
Now, every time change the form recordset (by cboSelectRecordset), I don't
see a few combo-boxes' values, as if they were not saved. But they do exist
in the control sources' table. Moreover, when I "click" the "empty" boxes,
its values do appear as if they "wake up".
There is no consistent pattern in this behavior - sometmes all the boxes are
populated, other times half of them are "asleep".
I've solved the problem explicitly by putting the Requery command for every
box in the cboSelect AfterUpdate event, but I don't like this solution.
I wonder if there is some Access restriction on how many criteria references
one may put in the same form, or there is another problem with it?

 

[fredg]

In my form I have 34 combo-boxes wich lists are filtered by using the
relative previous combo-boxes' values as a criteria in its queries (like
[Forms]![FormName]![PreviosControlName]).
Each of them also has OnEnter event Me.ControlName.Requery to filter its
lists on entry.
Each combo-box result value is stored in a table.
Now, every time change the form recordset (by cboSelectRecordset), I don't
see a few combo-boxes' values, as if they were not saved. But they do exist
in the control sources' table. Moreover, when I "click" the "empty" boxes,
its values do appear as if they "wake up".
There is no consistent pattern in this behavior - sometmes all the boxes are
populated, other times half of them are "asleep".
I've solved the problem explicitly by putting the Requery command for every
box in the cboSelect AfterUpdate event, but I don't like this solution.
I wonder if there is some Access restriction on how many criteria references
one may put in the same form, or there is another problem with it?

Have you recently installed Access 2003 Service Pack 3?
Could be the Service Pack 3 bug.
See this link for details on the hotfix.

http://support.microsoft.com/kb/945674

 

[Mishanya]

Now I've even rounded the solution up by putting

Dim ctl As Control

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Requery
End Select
Next ctl

in the cboSelect AfterUpdate event (thanks to BaldyWeb!!!), but I still feel
like "raping" the form instead of treating it properly.

 

[John W. Vinson]

In my form I have 34 combo-boxes wich lists are filtered

That's an extraordinarily deep nest of conditional combos! What's the data? do
you in fact have a 34-level heirarchy?

 

[Mishanya]

Hi John! Nice to have You on board
No, You havn't got me right. Each combo is nested only 1 or 2 levels back
(has 1 or 2 criteria references in its query). In All I have 34 such boxes -
that why I thouhgt the problem might be with the engine not succeeding to
"catch" all the referenses at once.

 

[Mishanya]

Hi Fred.
No, I have not installed Access 2003 Service Pack 3 - my version is "virgin"
11.0.5614.0.
I did try to install the hotfix - but it woud not work, as I don't have the
Pack installed - so it has nothing to apply to. Although the adviced Hotfix
features do seem to handle exactly the problem I have.

In my form I have 34 combo-boxes wich lists are filtered by using the
relative previous combo-boxes' values as a criteria in its queries (like
[Forms]![FormName]![PreviosControlName]).
Each of them also has OnEnter event Me.ControlName.Requery to filter its
lists on entry.
Each combo-box result value is stored in a table.
Now, every time change the form recordset (by cboSelectRecordset), I don't
see a few combo-boxes' values, as if they were not saved. But they do exist
in the control sources' table. Moreover, when I "click" the "empty" boxes,
its values do appear as if they "wake up".
There is no consistent pattern in this behavior - sometmes all the boxes are
populated, other times half of them are "asleep".
I've solved the problem explicitly by putting the Requery command for every
box in the cboSelect AfterUpdate event, but I don't like this solution.
I wonder if there is some Access restriction on how many criteria references
one may put in the same form, or there is another problem with it?

Have you recently installed Access 2003 Service Pack 3?
Could be the Service Pack 3 bug.
See this link for details on the hotfix.

http://support.microsoft.com/kb/945674