Reference a subform via code

[Guest]

I’m trying to open a form called "Products" using the following command,
called from another "search" form.
DoCmd.OpenForm "Products", , , stCriteria
It works fine when stCriteria refers to controls on the main “Productsâ€
form; however, I can’t get it to work when stCriteria refers to controls on
Product’s subform (conveniently named “Products Subformâ€).

The user fills in unbound text boxes on the search form, then the stCriteria
variable is also built from the user's entries. The following operation in
example #1 below works just fine; the Products form opens, displaying only
the records specified by the users search criteria. However, the operation
in example #2 does not; the Products form opens, displaying no records.

Example #1 (Refers to control [Product Description] on main form; works fine.)
stCriteria = IIf(stCriteria = "", IIf(Not IsNull([Forms]![Query
Form]![txtDescription]), "[ProductDescription] Like '*" & [Forms]![Query
Form]![txtDescription] & "*'", ""), _
stCriteria & IIf(Not IsNull([Forms]![Query Form]![txtDescription]), " and
[ProductDescription] Like '*" & [Forms]![Query Form]![txtDescription] & "*'",
""))
DoCmd.OpenForm "Products", , , stCriteria

Example #2 (Refers to control [SerialNumber] on subform; opens “Productsâ€
form with no records)
stCriteria = IIf(stCriteria = "", IIf(Not IsNull([Forms]![Query
Form]![txtSerialNumber]), "[Forms]![Products]![Products
Subform].[Form]![SerialNumber] Like '*" & [Forms]![Query
Form]![txtSerialNumber] & "*'", ""), _
stCriteria & IIf(Not IsNull([Forms]![Query Form]![txtSerialNumber]), " and
[Forms]![Products]![Products Subform].[Form]![SerialNumber] Like '*" &
[Forms]![Query Form]![txtSerialNumber] & "*'", ""))
DoCmd.OpenForm "Products", , , stCriteria

Note: “Products Subform†is the name of the subform CONTROL on the
“Products†form.

I tried many of the solutions in several other posts, but to no avail.
Please help.

Thanks,
Dorci

 

[John Vinson]

Example #2 (Refers to control [SerialNumber] on subform; opens “Products”
form with no records)
stCriteria = IIf(stCriteria = "", IIf(Not IsNull([Forms]![Query
Form]![txtSerialNumber]), "[Forms]![Products]![Products
Subform].[Form]![SerialNumber] Like '*" & [Forms]![Query
Form]![txtSerialNumber] & "*'", ""), _
stCriteria & IIf(Not IsNull([Forms]![Query Form]![txtSerialNumber]), " and
[Forms]![Products]![Products Subform].[Form]![SerialNumber] Like '*" &
[Forms]![Query Form]![txtSerialNumber] & "*'", ""))
DoCmd.OpenForm "Products", , , stCriteria

Note: “Products Subform” is the name of the subform CONTROL on the
“Products” form.

I tried many of the solutions in several other posts, but to no avail.
Please help.

I'm trying to figure out what stCriteria will become with this. Have
you tried stepping through the code with the debugger? What is the
actual resulting value of stCriteria?

John W. Vinson[MVP]