Unable to Filter by Selection on a subform

[Steve]

Initially I had one table and fields called ForAircraftType1,
ForAircraftType2 etc with aircraft names in them, to indicate which aircraft
a certain product was designed for. I have now correctly created a separate
table for these as they fall into the realm of a Mother-child relational
database. Related on IDAircraftPart as opposed to part title to ensure
uniqueness. I have a subform within FrmAircraftPart to allow data entry
into TblAircraftType and to see at a glance the aircraft types the part is
for.
However now I come to see all parts for a certain aircraft, I click on
Filter by Selection, clear the grid and type in an aircraft type in the
subform window, hit the filter icon and it still shows all records !!!!!
Is it now not possible to see my ACparts and all the related fields in
FrmAircraftParts for an aircraft type, this is a real drawback if so, before
I could do so no problem, but IT IS correct to have these two tables now, as
they are a typical mother-child relationship, as beforehand doing a query on
aircraft type A or B or C in fields 1 or 2 or 3 or 4 etc was complex !, now
I simply enter in Criteria A or B or C and query produces the goods !

Steve

 

[Rick Brandt]

Initially I had one table and fields called ForAircraftType1,
ForAircraftType2 etc with aircraft names in them, to indicate which aircraft
a certain product was designed for. I have now correctly created a separate
table for these as they fall into the realm of a Mother-child relational
database. Related on IDAircraftPart as opposed to part title to ensure
uniqueness. I have a subform within FrmAircraftPart to allow data entry
into TblAircraftType and to see at a glance the aircraft types the part is
for.
However now I come to see all parts for a certain aircraft, I click on
Filter by Selection, clear the grid and type in an aircraft type in the
subform window, hit the filter icon and it still shows all records !!!!!
Is it now not possible to see my ACparts and all the related fields in
FrmAircraftParts for an aircraft type, this is a real drawback if so, before
I could do so no problem, but IT IS correct to have these two tables now, as
they are a typical mother-child relationship, as beforehand doing a query on
aircraft type A or B or C in fields 1 or 2 or 3 or 4 etc was complex !, now
I simply enter in Criteria A or B or C and query produces the goods !

You have to understand what is happening when you apply a filter to a subform.
Doing so is the equivalent of saying "Show me only sub-form records matching
this filter criteria". It does *nothing* to filter which main records are
shown. If what you actually want is more like "Show me only the main records
having a particular kind of sub-record", you can do this, but not very easily
with the built-in filtering tools.

You need a filter that uses a sub-query. Assuming a main Primary Key of "[ID]"
which is also the field used in the link between the main and child form it
would look something like...

Me.Filter = "[ID] In(SELECT [ID] FROM ChildTableName WHERE...)"
Me.FilterOn = True

This can be done with the built-in "Advanced Filter" tool if you are comfortable
writing the sub-select query SQL in the criteria row. Otherwise you can apply
such a filter in code using the above technique.

 

[Steve]

Rick,
Shall give it a go, will need to get my Access Code minded friend to aid me
I think as you lose me after the WHERE ! and where exactly to put it.
Steve

Initially I had one table and fields called ForAircraftType1,
ForAircraftType2 etc with aircraft names in them, to indicate which aircraft
a certain product was designed for. I have now correctly created a separate
table for these as they fall into the realm of a Mother-child relational
database. Related on IDAircraftPart as opposed to part title to ensure
uniqueness. I have a subform within FrmAircraftPart to allow data entry
into TblAircraftType and to see at a glance the aircraft types the part is
for.
However now I come to see all parts for a certain aircraft, I click on
Filter by Selection, clear the grid and type in an aircraft type in the
subform window, hit the filter icon and it still shows all records !!!!!
Is it now not possible to see my ACparts and all the related fields in
FrmAircraftParts for an aircraft type, this is a real drawback if so, before
I could do so no problem, but IT IS correct to have these two tables now, as
they are a typical mother-child relationship, as beforehand doing a query on
aircraft type A or B or C in fields 1 or 2 or 3 or 4 etc was complex !, now
I simply enter in Criteria A or B or C and query produces the goods !

You have to understand what is happening when you apply a filter to a subform.
Doing so is the equivalent of saying "Show me only sub-form records matching
this filter criteria". It does *nothing* to filter which main records are
shown. If what you actually want is more like "Show me only the main records
having a particular kind of sub-record", you can do this, but not very easily
with the built-in filtering tools.

You need a filter that uses a sub-query. Assuming a main Primary Key of "[ID]"
which is also the field used in the link between the main and child form it
would look something like...

Me.Filter = "[ID] In(SELECT [ID] FROM ChildTableName WHERE...)"
Me.FilterOn = True

This can be done with the built-in "Advanced Filter" tool if you are comfortable
writing the sub-select query SQL in the criteria row. Otherwise you can apply
such a filter in code using the above technique.