Cascading Combo boxes are showing the same data

Question

Hello. I'm a computer repair tech and I've created a database to keep track of laptops that I've worked on, and what I did to fix them and what parts they need. I want combo boxes because each laptop model has different parts descriptions and part numbers. But when I fill in one combo box, they all populate with the same data. I would like to know how to get each of the combo boxes to work independently, so if the laptop needs more than one part, I can select it. But pull the data from the same tables and query.

Answer 1

That is bad design. It is called a Repeating Group and violates Normalization rules.

If I understand your need, in the first column of the Part Description you are selecting a Laptop model and you then want the second column to display a list of part numbers specific to that Laptop model.

As Hans indicate this is a tricky issue since your subform is one set of controls. The sample that Ken has shows you one way to get around it. Another suggestion is to have 2 unbound comboboxes on the main form. Set those up as cascading. Then, after you selecting the description and part #, add a button that will add a new record to the table the subform is bound to. But don't use comboboxes in the subform.

Answer 2

That's not good database design. Take a look at Ken Sheridan's examples.

Answer 3

Could I just create 5 parts tables (Part_Info2, Part_Info3, Part_Info4, Part_Info5, Part_Info6) tables. And do the same thing with the queries? (Partsquery2, Partsquery3, Partsquery4 etc.) And have each individual combo box pointing to its own individual table and query. Seems like overkill, but that should work, don't you think?

Answer 4

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the first two forms in the section on 'entering data via a form/subforms' each contain correlated combo boxes for select a country, region, and city.  The first form is in single form view, whereas the second is in continuous forms view and uses hybrid controls in which a text box is carefully superimposed on each combo box to appear to the user like a single combo box control.

Having said this, I'm unsure whether correlated combo boxes, which imply hierarchical relationships, are appropriate for what you are doing.  You are, in essence, building a bill of materials (BoM).  The standard model for a BoM is an adjacency list table whose primary key is a composite of two foreign keys each referencing the foreign key of a single parts table, in which each row can represent an assembly or an atomic base part.  Assemblies might be composed of other assemblies (as sub-assemblies) as well as or in addition to base parts.  

There are a number of algorithms for generating a BOM from the standard model, but all require recursive querying, which JET/ACE SQL used by Access does not support.  It can be simulated, however, by progressively writing data to an external linked table in a temporary database created at runtime.  You'll find an example of this in my same OneDrive folder as BoM.zip.  You'll also find a simpler PartsTree database in the zip archive, which uses a query which references a fixed number of multiple instances of the tables, but this of course only generates a BoM to a fixed number of levels, rather than to an arbitrary number of levels.

Answer 5

This is tricky in a continuous form, since - as you have found - a continuous form only contains one set of controls, not a separate set for each record.

1. Requery the combo box in the On Current event, so that the drop-down list will be updated as you move from record to record.
2. Place a text box with the display value of the combo box on top of the combo box