Filtering on data in a subform

[Jason]

Is it possible to filter records in a primary form by
using the records in a subform? My primary form is called
Test and has a control source table named TestResults.
The primary key field for the TestResults table is named
TestID. The subform is used to display name data in the
primary form and was created by a query on a table called
Names. The Names table is related in a one-to-many
relationship with the TestResults table, through the
TestID field. In the Names table, the first and last
names are individual fields, the query used to display the
name data on the primary form displays the names together
(e.g. John Doe) in one field. I want to be able to choose
a last name, and filter data in the primary form. I read
where a procedure involving an INNERJOIN could be used to
limit the recordset, however that example involved a combo
box being used to choose the Filter value, in this case
last name. I would like to be able to have a person type
in a last name instead of always having to choose one from
a list. Is this possible? Thanks.

 

[Rick Brandt]

Is it possible to filter records in a primary form by
using the records in a subform? My primary form is called
Test and has a control source table named TestResults.
The primary key field for the TestResults table is named
TestID. The subform is used to display name data in the
primary form and was created by a query on a table called
Names. The Names table is related in a one-to-many
relationship with the TestResults table, through the
TestID field. In the Names table, the first and last
names are individual fields, the query used to display the
name data on the primary form displays the names together
(e.g. John Doe) in one field. I want to be able to choose
a last name, and filter data in the primary form. I read
where a procedure involving an INNERJOIN could be used to
limit the recordset, however that example involved a combo
box being used to choose the Filter value, in this case
last name. I would like to be able to have a person type
in a last name instead of always having to choose one from
a list. Is this possible? Thanks.

You need to apply a filter that uses a sub-query.

Me.Filter = "TestID In(SELECT TestID FROM Names WHERE [last name] = '" &
Me!NameFilter & "')"
Me.FilterOn = True

 

[Jason]

-----Original Message-----

Is it possible to filter records in a primary form by
using the records in a subform? My primary form is called
Test and has a control source table named TestResults.
The primary key field for the TestResults table is named
TestID. The subform is used to display name data in the
primary form and was created by a query on a table called
Names. The Names table is related in a one-to-many
relationship with the TestResults table, through the
TestID field. In the Names table, the first and last
names are individual fields, the query used to display the
name data on the primary form displays the names together
(e.g. John Doe) in one field. I want to be able to choose
a last name, and filter data in the primary form. I read
where a procedure involving an INNERJOIN could be used to
limit the recordset, however that example involved a combo
box being used to choose the Filter value, in this case
last name. I would like to be able to have a person type
in a last name instead of always having to choose one from
a list. Is this possible? Thanks.

You need to apply a filter that uses a sub-query.

Me.Filter = "TestID In(SELECT TestID FROM Names WHERE [last name] = '" &
Me!NameFilter & "')"
Me.FilterOn = True


.
Would that filter be coded into an unbound field in the

primary form? What if there are more than one subforms
that need to be filtered on? Thanks.

 

[Rick Brandt]

-----Original Message-----
You need to apply a filter that uses a sub-query.

Me.Filter = "TestID In(SELECT TestID FROM Names WHERE [last name] = '" &
Me!NameFilter & "')"
Me.FilterOn = True
.
Would that filter be coded into an unbound field in the

primary form? What if there are more than one subforms
that need to be filtered on? Thanks.

Yes, in my example you would have a control on the main form (a Combo or TextBox) and
you would run the code in the AfterUpdate event.

If you needed to apply filtering from multiple subforms at once you would need to AND
two sub-queries together.