Form Filtering Question.....

[Guest]

My Main form is called Quotes and has a Quote ID as the pk.

I the following 4 field table that lists information regarding the quote:
Quote ID
Vehicle ID
Seating Position ID
Component ID

I can have:
Multiple Vehicle IDs per Quote ID
Multiple Seating Position IDs per Vehicle ID
Multiple Component IDs per Seating Position ID

Therefore, I have All 4 fields make up the pk for this table.

On my Quote form, I would like to have the following Tabular Subforms:
1. Vehicle ID Subform - showing just the Vehicle IDs linked to the current
Quote ID
2. Seating Position Subform - showing just the Seating Positions linked to
the selected Quote/Vehicle combo
3. Components Subform - showing just the Components linked to the selected
Quote/Vehicle combo/Seating Positions.

Of course I want to also be able to add data into each subform and be sure
that it's tied to the correct "master field" (the one above it in the
hiearchy).

Any ideas? Is there a better way to setup the data than a 4 field primary
Key?
With the table structure the way it is, I probably would expect to have more
than 30,000 records at max.

Thanks,
Mike

 

[Bill Nicholson]

Here's how you might do this:

Use a Master form that identifies the quote and 3 seperate forms that let
the user pick out vehicles, seating positions, and components. The 'child'
forms would recalculate when the master form is changed by the user. Also,
the component form would change when the seating form changes. etc. This
would allow the user to arrange the forms to their particular taste and also
eliminate the deeply nested subforms.
Another useful control for this type of applicaiton is the Tree View. The
Tree View lets your users drill down to the lowest level of detail or
overview the entire dataset, all from the same window. You might consider
setting up a Tree View and supplementing that with forms for the
quotes/vehicles/seating positions/components.

Also, I would consider creating an AutoNumber field for the primary key of
every table. You can still create another key that must be unique and
consists of your 4 fields, but your queries will be much easier to
manipulate if the table has a single field primary key. The way you
described it, if any of the 4 fields are changed, your primary key changes,
whoch would be disastrous for any other tables that have a foreign key
reference back to that table.

Bill N.
Cincinnati, OH USA
nicomp (at) yahoo.com