Intelligent Combo Boxes

[jmany01]

This may be somewhat confusing to explain but I'll give it a shot.



Table A which simply lists a field of various screw sizes.
Table B lists a field of thread pitches as the primary key but the second field is a lookup field of Table A that has the capability of selecting multiple screw sizes.

Table A
Screw Size
1/4"
3/8"
1/2"


Table B
Thread Pitch -------- Screw Size
16 ------------------ 3/8", 1/2"
24 ------------------ 1/4", 3/8", 1/2"
30 ------------------ 1/4", 3/8"

I'm creating a form to create screw parts. The form has a combo box first to select the screw size. There is also a combo box to select the thread pitch but i want that combo box to only allow the option of selecting thread pitches that are associated with that specific screw size as specified in Table B. I know how to do this via code in VB which would modify the Row Source Property of the thread pitch combo box in the form and hard code the options in but I'd like to incorporate the intelligence of Table B so I wouldn't have to edit the code of the form every time a new thread pitch was added.

Any help would be appreciated.

[jmany01]

Answered my own problem.
I wasn't aware of the ability of using the WHERE capabilities in the RowSource. so the row source for the thread pitch combo box looks like this

combo_threadpitch.RowSource = "SELECT [Table B].[Thread Pitch].[Value] FROM [Table B] WHERE [Table B].[Screw Size]=combo_screwsize.value"

note: combo_ denotes the name of a combo box in my form