Subform combo selection based on Main form combo selection

[Ms Kathy]

Access 2007: on Data Entry Form, this is what I hope to accomplish:
If I choose [90ft Line] from ComboBox on main form, then limit my choices on Items Subform ComboBox to include only those items from [90ft Line table]; OR if I choose [80ft Line] from ComboBox on Main form, then limit my choices on Subform ComboBox to include only those items from [80ft Line table], and so on.

I tried using this IIf statement (don't know if this is what I need to do or not):

IIf([forms]![F_Production_Main].[Prod_Line]="90ft",

![T_productionLine_90ft].[90ft_ItemNumber]) Or ([forms]![F_Production_Main].[Prod_Line]="80ft",

![T_ProductionLine_80ft].[80ft_ItemNumber])

And I get this message: Syntex error (comma) in query expression

Any direction would be appreciated. I will post this and then I have to head out. I will return in the AM.

 

[pbaldy]

It sounds like you have a design problem. The options should probably be in the same table, with a field to denote option. Then you have a simple cascading combo:

http://www.baldyweb.com/CascadingCombo.htm

If you want to use a different tables, you'll need VBA behind the first combo.