Multiple Input boxes to show one field

[ElcoyotldeAztlan]

Hello all,

I had a interesting request on a form and was wondering what you guys think.

Basically I will use this example
If I have many cars with many colors yet many cars can have many colors how can I show one car that has many colors If I'm just using one Field of colors,

Normally I would use two Fields ColorField1 and ColorField2 therefore my form can show two input boxes but is there a way to show two input boxes with one field?

On the table rows, the way this db is designed is one table connects all the IDs from two related tables, I know I could do something like

CarID ColorID
1 2
1 3

That would show CarID 1 is Car1 is both Color 2 and 3 (2 could be Red and 3 Blue from a related table)

 

[Minty]

If it simply for display purposes you can use a concatenate method as per Allen Browne http://allenbrowne.com/func-concat.html

This would allow you to display Car1 | Red,Green,Blue etc

Your data looks as if it is correctly stored,

 

[arnelgp]

no possible using junction table to show 2 textbox field for car color.
you can however do away with the junction table and incorporate the 2 text field to your main car table.
not much space to take for 2-byte fields.

 

[ElcoyotldeAztlan]

Thanks guys for the answers
Minty it wouldn't be used just for display purposes they also want to add the colors with new entries on a form.

 

[Minty]

Make a pop up form to add a colour to the current car record or have an unbound combo box with the colours listed and some code to add the selected colour to your car / colour table, then requery the displayed concatenated list.

Otherwise you are looking at a multivalued field which don't scale well and many experienced developers don't have much truck with.