Way too Complex cascading combo boxes

[AccessNovice7]

Hello,



thank you for taking your time to read my post, I am in hopes someone can point me in the right direction, or at least tell me if what I am trying to accomplish for work is possible. I think I am making this way too complex and that has made the design overwhelming. So here goes...

I have one table (created by importing an excel workbook) that has the following fields: Region number, locality, product Id, product descr, vendor, product type, fiscal year, qty, cost. I want to have a form that allows me to select multiple ways to view the data. I created a query that includes all fields from my table except the cost and qty. I then created an unbound form with a combo box, rowsourcetype= field list. Based on what field I select, I have a list box that populates the distinct values for that field. This is working. But I am stuck on how to do the next step and think my goal may just be too complex.

Goal: be able to summarize data in the table in multiple ways - the starting point is what field is selected, and then filtered by the connected list box. But I what to further define what order the remaining fields are included. Which essentially changes the groupings and how the qtys and costs are summarized. So if I select region, then I wanted to be able to pick which field next (basically duplicating the first combo box but excluding the field that was selected there) and so on. Each successive combo box would show the fields that have not been selected in previous combo boxes and the corresponding list box would cascade from the previous list boxes. All of the list boxes would have an "all" option.

Multiple outcomes:

by locality, by fiscal year, by product type, by vendor
by locality by vendor by fiscal year by product type
by vendor by product Id by locality by year
Etc

i think I have just made it too complex - is this even possible? Maybe it just doesn't even make sense to do it this way but now I have myself overwhelmed with the complexity so that I can't see what does make sense. Can anyone help me get on the right track?

thank you

[June7]

I think you will need a report for each grouping configuration. Give users a choice of reports to generate, not try to let them 'build on the fly'.

[Missinglinq]

I agree with June7; when you start talking about grouping data you're talking about Reports, not Forms.

Linq ;0)>

[AccessNovice7]

That makes sense to me, but i don't think it will meet the requirements i have been given. the owners basically want to pick their options and have it display the results on the screen, then be able to reselect their options, maybe my characterization of grouping is incorrect? Maybe it is more like refining the filter to greater or less precision. Say for example, if I select region, then I have the option to select all or certain regions from the cascading list box. Now my data would be filtered down to just those regions. Then say I have the option to select any of the other fields (if I could figure out how to do that). So lets say I picked vendor. The list box attached to that combo Box would only display vendors for that region. So now my data would be filtered by the selected region(s) and the selected vendors for that region. Then say I have the option to select any of the remaining fields. So say I select product type which would display a list box of the product types. So now my data would be filtered by the selected region, selected vendors in that region and product types for those vendors. What I need to do is allow the order that they are filtered to be selected by each successive combo box/text box but also to generate results at any point in the process. But it seems like it is more defining a variable amount of filter criteria in varying order. Do I want to see the data by region or by vendor or by year (etc). Maybe I need to do summaries by each field with a button to display detail (that generates a report) in order to get the grouping as you recommend. Is is possible to achieve this in a form by programically creating and displaying a pivot table with the criteria and order selected? I am quite overwhelmed by it at the moment.

[ssanfu]

There are a few things that are unclear to me.
I understand you have to be able to select the data you want to see in a report, so first you have to create a form to select the records.

I'm going to jump around so hang with me....

Each successive combo box would show the fields that have not been selected in previous combo boxes

Not hard. I've done that but using names in a table. Haven't tried with field list.

and the corresponding list box would cascade from the previous list boxes.

Whoa... where did the list boxes come from and what are their purpose?

the starting point is what field is selected, and then filtered by the connected list box

"Filtered" means removing records from the recordset (not table). So instead of having 20 records, after filtering you only have 15 records displayed.
Yes?

Multiple outcomes:

by locality, by fiscal year, by product type, by vendor
by locality by vendor by fiscal year by product type
by vendor by product Id by locality by year

Do you mean ordering (sorting) the records by these fields or do you mean changing the order of the field in the table?

---------------------------------------------------
Here is an beginning overview of what I envision...

There is a table - "Table1"
There is an unbound form, "Form1", with unbound combo boxes. You select the order of the fields you want to see from the combo boxes.

Right now, the order you select the fields is also the sort order. And no filtering of the recordset.

There is a saved query, "Query1", with all of the fields from "Table1" in it.
There is a button, (yes - you guessed it -) "Button1". The code behind the button creates a SQL string on-the-fly from the unbound combo boxes on "Form1".
The column names in "Query1", no matter what the 'real' field name, will always be aliased as "Field1", "Field2",..., "Fieldx"
This SQL string replaces the SQL of the saved query "Query1".

The record source for "Form2" is "Query1".
The default view for "Form2" is set to continuous forms.
The text box controls in "Form2" are bound to the query columns "Field1", "Field2",.....
The labels above the text boxes are set to the 'real' field names in the code from the combo boxed on the hidden form "Form1".
The labels and unused text box controls visible property could be set to FALSE if the field is not displayed. ie, you have 7 fields, but only selected 5 fields for display.

The "Button1" code would also hide (not close) "Form1" when "Form2" opens. The close button on "Form2" would unhide "Form1".

Of course, you would use your 'real' object names, not my ridiculous names-> Form1, Form2, Button1, etc.

Don't even want to think about the report


Whew...Sorry about the book. It is a big vision.....

[AccessNovice7]

Wow, I am really tired, not sure i can get my head around what you have shared right now. But to answer/ clarify...

1.) The list boxes list the values for the field selected in the combo Box. So if I pick vendor first, then all the vendors would show up in the list box. I have a code to "select all" that I found on the web with permission to use that shows at the top of the list. It is a multi select list box so I can pick just the vendors I want to see or all of them (or region or whatever field I selected in the combo box)

2.) Yes, that is what I meant by filtered

3.) neither, I don't think anyway. The selections tell me different things. So depending on the selections I might be wanting to see all costs for location by product type. So I would select location in the first combo, refine it by selecting which locations or all in the list box, the select the product type in the second box and refine it by that list box. But the next time, I want to see all costs by region by vendor by product Id. So I would select region from the first combo, filter it in the list box, then select vendor in the second combo, filter it in the list box, then select product Id from the third combo box and filter it. Each list box would only display what is available from the preceding choices (a product that is not used in a region selected or vy a vendor used in that region would not show on the list.

i was wondering if I could create a separate query for each initial choice in combo one and use that query for different sub forms (hide and unhide based on combo one choice) and place the list box choices as the query criteria. But I don't think I really followed your recommendation (probably above my pay grade! But I have also looked at this for a very long time and have made it pretty complex. But I will study it and see if I can get my head around it.

[AccessNovice7]

Oh, so sorry, thank you all for replying,

[June7]

Your original post indicated a desire to 'summarize' data by various groupings. Your later posts sound like just filtering records. Two very different things.

I do have a form where user can select a field name in one combobox then a second combobox will present data from that field. I have two pairs of these cascading comboboxes. A field selected in first pair does not show in the second pair. Then I also have another set of controls where users can select either SampleNumber or EnterDate field and enter a range in two textboxes. And another text box where they can select a TestNumber. Any combination of the parameters can be selected for criteria. However, the only operator used is AND, no mixing in OR.

Does this sound like what you want to do? The code does get complicated.

An alternative is to have a parameter input control for every field. I have seen this done as well.

[AccessNovice7]

Good Morning June7. Thank you for your reply

you are correct, I was not communicating this clearly, I apologize. I also have in my head the look of a report but I know it doesn't work the same way in a form. What you describe about your form does sound like what I am trying to achieve. Would it be possible to see a sample of your code and/or form? I haven't been able to figure out how to get the second combo box to show the field list excluding what was selected in the first combo box. Can you point me in the the right direction?

[June7]

See http://allenbrowne.com/ser-62code.html - this code demonstrates building filter criteria.

My code for the cascading controls is rather involved. You can download my db from https://app.box.com/shared/r8nea07sng

It is split design. Hold down shift key when opening frontend and reset links. Then run Login form. Enter your initials. From Menu form click Manage Samples. This is where the cascading controls and code are located.

[AccessNovice7]

Thank you, I'll post back after I have had a chance to study what you posted, though I agree..it looks rather involved!

[AccessNovice7]

I have run into a bit of a snag. My first combo box/associated list box works fine. In my second combo box I have to select something twice to get its associated list box to populate correctly (filtered by the first list box choice). I then select items from this list and run a command button to put the choices in my query. It is returning "". I used the exact same code (changed for the second set of controls) to put the first list boxes in a query and it works fine so I am not seeing what is wrong with the second set (or why I have to select two times in the second combo box to make the second list box to populate correctly. Anybody have any ideas? Thank you

[June7]

No idea. Would have to review code.

[ssanfu]

Can you post your dB with a few dummy records so we can see what the structure is like?

[AccessNovice7]

Ok, will have to make some changes since it is work related....so will upload as soon as I can do that, thanks