Filter By Form on Subforms

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am using Access 2002. I've designed a main form with
several subforms from related tables. When I click on
Filter by Form, I can search anything on the main form
with no problem (i.e. after clicking on the "Filter" icon
I'm shown a filtered view with only the number of
matching records listed as total (e.g. 3 of 59 filtered).

However if I enter something to search for in one of the
subforms, it just returns everything without filtering
for the criteria entered in the subform and it shows
record 1 of 59 -- and not the word "filtered" (to use the
above example). Even though it now shows FLTR on the
Form View status line at the bottom of the screen.

Anybody have any idea what's going on here and how I can
fix it??
 
Hi Steve,

I think that this is the correct behavior for filtering within a subform but
let's make sure I understand you correctly. When you add a additional filter
to the subform, it is only going to further restrict the rows in the
subform. It is not going to filter the records in the main form which is
what I *think* you might be expecting.

For example, if you have Customers in the main form and Orders in the
subform, when you filter the subform by Orderdate>1/1/2004 you would still
see all Customers in the main form, but the subform would be further
filtered to only show orders for this year.

Does this make sense or have I misunderstood your problem?
 
Sandra:

Thanks so much for your reply. You've got what I'm
trying to do exactly. But I'm sure I've been able to do
that in the past, i.e. to find records related records in
the main form by filtering for something on the subform.

So the question would be, to use your example, how can I
filter for Orderdate>1/1/2004 and get a filtered list of
all the Customers with OrderDate in that range??

Steve
-----Original Message-----
Hi Steve,

I think that this is the correct behavior for filtering within a subform but
let's make sure I understand you correctly. When you add a additional filter
to the subform, it is only going to further restrict the rows in the
subform. It is not going to filter the records in the main form which is
what I *think* you might be expecting.

For example, if you have Customers in the main form and Orders in the
subform, when you filter the subform by
Click to expand...
Orderdate>1/1/2004 you would still
see all Customers in the main form, but the subform would be further
filtered to only show orders for this year.

Does this make sense or have I misunderstood your problem?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I am using Access 2002. I've designed a main form with
several subforms from related tables. When I click on
Filter by Form, I can search anything on the main form
with no problem (i.e. after clicking on the "Filter" icon
I'm shown a filtered view with only the number of
matching records listed as total (e.g. 3 of 59 filtered).

However if I enter something to search for in one of the
subforms, it just returns everything without filtering
for the criteria entered in the subform and it shows
record 1 of 59 -- and not the word "filtered" (to use the
above example). Even though it now shows FLTR on the
Form View status line at the bottom of the screen.

Anybody have any idea what's going on here and how I can
fix it??
Click to expand...

.
Click to expand...
 
See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article explains how to achieve a quazi-filter by changing the main
form's RecordSource so it only contains the records that have a match in the
subform.

It would also be possible to use a subquery in the filter of the main form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sandra:

Thanks so much for your reply. You've got what I'm
trying to do exactly. But I'm sure I've been able to do
that in the past, i.e. to find records related records in
the main form by filtering for something on the subform.

So the question would be, to use your example, how can I
filter for Orderdate>1/1/2004 and get a filtered list of
all the Customers with OrderDate in that range??

Steve
-----Original Message-----
Hi Steve,

I think that this is the correct behavior for filtering within a subform but
let's make sure I understand you correctly. When you add a additional filter
to the subform, it is only going to further restrict the rows in the
subform. It is not going to filter the records in the main form which is
what I *think* you might be expecting.

For example, if you have Customers in the main form and Orders in the
subform, when you filter the subform by
Click to expand...
Orderdate>1/1/2004 you would still
see all Customers in the main form, but the subform would be further
filtered to only show orders for this year.

Does this make sense or have I misunderstood your problem?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I am using Access 2002. I've designed a main form with
several subforms from related tables. When I click on
Filter by Form, I can search anything on the main form
with no problem (i.e. after clicking on the "Filter" icon
I'm shown a filtered view with only the number of
matching records listed as total (e.g. 3 of 59 filtered).

However if I enter something to search for in one of the
subforms, it just returns everything without filtering
for the criteria entered in the subform and it shows
record 1 of 59 -- and not the word "filtered" (to use the
above example). Even though it now shows FLTR on the
Form View status line at the bottom of the screen.

Anybody have any idea what's going on here and how I can
fix it??
Click to expand...
Click to expand...
Click to expand...
 
Thanks Allen,

I was getting ready to write something similar (examples too). You saved me
a bunch of time!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Allen said:
See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article explains how to achieve a quazi-filter by changing the main
form's RecordSource so it only contains the records that have a match in
the subform.

It would also be possible to use a subquery in the filter of the main
form.


Sandra:

Thanks so much for your reply. You've got what I'm
trying to do exactly. But I'm sure I've been able to do
that in the past, i.e. to find records related records in
the main form by filtering for something on the subform.

So the question would be, to use your example, how can I
filter for Orderdate>1/1/2004 and get a filtered list of
all the Customers with OrderDate in that range??

Steve
-----Original Message-----
Hi Steve,

I think that this is the correct behavior for filtering within a subform but
let's make sure I understand you correctly. When you add a additional filter
to the subform, it is only going to further restrict the rows in the
subform. It is not going to filter the records in the main form which is
what I *think* you might be expecting.

For example, if you have Customers in the main form and Orders in the
subform, when you filter the subform by
Click to expand...
Orderdate>1/1/2004 you would still
see all Customers in the main form, but the subform would be further
filtered to only show orders for this year.

Does this make sense or have I misunderstood your problem?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Steve wrote:
I am using Access 2002. I've designed a main form with
several subforms from related tables. When I click on
Filter by Form, I can search anything on the main form
with no problem (i.e. after clicking on the "Filter" icon
I'm shown a filtered view with only the number of
matching records listed as total (e.g. 3 of 59 filtered).

However if I enter something to search for in one of the
subforms, it just returns everything without filtering
for the criteria entered in the subform and it shows
record 1 of 59 -- and not the word "filtered" (to use the
above example). Even though it now shows FLTR on the
Form View status line at the bottom of the screen.

Anybody have any idea what's going on here and how I can
fix it??
Click to expand...
Click to expand...
Click to expand...
 
Sandra:

I'm having a bit of trouble adapting Allen's example to
my own case. For one thing his example is trying to
filter for information from a third table not on either
the main form or the subform.

In my case I have an elaborate contact main form
("frmContactInfo") which has a lot of personal and
business information on it that comes from a table
named "tblContactInfo". In addition there a four
subforms on this main form which provide additional
information about the contact. For example there's a
subform ("frmLang subform") which lists all languages
(which can be multiple) the contact speaks, based on the
table "tblLang". The subforms (and their tables) are
related many-to-one to the main form on the
field "ContactID".

I want to be able to filter such that I can find all the
contacts who speak, for example, Spanish. I would
IDEALLY like to be able to find all contacts who, say,
speak Spanish AND Italian, or who speak Spanish OR
Italian. I don't see Allen's method using a combo box
list of languages being able to do that .

I believe I could do this with a report that has a
parameter input form, but would prefer to be able to do
it directly from the form.

Any suggestions/examples here??

Thanks so much,

Steve
-----Original Message-----
Thanks Allen,

I was getting ready to write something similar (examples too). You saved me
a bunch of time!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Allen said:
See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article explains how to achieve a quazi-filter by changing the main
form's RecordSource so it only contains the records that have a match in
the subform.

It would also be possible to use a subquery in the filter of the main
form.


Sandra:

Thanks so much for your reply. You've got what I'm
trying to do exactly. But I'm sure I've been able to do
that in the past, i.e. to find records related records in
the main form by filtering for something on the subform.

So the question would be, to use your example, how can I
filter for Orderdate>1/1/2004 and get a filtered list of
all the Customers with OrderDate in that range??

Steve
-----Original Message-----
Hi Steve,

I think that this is the correct behavior for filtering
within a subform but
let's make sure I understand you correctly. When you add
a additional filter
to the subform, it is only going to further restrict the
rows in the
subform. It is not going to filter the records in the
main form which is
what I *think* you might be expecting.

For example, if you have Customers in the main form and
Orders in the
subform, when you filter the subform by
Orderdate>1/1/2004 you would still
see all Customers in the main form, but the subform
would be further
filtered to only show orders for this year.

Does this make sense or have I misunderstood your
problem?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to
this newsgroup.

Steve wrote:
I am using Access 2002. I've designed a main form with
several subforms from related tables. When I click on
Filter by Form, I can search anything on the main form
with no problem (i.e. after clicking on the "Filter"
icon
I'm shown a filtered view with only the number of
matching records listed as total (e.g. 3 of 59
filtered).

However if I enter something to search for in one of
the
subforms, it just returns everything without filtering
for the criteria entered in the subform and it shows
record 1 of 59 -- and not the word "filtered" (to use
the
above example). Even though it now shows FLTR on the
Form View status line at the bottom of the screen.

Anybody have any idea what's going on here and how I
can
fix it??
Click to expand...
Click to expand...

.
Click to expand...
 
You can do this with an unbound combo on your main form to filter the
contacts to only the Spanish speaking ones.

Presumably you have 3 tables:
- tblContactInfo contains the people (names, addresses);
- tblLang contains the list of languages;
- tblContactLang is a junction between the 2 (1 row for every combination,
i.e. if a person speaks 3 languages, they have 3 records).

These 5 steps create a tempory query to help you get your RecordSource
string right. Once you have that you can discard this query:
1. Create a query using tblContactInfo and tblContactLang.

2. Drag the star from tblContactInfo into the query design grid.

3. Drag the LangID from tblContactLang. Uncheck the Show box.
In the Criteria row, pick any language.

4. Open the Properties box (View menu), and set Yes beside Unique Values.

5. Switch the query to SQL View.

What you see there is a template for the string you need to create in your
form.

1. Place an unbound combo box on the main form (e.g. in the Form Header
section). Set its RowSource to tblLang.

2. In its AfterUpdate event procedure, build a string like the one you see
above, but concatenate the value of the combo into the string in place of
the dummy value you used at step 3 above.

You will end up with something like this:

Private Sub cboLang_AfterUpdate()
Dim strSQL As String

If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If

If IsNull(Me.cboLang) Then 'Show all records
Me.RecordSource = "tblContactInfo"
Else
strSQL = "SELECT DISTINCTROW tblContactInfo.* " & _
" FROM ... " & _
" WHERE tblContactLang.LangID = " & Me.cboLang & _
" ORDER BY tblContactInfo.Surname, tblContactInfo.FirstName;"
Me.RecordSource = strSql
End If
End Sub


To extend the concept to those who speak a combination of languages, you
could provide multiple language combos/listboxes and some way to let the
user select AND or OR combinations. Theoretically it is just a matter of
adjusting the WHERE clause of the RecordSource statement, and it would
probably involve using subqueries. In practice, interfacing the appropriate
bracketing options for the user will be an issue, since:
A or (B and C)
is not the same as:
(A or B) and C

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Steve said:
I'm having a bit of trouble adapting Allen's example to
my own case. For one thing his example is trying to
filter for information from a third table not on either
the main form or the subform.

In my case I have an elaborate contact main form
("frmContactInfo") which has a lot of personal and
business information on it that comes from a table
named "tblContactInfo". In addition there a four
subforms on this main form which provide additional
information about the contact. For example there's a
subform ("frmLang subform") which lists all languages
(which can be multiple) the contact speaks, based on the
table "tblLang". The subforms (and their tables) are
related many-to-one to the main form on the
field "ContactID".

I want to be able to filter such that I can find all the
contacts who speak, for example, Spanish. I would
IDEALLY like to be able to find all contacts who, say,
speak Spanish AND Italian, or who speak Spanish OR
Italian. I don't see Allen's method using a combo box
list of languages being able to do that .

I believe I could do this with a report that has a
parameter input form, but would prefer to be able to do
it directly from the form.

Any suggestions/examples here??

Thanks so much,

Steve
-----Original Message-----
Thanks Allen,

I was getting ready to write something similar (examples too). You saved me
a bunch of time!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Allen said:
See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article explains how to achieve a quazi-filter by changing the main
form's RecordSource so it only contains the records that have a match in
the subform.

It would also be possible to use a subquery in the filter of the main
form.


Sandra:

Thanks so much for your reply. You've got what I'm
trying to do exactly. But I'm sure I've been able to do
that in the past, i.e. to find records related records in
the main form by filtering for something on the subform.

So the question would be, to use your example, how can I
filter for Orderdate>1/1/2004 and get a filtered list of
all the Customers with OrderDate in that range??

Steve
-----Original Message-----
Hi Steve,

I think that this is the correct behavior for filtering
within a subform but
let's make sure I understand you correctly. When you add
a additional filter
to the subform, it is only going to further restrict the
rows in the
subform. It is not going to filter the records in the
main form which is
what I *think* you might be expecting.

For example, if you have Customers in the main form and
Orders in the
subform, when you filter the subform by
Orderdate>1/1/2004 you would still
see all Customers in the main form, but the subform
would be further
filtered to only show orders for this year.

Does this make sense or have I misunderstood your
problem?


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to
this newsgroup.

Steve wrote:
I am using Access 2002. I've designed a main form with
several subforms from related tables. When I click on
Filter by Form, I can search anything on the main form
with no problem (i.e. after clicking on the "Filter"
icon
I'm shown a filtered view with only the number of
matching records listed as total (e.g. 3 of 59
filtered).

However if I enter something to search for in one of
the
subforms, it just returns everything without filtering
for the criteria entered in the subform and it shows
record 1 of 59 -- and not the word "filtered" (to use
the
above example). Even though it now shows FLTR on the
Form View status line at the bottom of the screen.

Anybody have any idea what's going on here and how I
can
fix it??
Click to expand...
Click to expand...
Click to expand...
 
Back
Top