Referring to form control

[Amy Blankenship]

I have a form structure that looks something like this:

CourseStructure>Category Subform>Topic Subform>QuickCode

From QuickCode, I need to have a Combobox where the rowsource refers to
CategoryID on Category Subform. I have tried this expression in the
Immediate window:
[Forms]![CourseStructure]![Category Subform].[Form]![CategoryID]

It returns 1 or 2 or whatever the current CategoryID happens to be.

However, when I try it in the query, it returns no rows. When I put in just
1 or 2 there, it properly returns all the records.

The query is this:

SELECT KSubCat.KSubCatID, [KCategoryCode] & [KSubCategorycode] AS Code
FROM ((KCategorySet INNER JOIN Category ON KCategorySet.KCatSetID =
Category.KCatSet) INNER JOIN KCategory ON KCategorySet.KCatSetID =
KCategory.KCatSet) INNER JOIN KSubCat ON (KCategory.KcategoryID =
KSubCat.KCategoryID) AND (KCategory.KcategoryID = KSubCat.KCategoryID) AND
(KCategory.KcategoryID = KSubCat.KCategoryID)
WHERE (((Category.CategoryID)=[Forms]![CourseStructure]![Category
Subform].[Form]![CategoryID]));

Obviously, I am misunderstanding the form controls reference from Dev
Ashish's site, but I've tried every variation I can think of. Could someone
throw me a rope here?

Thanks;

Amy

 

[Devlin]

Amy,

This can be handled in one of two ways. The first is the simplest.

If your query is saved in the queries collection, open the query in design
view. Next select Query from the menu, then Parameters. In the Parameters
dialog, enter your Form!Control syntax just as you did in the design window.
Then specify the datatype in the second column. This should resolve your
issue.

The other way to do this is to set the ComboBox's Row Source each time the
criteria changes. You probably want your ComboBox to be requeried when the
current record changes. So at the OnCurrent event write some code the
produces a string SQL statement, changing the WHERE statement each time to
relfect the desired criteria value. Then assign your SQL statement to the
ComboBox's RowSource property.

I hope this helps out.

Devlin