A limiting many to many relationship

[Tyra]

Ok, I am probably making this harder than it needs to be, but here goes..

I need to set up a relationship where a user (dealer) can pick a car with a unique serial number and assign one of 4 colors to it.

There can only be one car with that serial number of each color. (So if there are 2 cars there are 8 possible combinations. Car1 in Blue, red, green, black and car2 in blue, red green, and black)

If that unique car has no colors left, it should not be available to pick from the start.

Once selecting a car, only those colors not used would appear in the drop down.

Sounds simple..right?

[bcooney]

What tables do you have in your solution?

This isn't all that simple. Have you made any progress? I'll try to find the time for a demo, meanwhile try a search on "dwindling value list."

[Tyra]

Yes, I have gotten it to work in a very clunky way. Basically had to create a join table that when a new car was entered it spawned a record for each possible color type in a Join table so that the color table could write its ID to that particular record. this allowed me to create a relationship that would show all records where Color:Car_id = Joined:Car_id:=Car_id and Color:Color_id <> Joined:Color_id. With a drop down list displaying the Joined:Color_Type field. It works but don't like creating the extra records. Will try to upload a sample in next couple of days.

Tables:

Invoice

Cars

Colors

Joined

[bcooney]

You will need the join table, but you do not need to create the record "placeholders" for each car/color combination ahead of time. Simply put a portal to the join table on the car entry form.

[comment]

Actually, you can do this without a join table:

DwindleVLbySales10.zip