Dependent List Boxes do not refresh using ReQuery

[weeblesue]

I have 6 list boxes that build a filtering query. The user selects option1, then Box2 fills and the user selects option2. Then Box 3 fills based on those 2 selections, and the user selects option3. This continues until a final result is selected, based on the user's selections in the 6 list boxes.

It's all working fine, but sometimes the result is known after Box2. Meaning that Option1 and Option2 already filter out a single record. There's no need to further click on box3, 4, 5, or 6, because there's only one list item in the box.

Yet my users still have to click on those single entries to "select", so the subsequent box has a starting point to run its query.

In each list box, I have an OnClick event that sets each subsequent box to null, then Requeries the boxes. But they don't 'refresh' on the screen. The subsequent boxes show empty.

What I'd like to do is pass the resulting queries into an array. Then count the members of the array. If the count = 1, then "select" the result to show in the list box. Then move on to the next list box.... and repeat. But I don't know the code to do this.



So if my user picks option1 and option2 and that defines a single record, options 3, 4, 5, and 6 are automatically populated and the result is shown.

Any clues?

Thanks,
Susan

[orange]

Sounds like you don't need 6 listboxes. So why have them?

Can you show us your table relationships and some sample data that illustrates the condition?
It's easier to answer.\advise when there is some context to the problem.

[weeblesue]

No, I definitely need all 6 list boxes. I've attached a couple screen shots to show what I'm trying to do, and why all 6 are needed.

ListBoxHelp1 shows how the user progresses through the options to narrow down their selections. It shows that for *some* selections, even the 6th list box has options A or B to choose from. This is why all 6 are needed.

ListBox2help shows that for some *other* selections, once you select the 1st item, all the other list boxes end up with just the one option. This only happens *sometimes* and not all the time. It is for *these* occasions where I'd like the subsequent list boxes to get populated automatically.

ListBoxHelp3 shows what is going on when I'm in the middle of a selection. Once I've selected the Pressure, there is only one CorrosionAllowance option, and only one Joints option. But these don't get "selected" automatically. They're set to null and requeried, but I still must click on the Corr Allow list box to get the Joints box to populate.

I have only one table. the script behind the list box does all the Selecting.

An example Select statement is:

 
SELECT DISTINCT MainData.ServiceID, MainData.Class, MainData.[Primary Material] FROM MainData WHERE (((MainData.ServiceID)=forms!SelectForm!SelSvc) And ((MainData.Class)=forms!SelectForm!SelCls));

The script behind the OnClick event is (varied, of course, for how many list boxes come after it):

 
Private Sub SelCls_Click()
    SelMat.Value = Null
    SelMat.Requery
    SelPrs.Value = Null
    SelPrs.Requery
    SelCA.Value = Null
    SelCA.Requery
    SelSB.Value = Null
    SelSB.Requery
    SelResult.Value = Null
    SelResult.Requery
End Sub

I have to set the values to Null before the requery, or the value from the previous selection is still active.

Thank you for your time.