Filter on sub form

[domino3]

I have a subform within a form. I am able to filter data on the main form, but when I try to set a filter on a field on the subform the filter doesn't work. Do I need to change a setting in the properties to allow filtering on either the main form or the subform?

 

[SMerrill]

Filtering a subform is possible using default settings. You will have to be more specific. Please include example file stripped down, if possible.

Take care,
--Shaun

"I wish that my room had a floor; I don't care so much for a door.
But this crawling around without touching the ground is getting to be quite a bore!" -- Gelett Burgess

 

[humaniversity]

Hi,
I understand that you would like to filter records on your main form that holds characteristics given by the datafields presented in the subform,

I include an example from an address book I have developed, it builds a sql stetment that concanates to the filter of the mainform:

In the event ApplyFilter of the main form add following pieces of code
First:
==========================================================
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' If the filter button clicked was
' close without filtering or close without server filtering
' Jump out of the procedure
If (ApplyType = 2) Or (ApplyType = 4) Then
GoTo ByPassApplyFilter
End If
==========================================================
the Tag ByPassApplyFilter looks like this at the bottom of the routine:
==========================================================
ByPassApplyFilter:

End Sub
==========================================================

Now in between the top of the routine and bottom include following code:
==========================================================
'Declare variables for the main form and the basic functionality

Dim stMainFormFilter As String
Dim bolMainFormFilterOn As Boolean

Dim stInSelectClauseCheckPresence As String
Dim stAndInSelectClauseCheckPresence As String
Dim stWhere As String
Dim stEndBrackets As String
Dim stAnd As String

'Declare variables for the PeopleEmailDetails sub form
Dim stSubFormPeopleEmailFilter As String
Dim stInSelectClausePeopleEmail As String
Dim bolSubFormPeopleEmailFilter As Boolean



' Initialize variables for building query strings
stAnd = " And"
stWhere = "where"
stEndBrackets = &quot)"

' Initialize variables for testing query strings in filters from query builds
stInSelectClauseCheckPresence = " (Qpeople.PersonID In (select"
stAndInSelectClauseCheckPresence = stAnd & stInSelectClauseCheckPresence

stMainFormFilter = Me.Filter
bolMainFormFilterOn = Forms("FPeopleMain&quot.FilterOn

' If there is a filter applied from User go into truncation previous string expressions
If Len(stMainFormFilter) > 0 Then
' If the filter contains a string "In select..." from a previous build
If (InStr(stMainFormFilter, stInSelectClauseCheckPresence)) Then
' If the Filter also includes a starting "And", truncate the expression from the correct position
If (InStr(stMainFormFilter, stAndInSelectClauseCheckPresence)) Then
stMainFormFilter = Left(stMainFormFilter, InStr(stMainFormFilter, stAndInSelectClauseCheckPresence))
Else
stMainFormFilter = Left(stMainFormFilter, InStr(stMainFormFilter, stInSelectClauseCheckPresence))
End If
End If
End If

' Prepare to build the query strings of sub-selections

'---------------------------------
' The PeopleEMailDetailsSub Form

stSubFormPeopleEmailFilter = Forms("FPeopleMain&quot.[DpersonEmailSub].Form.Filter
bolSubFormPeopleEmailFilter = Forms("FPeopleMain&quot.[DpersonEmailSub].Form.FilterOn
stInSelectClausePeopleEmail = " (Qpeople.PersonID In (select QPersonEmailDetails.PersonID from QPersonEmailDetails where "

' If the filter has selections
If Len(stSubFormPeopleEmailFilter) > 0 Then
' If it is needed to put the " And" string before adding further filters
If Len(stMainFormFilter) > 0 Then
stMainFormFilter = stMainFormFilter & stAnd
End If
' Now add the string of "In select ...." and the actual sub form filter
' and add the correct number of trailing &quot..
stMainFormFilter = stMainFormFilter & _
stInSelectClausePeopleEmail & _
stSubFormPeopleEmailFilter & stEndBrackets
' Remove any leading blank from the string initialization with the Left-Function
stMainFormFilter = LTrim(stMainFormFilter)
End If

'---------------------------------------------
Me.Filter = stMainFormFilter
bolMainFormFilterOn = Forms("FPeopleMain&quot.FilterOn

' Set the variable used for refresh of the apply filter function
' Because the Event Current is called as the first thing after apply filter
' and the event current is calling apply filter
' this whole gymnastics is needed because the content of
' the subform filters are not available before the button apply filter has been
' pressed AND the control returns to the data-form after filter view!

intRefreshFilter = 0

==========================================================

My main form (identified as Forms("FPeopleMain&quot ) has the master linkfield Qperson.PersonID
and my subform (identified as a tab Forms("FPeopleMain&quot.[DpersonEmailSub].Form ) gets data from the query:QPersonEmailDetails with child link field:QPersonEmailDetails.PersonID

The last part of coding needed is to maintain the refresh of the filters wich happens when then event current is applied:

==========================================================
Private Sub Form_Current()

Dim intArgTrue As Integer
Dim intArgFalse As Integer

If (intRefreshFilter <> 1) Then
intArgTrue = True
intArgFalse = False
Form_ApplyFilter intArgFalse, intArgTrue
intRefreshFilter = 1
End If

End Sub
===========================================================
At last but not least you have to decalre a variable, accesable for the whole form that holds the refreshflag updated by the event current (The variable should be dclared at the top of the forms code in the section genral declarations):
===========================================================
Option Compare Database
Option Explicit
Private intRefreshFilter As Integer

===========================================================
and finaly initializing the variable when the mainform opens:

===========================================================
Private Sub Form_Open(Cancel As Integer)

' Initialize the variable used for refresh of the apply filter function
intRefreshFilter = 1

End sub

===========================================================
What you basically need to change in order to accomodate this example to you rapplicatin is the following variable names or variable contents:

stInSelectClauseCheckPresence = &quot; (Qpeople.PersonID In (select&quot;

bolMainFormFilterOn = Forms(&quot;FPeopleMain&quot.FilterOn

stSubFormPeopleEmailFilter = Forms(&quot;FPeopleMain&quot.[DpersonEmailSub].Form.Filter

bolSubFormPeopleEmailFilter = Forms(&quot;FPeopleMain&quot.
[DpersonEmailSub].Form.FilterOn

stInSelectClausePeopleEmail = &quot; (Qpeople.PersonID In (select QPersonEmailDetails.PersonID from QPersonEmailDetails where &quot;
========================================================

I hope it helps you, have fun.
Regards Vigy