Filtering on data in a subform

  • Thread starter Thread starter Jason
  • Start date Start date
J

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.
 
Jason said:
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.
Click to expand...

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
 
-----Original Message-----
Jason said:
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.
Click to expand...

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
Click to expand...
primary form? What if there are more than one subforms
that need to be filtered on? Thanks.
 
Jason said:
-----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
Click to expand...
primary form? What if there are more than one subforms
that need to be filtered on? Thanks.
Click to expand...

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.
 
Back
Top