I think you may have misunderstood the nature of the relationship type. There is not a set of one-to-many relationship types from each table to the next as there would be in a purely hierarchical model, my ComboDemo file being an example, where the
model is:
Counties---<Districts----<Parishes
What you have is an octonary relationship type, an 8-way many-to-many relationship type. In effect this is what your current table models, but using multi-valued fields rather than multiple rows. I referred in my earlier reply to a many-to-many relationship
type being resolved into two one-to-many relationship types, which is the most common, but it can be any number greater than one. So what you need to model the relationship type is a single table with much the same columns as your current tblVariants,
but with each as a straightforward text column.
To fill this table you create an append query which appends all columns from tblVariants, but in the case of the multi-valued fields the column name is followed with .Value. If a column position in tblVariants can be Null append Nz(TheColumName,"N/A")
or Nz(TheColumName.Value,"N/A") as appropriate. As the table to which rows are being appended is 'all key' no columns can be Null, so where a column is not appropriate to the relationship, N/A is inserted. You don't even need to
design this table beforehand, you can if you wish use a 'make table' query and then set its primary key to all columns, and index each column non-uniquely after it's been filled.
Once you've filled this table you can then fill each of the referenced tables, ConvModels, Makes, Models etc by executing a series of 8 simple append queries, using the DISTINCT predicate to suppress duplication. So to fill ConvModels for instance, if
we assume the table which models the relationship type is named VehicleOptions the query would be:
INSERT INTO ConvModels(ConvModel)
SELECT DISTINCT ConvModel
FROM VehicleOptions;
Do similarly for the other seven referenced tables, and, once you've filled them all, create and enforce relations between VehicleOptions and each of the eight referenced tables. Normally you'd give each an autonumber 'surrogate' foreign key, but in this
case don't. Using the table's single text column as its 'natural' primary key, and having text foreign keys in VehicleOptions makes the creation of correlated combo boxes very much easier. You don't need to use 'hybrid' controls as in my demo,
and you don't need to include the referenced tables in the combo boxes' RowSource properties, just the VehicleOptions table. Make sure you use the DISTINCT predicate in the RowSource queries to suppress duplicate values in the drop-down lists.
When correlating the combo boxes remember that you reference all of the combo boxes higher in the hierarchy as parameters in the RowSource query, not just the one immediately above it in the hierarchy. In the AfterUpdate event procedure of each bar the
final one, set all the combo boxes below it in the hierarchy to Null and Requery them all.
Ken Sheridan, Stafford, England
Cascading Combo Boxes
