Combo boxes filtering a subform, how do I get them to filter each other?

[SaskiFX]

I thought I was in the home stretch of my project, everything worked great when I was messing with 200 records. Now that I'm messing with 2000+ records, things are very slow. So a search form redesign!



I have a form (frmSearch) that has a subform (frmSubSearch) embedded in it. The frmSubSearch is a datasheet only that is just pulling its info from a query (qrySearch). qrySearch has about 8 columns of data in it.

On the main form I have a combo box that is feeding its list from the qrySearch using a SELECT DISTINCT statement. So a user selects an item in the combo box and my After_Update fires. This sets a filter on frmSubSearch. I have three of these combo boxes that can add to the filter and they work great so far. But I want to have the combo boxes filter themselves based on whats left on frmSubSearch. So if a user selects something in the 2nd combo box, the sub form filters and updates, but then I want the other two combo boxes to only have valid selections, and not something selectable that would wind up giving me a blank sub form result.

I have tried using .Requery in various ways, but its not helping. I have also been looking into the idea of Cascading Comboboxes, but these don't seem to quite fit what I'm trying to do.

Any suggestions? Thanks!

[June7]

If you want a combobox to offer only values that have actually been used in records, then the RowSource of combobox would have to be that data table.

[SaskiFX]

How do I get the combo box to reference the sub form data?

Currently my RowSource is set to:

SELECT DISTINCT [qrySearch].[CoreThickness] FROM qrySearch ORDER BY [CoreThickness];

And the AfterUpdate event builds the filter and does:

Me.frmSubSearch.Form.Filter = strFilter

So, am I pointing my combo box to the wrong object for the RowSource?

[June7]

I don't know your db. What is qrySearch?

I think I do something similar to what you describe.

I have comboboxes that give the user a list of values that are already in the data table, however, users can enter anything they want. The list is only an aid to help them enter long text strings that have previously been used. The comboboxes are not conditional - they are not dependent on other comboboxes.

I just use the table as the RowSource, not a query:

SELECT DISTINCT [fieldname] FROM tablename ORDER BY [fieldname];

[SaskiFX]

I'm not explaining myself well. I created a quick example DB for what I'm trying to do. I have attached it.

If you open Form1, you will see the three combo boxes I have. If in the first box for Description 1, you Select Alpha, I want the other boxes to no longer show 'Alpha' as an option, as there is no record that has 'Alpha' repeated in Description 2 or 3.

I've been trying other ways to get this working today without luck.

[June7]

That is not conventional use of dependent comboboxes.

Normally, there is a 'lookup' table for values. Consider cars - make, model, package.

Select Ford in combobox1, combobox2 lists only Ford models.

Select Escape in combobox2, combobox3 lists only Escape package options.


If you limit comboboxes to only values already in data table, how can user input a value not yet used, such as model Fusion?


I think I understand what you are attempting, I just don't see the benefit.

[SaskiFX]

No inputs, just for searching to narrow down results. I'm going to disable their ability to type into the fields.

[June7]

Oh, okay.

Combo1
SELECT DISTINCT [Description1] FROM Table1 ORDER BY [Description1];

Combo3
SELECT DISTINCT [Description2] FROM Table1 WHERE Description1 = [Combo1] ORDER BY [Description2];

Combo5
SELECT DISTINCT [Description3] FROM Table1 WHERE Description2 = [Combo3] ORDER BY [Description3];

Then need to requery Combo3 and Combo5 in the AfterUpdate events of Combo1 and Combo3:

Me.Combo3. Requery
Me.Combo5.Requery

[SaskiFX]

Ah, now I think I understand. I'm wanting these to operate independent of each other, so the selections don't necessarily have to be in order. However, now that you have pointed me in the right direction on the RowSource, I think I can code up the RowSource statement to get built on the fly. I'll update on Monday with my code and mark this solved, or with more questions.

Thanks!

[June7]

Ooops, I did not fully understand. You want to allow user to select from any combobox first then the other combos will adjust? That does sound complicated. Also sounds like a non-normalized data structure.