Subform works fine on its own but not within another form

[KerryL]

I have a subform (sfrmRequestDetails) that is part of another form (frmCustomerRequests).

Within "sfrmRequestDetails" are a couple of combo boxes called "cboCategory" & "cboProduct". Selecting a category from "cboCategory" limits the product selection in "cboProduct".

Here's the problem:
When I open "sfrmRequestDetails" on its own, "cboCategory" and "cboProduct" work together just fine. But when I open "frmCustomerRequests" and go to the subform area to fill in the details, the link between "cboCategory" and "cboProduct" no longer works correctly.

After selecting a category I click the arrow on "cboProduct" and instead of a limited list of products, I recieve this prompt:

Enter Paramter Value
Forms!sfrmRequestDetails.cboCategory

Why would "cboProduct" limit its list based on the selected category correctly when the sub-form is opened independently, but not when used within the parent form? When I make a change in "sfrmRequestDetails" I don't have to re-paste the subform into the parent form for the changes to take effect, do I?


FYI, here's the row source I'm using in "cboProduct".
SELECT [tblProducts].[ProductName] FROM tblProducts WHERE ((([tblProducts].[CategoryName])=[forms]![sfrmRequestDetails].[cboCategory]));


Thanks in advance for any advice you can give me,
KerryL

 

[MangroBongacello]

You have to include the main form in the path to combobox in the subform. Try this:

SELECT [tblProducts].[ProductName] FROM tblProducts WHERE ((([tblProducts].[CategoryName])=[forms]![frmCustomerRequests]![sfrmRequestDetails].form![cboCategory]));

Hope this helps
Mangro

 

[Lightning]

A slightly easier way (and certainly more legible way) is to use the Parent keyword:

SELECT [tblProducts].[ProductName] FROM tblProducts WHERE
((([tblProducts].[CategoryName])=Parent.[cboCategory]));

HTH
Lightning

(See Parent Property in the Access Help File)