Q: filter mainform based on value in (linked) subform

[Jen]

I try to open a mainform (frm_fakturainmatning) and it's subform
(frm_fakturarader_subform) from a startform (frm_startform) and filter the
mainform based on value of textfield in the subform (txt_forklaring).

On the startform (frm_startform) I have an unbound textbox
(txt_bladdra_per_fritext), and based on the value in this textform I want to
filter the mainform (where matches are found in the mainforms subform's
(txt_forklaring) fields). I pass the value like this:

stLinkCriteria =
"forms!fakturainmatning!fakturarader_subform.Form!forklaring Like '*" &
[Forms]![startform]![txt_bladdra_per_fritext] & "*'"

The system can't find the subforms textfield txt_forklaring. I guess that is
because the field forklaring isn't in the recordsource of the mainform. If I
put it there and change the stLinkCriteria accordingly then the filter
works, but then the mainform cykles through all the records in the subform
(the subform is datasheet and holds several records on the/ per mainform).
The mainforms recordsource/ query is bound to 3 tables. The subform
recordsource/ query is bound to its own table ("tbl_fakturarader", limked to
the tables in the recordsource/ query for the mainform).

I'm sorry that I can't explain this better in english, I know it could be
hard to understand what I'm trying to explain. Please ask for clarification
and I will do my best to answer you. Any help on how to filter mainform
based on value in (linked) subform would be wery appreciated.

Jen

 

[John Viescas]

I think I understand what it is you're trying to do. Here is an example and
solution using Northwind that you can adapt to your problem.

"Show me the orders that have a product that have a certain word in the
product name."

stLinkCriteria = "[OrderID] IN (SELECT [OrderID] " & _
"FROM Products INNER JOIN [Order Details] " & _
"ON Products.ProductID = [Order Details].ProductID " & _
"WHERE Products.ProductName LIKE '*" & _
Me.txtProdSearch & "*')"
DoCmd.OpenForm "Orders", WhereCondition:=stLinkCriteria

Notice that the filter on the outer form uses a subquery filter on the rows
that are in the subform. This should display only the orders that contain
the requested product. If you want to further filter the subform records to
show only the matching products, you'll need to pass something in OpenArgs
and add code to main form to apply the filter to the subform. Your code in
the start form might look like:

Do Cmd.OpenForm "Orders", WhereCondition:=stLinkCriteria, _
OpenArgs:="ProductName LIKE '*" & Me.txtProdSearch & "*'"

In the Load event of the "main" form, do something like this:

If Len(Me.OpenArgs) > 0 Then
Me.[Orders Subform].Form.Filter = Me.OpenArgs
Me.[Orders Subform].Form.FilterOn = True
End If

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I try to open a mainform (frm_fakturainmatning) and it's subform
(frm_fakturarader_subform) from a startform (frm_startform) and filter the
mainform based on value of textfield in the subform (txt_forklaring).

On the startform (frm_startform) I have an unbound textbox
(txt_bladdra_per_fritext), and based on the value in this textform I want to
filter the mainform (where matches are found in the mainforms subform's
(txt_forklaring) fields). I pass the value like this:

stLinkCriteria =
"forms!fakturainmatning!fakturarader_subform.Form!forklaring Like '*" &
[Forms]![startform]![txt_bladdra_per_fritext] & "*'"

The system can't find the subforms textfield txt_forklaring. I guess that is
because the field forklaring isn't in the recordsource of the mainform. If I
put it there and change the stLinkCriteria accordingly then the filter
works, but then the mainform cykles through all the records in the subform
(the subform is datasheet and holds several records on the/ per mainform).
The mainforms recordsource/ query is bound to 3 tables. The subform
recordsource/ query is bound to its own table ("tbl_fakturarader", limked to
the tables in the recordsource/ query for the mainform).

I'm sorry that I can't explain this better in english, I know it could be
hard to understand what I'm trying to explain. Please ask for clarification
and I will do my best to answer you. Any help on how to filter mainform
based on value in (linked) subform would be wery appreciated.

Jen

 

[Jen]

John, I don't fully understand why it works but it works. Thank you so so
much for your help. Jen.

I think I understand what it is you're trying to do. Here is an example and
solution using Northwind that you can adapt to your problem.

"Show me the orders that have a product that have a certain word in the
product name."

stLinkCriteria = "[OrderID] IN (SELECT [OrderID] " & _
"FROM Products INNER JOIN [Order Details] " & _
"ON Products.ProductID = [Order Details].ProductID " & _
"WHERE Products.ProductName LIKE '*" & _
Me.txtProdSearch & "*')"
DoCmd.OpenForm "Orders", WhereCondition:=stLinkCriteria

Notice that the filter on the outer form uses a subquery filter on the rows
that are in the subform. This should display only the orders that contain
the requested product. If you want to further filter the subform records to
show only the matching products, you'll need to pass something in OpenArgs
and add code to main form to apply the filter to the subform. Your code in
the start form might look like:

Do Cmd.OpenForm "Orders", WhereCondition:=stLinkCriteria, _
OpenArgs:="ProductName LIKE '*" & Me.txtProdSearch & "*'"

In the Load event of the "main" form, do something like this:

If Len(Me.OpenArgs) > 0 Then
Me.[Orders Subform].Form.Filter = Me.OpenArgs
Me.[Orders Subform].Form.FilterOn = True
End If

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I try to open a mainform (frm_fakturainmatning) and it's subform
(frm_fakturarader_subform) from a startform (frm_startform) and filter the
mainform based on value of textfield in the subform (txt_forklaring).

On the startform (frm_startform) I have an unbound textbox
(txt_bladdra_per_fritext), and based on the value in this textform I

want

to
filter the mainform (where matches are found in the mainforms subform's
(txt_forklaring) fields). I pass the value like this:

stLinkCriteria =
"forms!fakturainmatning!fakturarader_subform.Form!forklaring Like '*" &
[Forms]![startform]![txt_bladdra_per_fritext] & "*'"

The system can't find the subforms textfield txt_forklaring. I guess

that

is
because the field forklaring isn't in the recordsource of the mainform.

If

I
put it there and change the stLinkCriteria accordingly then the filter
works, but then the mainform cykles through all the records in the subform
(the subform is datasheet and holds several records on the/ per mainform).
The mainforms recordsource/ query is bound to 3 tables. The subform
recordsource/ query is bound to its own table ("tbl_fakturarader",

limked

to
the tables in the recordsource/ query for the mainform).

I'm sorry that I can't explain this better in english, I know it could be
hard to understand what I'm trying to explain. Please ask for clarification
and I will do my best to answer you. Any help on how to filter mainform
based on value in (linked) subform would be wery appreciated.

Jen