Datasheet Form with combo list

[Erick]

If I have the following data source (either table or query) with 3 fields (id, type, colour)

ID-----Type-------Colour
1-------A----------Red
1-------A----------Blue
1-------A----------Green
2-------B----------Red
2-------B----------Yellow
2-------B----------Purple
2-------B----------Green

I need to create a Datasheet Form to list the above data with distinct ID (so in this case, it will only show 2 records). However, for the colour field, I need a dropdown combo list to give the relevant options. So the datasheet form will look like this:
ID-----Type-------Colour
1-------A----------(Combo list: Red, Blue, Green)
2-------B----------(Combo list: Red, Yellow, Purple, Green)

I just can't work out how to filter the combo list record to only show the ones relevant for the type.

 

[SydneyGeek]

You need to make the second combo use the value from the first in its criteria. Here is a link that shows the technique.

Denis

 

[boblarson]

What you really need is a table for your colours. So, like this:

tlkpColours
ColourID - Autonumber(PK)
ColourName - Text


and then you would have a separate types lookup table as well

tlkpTypes
TypeID - Autonumber (PK)
TypeDesc - Text

And then a junction table where you can assign the types to the colours

tlkpColoursTypes
ColourTypeID - Autonumber(PK)
ColourID - Long Integer (FK)
TypeID - Long Integer (FK)


Now with that you can assign whatever you need for types and then also you can have your colours in the combo but with the linked table you can also limit by type but only bring back the ones you need.