Form with Subform - Subform contains drop down list

[tv69]

Hello,



I'm quite new to Access and maybe my idea of what I am trying to achieve is all backwards but I will describe it and see how it goes.

I've been trying to create a subform within a form with the subform having a drop down list. Once I have that figured out I will be adding another level (another subform with drop down list within the subform already mentioned).

I am able to create the subform with the form but I'm having trouble with the drop down list. I will explain what I am after with an example.

Form - contains models of vehicles (Jetta, GTI, Golf, Passat, Touareg, Tiguan, etc...)
Subform 1 - contains tranmission type (automatic, manual, tiptronic, paddle)
Subform 2 - contains vehicle color (red, white, black, blue, green)

I would like to use the form described as a way to input all the vehicle variations as they are needed. Then it would be desirable to use that information on an another form.

Another form, an Order form would then use the vehicle variations. The user would select the model of vehicle and in the drop down for transmission type would only see the available selections based based on the variations built above. Likewise, once transmission type is selected, the colors available would be shown in a drop down list, but only the colours available for that particular vehicle and transmission combination.

The above is a simplified example. The real application for this method will be a product line that will contain over 150 styles, with variations in appearance and colour.

I appreciate any input that could help me to build this.

TV

[RuralGuy]

Why are you using SubForms here? why not just put the ComboBox right on the MainForm?

[tv69]

Hi RuralGuy,

Well, that's exactly it, I'm not experienced enough to know what the best solution may be.

My thoughts are that I need a way to create the variations easily through an interface (product variation form, subform, subform), then I need access to those variations (main order form).

The variations will not be created by the same user. The variations will likely be controlled by an administrator type and the order form will be controlled by a customer service representative.

Is there a better approach?

TV

[RuralGuy]

SubForms are usually used to display a 1:M relationship between two tables/queries. That does not appear to be your case. As I said, just put the ComboBoxes on the MainForm and make your selections from there.

[tv69]

So would the mainform serve both functions in this case? Would you be able to add more combinations if they are not already there? In my case there may only be one combination let's say, Jetta, Automatic, White. Sometime later, someone may want a Jetta, Manual, Black. I don't want customer service to have the flexibility to enter any combination they wish. Customer service can only pick from existing combinations.

In your suggestion would management add data to the individual tables in the background to create the desired combinations? I'm trying to find an easy way to create the combinations. I'm a novice at this but the other users will be even more novice if that's possible! :-)

[RuralGuy]

Are you planning to have one table that contains all of the various combinations that have been specified?

[tv69]

I am open to suggestions but my thought is to use linked tables with 1:M relationships. The 'Vehicle' table will have unique values, while the 'Transmission' table will have duplicate values linked to the varous Vehicles. Further, each Vehicle/Transmission combination will have unique 'Color' values but the 'Color' table will have duplicate values for all the other Vehicle/Transmission combinations.

Am I sounding sane?!

[RuralGuy]

I understand the Lookup Tables but where do you expect to record the various combinations that have been selected?

[tv69]

My previous post is how I'm thinking the combinations would be built and stored, with linked tables. The stored combinations would then be selected on the main order form via lookups.

[RuralGuy]

Would your "combination" table would look something like this?
ComboID - AutoNumber
VehicleID - ForeignKey (FK) Long from the Vehicle table
TransID - FK (long) from the Transmission table
ColorID - FK (long) from the Color table

[tv69]

RuralGuy,

Would the Foreign Keys not come from other tables?

VehicleID (no foreign key)
TransID - FK from VehicleID
ColorID - FK from the TransID

[RuralGuy]

Yes, they would come from your Lookup tables.

[tv69]

Is it feasible to:

1. build the list of vehicle variations via a user form with the linked tables as I have described

or

2. do I need to create a single flat table with all the vehicle variations?

[RuralGuy]

Do the variations need to be pre-defined?

[RuralGuy]

Maybe I just don't understand what you are trying to accomplish.