Filtering Dynamic Combo Boxes

[Topflite66]

I am fairly new to working with Access and I am working on a form that contains several dynamic combo boxes. I have 3 combo boxes, (cboGroups_Packages_1, cboGroups_Packages_2, cboGroups_Packages_3) that are generating a list from the same query, qryGroups_Packages. I would like to be able to filter out the first selected item in the first combo box from the second and third combo boxes. For example, all three combo boxes would display FX4 Pkg, Sport Appearance Pkg, Chrome Appearance Pkg, Trailer Tow Pkg, and Max Trailer Tow Pkg. If in the first combo box I choose the Sport Appearance Pkg, I would like the 2nd and 3rd combo boxes to display the rest of the options except for the Sport Appearance Pkg. Then the same for the other combo boxes. I would like to only display in the 3rd combo box the options that were not selected in the 1st and 2nd combo boxes. I hope this makes sense. Thanks in advance.

[Micron]

Each combo has to have its own query - they cannot be the same. In the subsequent (child) ones, the row source (sql statement) would have to specify WHERE [packages] <> Forms!frmYourFormName.cmbParentComboName or similar. Use your own form/control names of course.
Downstream from#2, you'd have to add each one that comes before in an AND syntax. Best explain whether or not these combos are to be bound. Usually they are not; reason being, if you alter a combo value when the record is displayed, then move off that record or do some other action that causes the form to be saved, that record contains the new value. This can result in unwanted changes.

What you appear to want is what's often referred to as 'cascading combo boxes' and there's lots of info on the 'net on how to create them. However, they usually aren't tied to the same list.

[Topflite66]

Thank you for your reply Micron. I am actually using several cascading combo boxes for my other selections which I was able to get working correctly, including the three Groups_Packages combo's. I have a separate table, containing metadata, and query for each successive cascading combo. I then have each combo pointing to a to a different field in a separate data table that I will use for my reports. So far everything is working correctly but would like to be able to select the three top packages using separate combo's. That's where the Groups_Packages combo's comes in to play. All three will pull their list from the same table, Or do I need separate tables as well as queries for each Groups_Packages combo?

As for the SQL statements, if I am understanding correctly, I could enter a filter expression in the criteria of the child queries? Unfortunately I'm not as familiar with SQL as I should be. I think I know enough to get myself into trouble. LOL Does this seem accurate for the filter expression - [Groups_Packages_2] <> [Forms]![Vehicle Entry Form].cboGroups_Packages_1 ???

Sorry for my lack of knowledge on this but I am pretty much a novice when it comes to Access. I have built a couple basic databases with Access but this is starting to get a bit above my pay grade. LOL Thanks again for your patience.

[Topflite66]

[QUOTE=Topflite66;368001]

As for the SQL statements, if I am understanding correctly, I could enter a filter expression in the criteria of the child queries? Unfortunately I'm not as familiar with SQL as I should be. I think I know enough to get myself into trouble. LOL Does this seem accurate for the filter expression - [Groups_Packages_2] <> [Forms]![Vehicle Entry Form].cboGroups_Packages_1 ???

I tried that filter expression and unfortunately it didn't work.

[Micron]

Would help if you had posted the sql so we don't have to guess at what you've named things. I will guess that the field to be filtered is "Pkgs". Maybe for combo1 row source

SELECT * FROM qryGroups_Packages

then for combo2

SELECT * FROM qryGroups_Packages WHERE qryGroups_Packages.Pkgs <> Forms![Vehicle Entry Form].cboGroups_Packages_1

then for combo3

SELECT * FROM qryGroups_Packages WHERE qryGroups_Packages.Pkgs <> Forms![Vehicle Entry Form].cboGroups_Packages_1 AND qryGroups_Packages.Pkgs <> Forms![Vehicle Entry Form].cboGroups_Packages_2

and so on if there's more.
Perhaps you can see why I don't use such long names for controls. Using my naming convention it would have been frmVehEntry.cmbGrpPkg1 No brackets required; no underscore. Except for the spaces you have, yours is OK - just more cumbersome. If it were much worse, I might construct the row source in vba and assign it to the control. You're going to need code anyway, to requery the child combo(s) every time you select a value from a parent(s).

I also have no idea what the bound column is in the combos or even if what I wrote will match that column since * will return all fields. Hopefully you can figure out how to use your own SELECT part and incorporate the WHERE part of the examples.

[June7]

"it didn't work" means what - error message, wrong results, nothing happens?

What exactly do you have in the RowSource property - reference to table or query or an SQL statement? I use SQL statements.

SELECT some fields FROM table or query name WHERE some field <> [other combobox1 name];

I find it works better without the form reference prefix when combos are all on the same form.

Are the combobox values numeric unique ID?

Then code in some event (try GotFocus of combos 2 and 3) to Requery.

Me.combobox2name.Requery

I agree, shorter names like cboGrpPkgs1, no spaces, no punctuation/special characters (except underscore and I even avoid those). No reserved words as names.

[Topflite66]

Thank you both for your suggestions with the naming of the controls. I guess I should have thought of that before I started typing in all the VBA code for the cascading combo's. I've read both the 2003 and the 2010 versions of the O'Reilly Missing Manual book and have been using their suggestions. However, I can see how abbreviating the names can be helpful and a time saver.

Micron, I will try your suggestions, tomorrow at work, to see if I can get them to work. If not I will post the SQL so you are better able to understand what I am trying to do. I should have thought of that as well. Thanks again for you help and patience.

[Topflite66]

Unfortunately, last night the light bulb came on and I realized something regarding what I am trying to accomplish. I will more than likely not be able to do this, unless you have another suggestion. This db is going to be for vehicle inventory so I can generate reports regarding number of sales and current inventory levels for each model, color, trim level, etc. I wanted to incorporate the top 3 accessory packages for selection in the cascading combo's. However, not all vehicles have 3 accessory packages. Some of the less expensive vehicles don't have any accessory options that are available and some of the more expensive units have several accessory option packages. So I added "N/A" as one of the options in the Groups_Packages table for each type of unit. I just realized that if I chose "N/A" in the first combo box and I use this filter then "N/A" will not show up in the 2nd and 3rd combo's. Unless you have another suggestion. I apologize for taking up your time. Thank you.

[Micron]

I apologize for taking up your time.

No problem. You're very thankful and appreciative, by the way.
If a vehicle can have 0, 1, 2 or 3 option packages, then consider a lookup table tblOptGrps as the combo row source with:
OptGrpID (autonumber PK), OptGrpName, (text), OptGrpDesc (text) perhaps looking like this

OptGrpID	OptGrpName	OptGrpDesc
1	Basic	cheapest
2	Silver	better
3	Gold	best

Maybe now you have 3, maybe later there's 4, so you just add to the list. This is the way design should work as opposed to having to open forms/tables and modify them to suit changing situations. What I can't tell is if in this table you also need a field for obsolete (either Date or Yes/No field) since removing records isn't usually advised. Having one could prevent obsolete options from appearing but you'd need them when searching on existing inventory that might have the obsoleted options. You'd need a way to prevent the obsolete from being chosen when doing data entry, but this consideration should already be part of the overall db design. That's about all you need to "fix" the combo issue.

I came up with the rest of this post in a very round-about fashion and now I'm not even sure you need this commentary, but I'll leave it in. It has to do with what I suspect you need for the rest of the form process and am not sure why I felt it was necessary without ever having seen your db design. Perhaps it was driven by a notion that you needed to know how to integrate the options list.

Then a joining table to associate the options with the inventory data (tblInvOpts). The fields likely should be number (long) and the record values are FK's where that number is the ID field from the parent related table(s) assuming you have tblMake and tblModel already. This would enable you to start filtering the options based on make and model as soon as you open the form (you'd probably also want to drive the report from this form). The data in this table might look like

InvOptID	MakeFK	ModelFK	OptGrpIDFK
1	1	4	1
2	1	6	2
3	1	8	3

What this shows for record 2 is that you have a vehicle of make 1 (ID 1 from tblModel) that is model 6 (ID 6 from tblModel) that has the option group 2. You should be able to join tblMake, tblModel, tblOptGrps in a query to get the text values of these ID's in a query. A query would return each vehicle in inventory that met the values of make, model and option (1,6,2) and you'd count the resulting records. Again, you are not trying to discern 6 from 5 because you've included the text values in order to make sense of what you're selecting.

Sorry if this is all basic to you, but if the concept is new, I think you should review db normalization. I have links for that if you need them, but if you do, you might have to step back and redesign to reduce future issues. You could post a pic of your relationships, or a zipped copy of your db for analysis. I may not be able to open it - I can't see from this response page what version you're using.

[Topflite66]

Thank you for your post. I actually have Access 2016 so not sure if you would be able to open it in 2007 or not. I could zip it up and send it to you for review if you think you have the time to look at it and would be willing to offer suggestions. As I mentioned I am a novice when it comes to Access so any suggestions are welcomed. Most of what I've learned I have read on the internet and through an Access 2010 Missing Manual book. I don't really have a working knowledge of Access other than some basic db's that I created for home. What started this whole thing is that I am the New Car Inventory Manager for a Ford dealership and I have previously been using Excel to keep track of ordered units. I decided I wanted reports to be able to better understand what our customers trends were based on. Because of the scale of the Excel sheets and the yearly changes in options it became a nightmare trying to use Excel to compile all the data. So I decided to take a crack at Access. I figured, I taught myself how to do everything else in computers so I should be able to do this as well. 4 months later, I'm still refining and learning. Apparently I'm a gluten for punishment. LOL

I had read in the Missing Manual book about organizing the tables using the ID fields. I had thought about using that scenario, however decided to use the actual descriptions because of confusion. Each year our Manufacturer comes out with Order Guides that I am working with to enter the metadata. I currently have the following tables:

Year, Model, Body_Code, Pep_Code, Engine, Trans, Wheelbase, Axle, Groups_Packages, Wheels, Ext_Color, Trim

For the most part, each successive table is dependent on the previous set of tables. For example only certain Engine options are available depending on the Year, Model, Body Code, and Pep Code. If you change any of the other previous options a completely different set of Engine options would be available. And it can and does change year to year. Especially when you get to the Groups & Packages, Exterior colors, Wheels, and Trim. So I based all of my relationships on the Year. As I added data to each table, the tables got larger and larger because I had to account for all the previous options. That's where using the actual descriptions made more sense to me because I would probably get confused as to what each number meant. I now have all the metadata entered for 2015 so when I enter the 2016 metadata I can compare what the options were for 2015 and then copy and paste in the tables or adjust as needed. I know that probably seems like backward thinking and a lot of work. It has been, but because I am not knowledgeable enough about Access, that was only way I could think of to accomplish what I need. Sorry for such a long post but thought it might help you understand some of the decisions I've made in creating this. I hope this makes sense.

[Micron]

Thank you for your post. I actually have Access 2016 so not sure if you would be able to open it in 2007 or not.

Depends on whether or not you use incompatible features. No need to send, post a copy here. If I cannot, others should be able to open it.

Year, Model, Body_Code, Pep_Code, Engine, Trans, Wheelbase, Axle, Groups_Packages, Wheels, Ext_Color, Trim

Don't agree with this structure unless maybe these are all lookup tables, in which case I see nothing that suggests you have tables for records.

For the most part, each successive table is dependent on the previous set of tables.

No, definitely not; at least by my understanding of this statement. Tables are related and not interdependent.

I based all of my relationships on the Year

So an Escape can have the same options as a Fiesta or F150 as long as it's the same year? Can you see a problem with this? Maybe you could work around the issues, but one work around leads to another because of incorrect design at the start. As for the rest of the post, I'm not really following. To me, metadata is data about data, so I don't see how it fits. Sounds like you got off on the wrong path, like you're in a maze. Might only get worse. Anyway, you've got the answers on how to filter cascading combos where the child doesn't contain the value chosen in a parent (which is different) and to answer post 8, the suggested options table from post 9 would require model and year fields (you know not to use Year as an object/field name, right?). Probably don't need Make if the store will only ever sell Fords as new vehicles.

[orange]

Hmmm. I'd like to see the tables and relationships you have at the moment.

One question that jumps out at me is ---Doesn't Ford offer such a database or access to such a database?
Seems to me that every dealership would have similar need, and if every dealership had to build their own (without database background etc) this would be a major time occupier and distraction.

Here is a draft model based on items that have many attributes. This was a concept model dealing with clothing (finished garments) that have various categories ---color, sizes, fabric.... It doesn't deal with your industry/needs but there is a similar concept (quite remote I agree).


I recommend you get a clear description of what you are trying to do based on your business facts, and build a data model that you can test. I would think the number of options and attributes would be quite voluminous. If you get a data model, using pencil and paper and some sample data and a few test scenarios, you can test the model and adjust as needed. Getting the tables and relationships set up to support your requirements is a very critical step. If done correctly it will save hours of frustration and work arounds. A model ignored or not tested could lead to a lot of headaches and extra work at every step (operation/maintenance/adjustment..)

Here is another free data model re vehicle manufacturers from Barry Williams' site.

Here's another model I helped someone with that may be more relevant to your business. (I knew I had something, just couldn't recall name etc.) This is the link to the thread that may be useful in getting your business rules/facts organized.



Good luck.

[Topflite66]

First of all, thank you for the detailed response Orange. I actually did write out on a piece of paper all the tables I needed and how they would relate. Because of my lack of knowledge, what I have now is what I came up with as the best scenario. I had thought of using a joining table and in fact started out that way, based on the book I read about Access, but was getting confused as to what each ID number referenced. I currently have a partially completed form to enter the orders from 2015, and all of the backend or metadata for the cascading combo boxes entered for 2015. It has taken quite a bit of work and effort to make sure everything matches but the nice thing is that when I add for 2016 to current, I can copy and paste a lot of the information for each table. I will just need to compare each model from year to year.

One question that jumps out at me is ---Doesn't Ford offer such a database or access to such a database?
Seems to me that every dealership would have similar need, and if every dealership had to build their own (without database background etc) this would be a major time occupier and distraction.

Ford does have data that they maintain for the last 6 months that is relevant to our region. However we have a fairly large region, geographically, and what sells well in one end of the region doesn't necessarily sell well in the other end of the region.

I have attached a what I have so far. I don't have any data files entered yet as I have been working primarily on getting the backend data correct and the form to work correctly. I'm sure there are several things I've done that could be done better or more efficiently but because I haven't built many db's, especially on this scale, and no formal training, this is what I came up with.

Thank you in advance for your help.

[Topflite66]

Micron , thank you for your response.

No, definitely not; at least by my understanding of this statement. Tables are related and not interdependent.

I probably stated that incorrectly. The tables themselves are not interdependent, however each data set is. Only certain models are available for 2015, each body code is dependent on the selected model, each pep code (Trim Level, i.e. SE, SEL, Titanium) is dependent on the model and the body code, only certain engines are available for the selected Model, body code, and pep code that were selected, etc.

So an Escape can have the same options as a Fiesta or F150 as long as it's the same year?

That may or may not be the case. They will have different Body codes and Pep Codes, but for example a Focus and a Fiesta can have the same Engines, Transmissions, Groups and packages.

We actually sell Lincolns as well but I am familiar enough with the product lines to know that an MKX is a Lincoln and an Edge is a Ford. I attached a zipped file of what I have so far to Orange's reply. Hopefully that will help clear up what I have been working on. Also this is more of a personal goal so I don't have any time table to get this up and running.

Thanks again.

[Micron]

Been away. Have found that I can open your db, seemingly without issue (not always the case). I will try to look it over during the weekend.