Pls Help! Form works but subform doesn't!

[tpalmer00]

Hi,

I'm currently working on a project which is a lot more difficult than i thought it would be. I created a form which has 2 combo boxes: Category and product. When the user selects the Category I want the product cbo box to filter only the products from that particular category.

The form I built works perfect. However, when i go to add it in as a subform the product cbo box no longer presents a list. I get a box that asks for the parameter value - Forms!KBTPContentForm!Category

I'm sure I just need to repoint the code but haven't had any luck so far. Below is the code attached.

Category cbo box. After Update code:

Private Sub Category_AfterUpdate()
Product = Null
Product.Requery
Product = Me.Product.ItemData(0)
End Sub

Private Sub Form_Current()
Me.Product.Requery
End Sub

Private Sub Form_Load()
If IsNull(Category) Then
Category = Me.Category.ItemData(0)
Call Category_AfterUpdate
End If
End Sub

Expression attached to product cbo (rowsource)

SELECT Courses.CourseID, Courses.CourseName, Courses.CategoryID FROM Courses WHERE (((Courses.CategoryID)=Forms!KBTPContentForm!Category)) ORDER BY Courses.CourseName;

I should say that the framework for the system sits on a form called Frm_CatProd which has tab control pages. The subform (which doesn't work) sits on a page called Criteria.

Can anyone point me in the right direction??

Many Thanks,

Trish

 

[Remou]

You must refer to subforms in a specific way:

http://www.mvps.org/access/forms/frm0031.htm

 

[BSman]

In the form that will end up as a subform, try the following:

For the AfterUpdate property of your combo box for the category (cboCategory), change the record source for cboProduct (the combo box for the producted) based on the category selected. So....

me.cboProduct.RecordSource = "SELECT * from Products WHERE Category = '" & me.cboCategory & "'"

me.cboProduct.RecordSource.Requery


If this works as a separate form, it should still work when you use that form as a subform.

Bob

 

[tpalmer00]

Hi Bob,

That's the funny thing about this - it works great as a form. As soon as I put it into the Main Form is stops working. I tried your code and no dice unfortunately. It still gives me the error message like it's searching for a form. I've been experimenting with different code like:

Forms!Mainform!Subform1.SourceObject and

Forms!Mainform!Subform1.Form.RecordSource

But still no luck. Is there anything else that could prevent this from working right as a subform?

Thanks so much,
Trish

 

[BSman]

Try a variation like this:

me.parent.ChildForm.cboProduct.RecordSource

Use this to refer to the control in both statements to set the record source and to requery the combo box. ChildForm should be, I believe, the name of the control on your main form that contains the subform, rather than the name of the subform itself.

Bob