Filter in MainForm and SubForm

[PaulB]

Hi All,

For my daughter's primary school, I'm writing some kind of admin
application. While doing this, I have the next question.

I have two tables:
- TblParents, which has (amongst others) fields [ID_Parents] and
[FamilyName]
- TblChilds, which has (amongst others) fields [ID_Parents], [FirstName]
and [FamilyName]
There's a relation: both have field [ID_Parents]

In the main form (FrmParents), there is a subform (subFrmChilds), linked
with [ID_Parents]. This works fine.

In this form, there is a text box [StrFilter], which is the input for a
filter. What I want is that this filter has effect on both the
Familyname and the FirstName. The background of this idea is that the
user should be able to filter as easy as possible. So, regardless
whether he's looking for a firstname or a familiy name, he's using one
text box.

I tried to do this as follows (event code in FrmParents):

If Me.StrFilter <> "" Then
Me.Filter = "[FamilyName] like '*" & Me.StrFilter & "*'"
Me.FilterOn = True
With Me.subFormChilds.Form
.Filter = "[FirstName] like '*" & Me.StrFilter& "*'"
.FilterOn = True
End With
Else
Me.FilterOn = False
Me.subFrmChilds.Form.FilterOn = False
End If
Endif

Anyhow, this filter only works fine for filtering the mainform. There's
no effect on the subform.
What should I change to make it work for the subform as well?

Thanks in advance for any suggestions.

Paul

 

[Maurice]

If your subform is linked to the mainform by a particular field the subform
should filter along with the mainform. Have you linked the main form to the
subform with the appropriate ID-fields?

 

[PaulB]

Hi Maurice,

Yes, it is (using [ID_Parents]).

Paul


Op 29-5-07 20:32, Maurice schreef:

 

[Allen Browne]

Paul, there are several things that could go wrong here.

1. If you leave the unbound text box blank, it will be Null, which is not
the same as a zero length string. Perhaps you need to test:
If IsNull(me.StrFilter) Then
or if you wish to test for both cases:
If Me.strFilter & vbNullstring <> vbNullstring Then

2. Access gets confused if you filter both the main form and subform.
Details in the first 2 topics at:
http://allenbrowne.com/bug-02.html

3. Since the child records depend on the parent records, filtering the main
form affects what's there in the subform too.

4. The filters you proposed will *exclude* records where the names are null
(e.g. if the FirstName is blank in the subform.) That may not be what you
intend.

 

[PaulB]

Hi Allen,

Thank you for your sugestions. Unfortunately, this did not solve my problem.

But when I'm thinking this over, a question/remark came up. In fact what
I'm asking is an 'OR' filter. I do not whether that's possible on a
MainForm/SubForm combination.

Example: if the StrFilter = "Pete", I want to have an overview of all
families where the familyname is e.g. Peterson, ánd all families where
one of the kids is named Pete (or Peter). Is this possible at all?

Thank you for your thoughts on this!

Paul


Op 30-5-07 14:38, Allen Browne schreef:

 

[Allen Browne]

Hmm: That would be messy.

You would need to use a subquery in the filter string for the main form.

This kind of thing:

Dim strWhere As String
strWhere = "(([FamilyName] Like ""*" & Me.strfilter & "*"") OR EXISTS " & _
"(SELECT MyChildID FROM MyChildTable " & _
"WHERE ((MyChildTable.MyForeignKeyID = MyMainTable.MyPrimaryKeyID) " & _
"AND (MyChildTable.FirstName Like ""*" & Me.strfilter & "*"")))"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[PaulB]

Thanks mate, this works perfectly. Exactly as I needed. You've been a
great help; I'd never came to this solution myself.

Paul

Op 31-5-07 4:03, Allen Browne schreef: