Referencing another field in a subform from the rowsource of a combo in the form.

[Philip Middleton]

Access 2003 / WinXP Pro SP2
I cant quite seem to get this right.
I have 2 combo boxes in a subform.
The first combo [Prod_Cat] simply select from a list of Categories. Works
fine.
The second Combo [Prod_No] selects from a table, limiting the selection
based on 2 things.
First, a value on the parent form ( [forms]![Orders]![Price_Level_ID]).
this works fine.
Second, the value in the frist combo box on the same subform. This I cant
get to work.
I cant seem to find the exact method of refering to a field in a subform.
The SQL in the RowSource for the second combo is;
SELECT Prices.Prod_No, Stock.Prod_Name, Prices.Price, Prices.TaxFree FROM
Prices INNER JOIN Stock ON Prices.Prod_No = Stock.Prod_No WHERE
(((Prices.Price_Level_ID)=[Forms]![Order]![Price_Level_ID]) AND
((Stock.Prod_Cat)=[Forms]![Order]![Order_Items_Subform]![Prod_Cat])) ORDER
BY Prices.Prod_No;

The part in question is;
[Forms]![Order]![Order_Items_Subform]![Prod_Cat]
this is evidently not the connect format for refering to a field [Prod_Cat]
in a subform called [Order_Items_Subform] in a form called [Orders]

I have tried some veriations on this format, like
[Forms]![Order]![Order_Items_Subform].[form]![Prod_Cat] but I havnt quite
got it right because when I try use the second combo box, Access asks me for
the value directly.

What am I doing wrong?

Thanks...Philip Middleton

 

[Marshall Barton]

Access 2003 / WinXP Pro SP2
I cant quite seem to get this right.
I have 2 combo boxes in a subform.
The first combo [Prod_Cat] simply select from a list of Categories. Works
fine.
The second Combo [Prod_No] selects from a table, limiting the selection
based on 2 things.
First, a value on the parent form ( [forms]![Orders]![Price_Level_ID]).
this works fine.
Second, the value in the frist combo box on the same subform. This I cant
get to work.
I cant seem to find the exact method of refering to a field in a subform.
The SQL in the RowSource for the second combo is;
SELECT Prices.Prod_No, Stock.Prod_Name, Prices.Price, Prices.TaxFree FROM
Prices INNER JOIN Stock ON Prices.Prod_No = Stock.Prod_No WHERE
(((Prices.Price_Level_ID)=[Forms]![Order]![Price_Level_ID]) AND
((Stock.Prod_Cat)=[Forms]![Order]![Order_Items_Subform]![Prod_Cat])) ORDER
BY Prices.Prod_No;

The part in question is;
[Forms]![Order]![Order_Items_Subform]![Prod_Cat]
this is evidently not the connect format for refering to a field [Prod_Cat]
in a subform called [Order_Items_Subform] in a form called [Orders]

I have tried some veriations on this format, like
[Forms]![Order]![Order_Items_Subform].[form]![Prod_Cat] but I havnt quite
got it right because when I try use the second combo box, Access asks me for
the value directly.

[Forms]![Order]![Order_Items_Subform].[form]![Prod_Cat]
is the proper syntax.

Double check that Order_Items_Subform is the name of the
subform **control** on the main form. The name of the
control may be different from the name of the form object
displayed in the control.

Also check the name of the combo box control to make sure it
really is Prod_Cat

 

[Philip Middleton]

Access 2003 / WinXP Pro SP2
I cant quite seem to get this right.
I have 2 combo boxes in a subform.
The first combo [Prod_Cat] simply select from a list of Categories. Works
fine.
The second Combo [Prod_No] selects from a table, limiting the selection
based on 2 things.
First, a value on the parent form ( [forms]![Orders]![Price_Level_ID]).
this works fine.
Second, the value in the frist combo box on the same subform. This I cant
get to work.
I cant seem to find the exact method of refering to a field in a subform.
The SQL in the RowSource for the second combo is;
SELECT Prices.Prod_No, Stock.Prod_Name, Prices.Price, Prices.TaxFree FROM
Prices INNER JOIN Stock ON Prices.Prod_No = Stock.Prod_No WHERE
(((Prices.Price_Level_ID)=[Forms]![Order]![Price_Level_ID]) AND
((Stock.Prod_Cat)=[Forms]![Order]![Order_Items_Subform]![Prod_Cat])) ORDER
BY Prices.Prod_No;

The part in question is;
[Forms]![Order]![Order_Items_Subform]![Prod_Cat]
this is evidently not the connect format for refering to a field
[Prod_Cat]
in a subform called [Order_Items_Subform] in a form called [Orders]

I have tried some veriations on this format, like
[Forms]![Order]![Order_Items_Subform].[form]![Prod_Cat] but I havnt quite
got it right because when I try use the second combo box, Access asks me
for
the value directly.

[Forms]![Order]![Order_Items_Subform].[form]![Prod_Cat]
is the proper syntax.

Double check that Order_Items_Subform is the name of the
subform **control** on the main form. The name of the
control may be different from the name of the form object
displayed in the control.

Also check the name of the combo box control to make sure it
really is Prod_Cat

Thanks.
Cant believe it!
It was the subform NAME in the parent form. I checked earlyer today, and
changed the subform name to "Order_Items_Subform " from "Order_Items
Subform".
As I have turned off warnings in code "DoCmd.SetWarnings (False)", I had
simply assumed this it had saved it, when it paused closing the form.
The form controle name was one of the few things I had not gone back and
checked!
Thanks for making me check - and think I'll turn warnings back on during
dev.

Ta... Philip.