Remove subform filter does not show all records

O

obzerbin

Hello...

In Access 2003 Project...

I have two subforms on a parent form. One of these has a combo box
for selecting the filter value, and a button for clearing the filter.
On the AfterUpdate event, the subform filters and then
programmatically filters the other subform, which works fine. When
the button is clicked, it removes the filter from the subform, and
clears the filter from the other subform programmatically, but I can't
get the other subform to show it's records.

'combo box filter code
Private Sub cmbCAMPUS_SLCT_AfterUpdate()

Dim strCurrFilter As String

Me.Filter = ""

Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = ""

If (Not IsNull(Me.cmbCAMPUS_SLCT)) Then
strCurrFilter = "(RTRIM([CAMPUS_NAME]) LIKE '%" &
RTrim(Me.cmbCAMPUS_SLCT) & "%')"

Me.Filter = strCurrFilter
Me.FilterOn = True

Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter =
strCurrFilter
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = True

End If

End Sub



'command button filter removal code
Private Sub cmdCLR_FLTR_Click()

Me.Filter = ""
Me.FilterOn = False
Me.cmbCAMPUS_SLCT = Null

Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = ""
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = False

DoCmd.ShowAllRecords

End Sub

I have tried several variations on this including serverfilter
properties (but I have a Stored Procedure as the recordsource).

The filter lifts on Me. But not on the referenced subform.

The problem is somewhere in the command button code...or I am missing
the obvious or ???

Please help.

Hans
 
S

Sylvain Lafontaine

This is an old problem related to the fact that you are removing two filters
inside the same event. If you inverse the order of removal, for example
removing the filter on the second subform first, you will see that's now the
first form who doesn't get un filtered.

To correct this problem, place the line « ... Form.FilterOn = False » before
the line « Form.Filter = strCurrFilter » and add a new line « ...
Form.FilterOn = True » just before:

Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = True
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = False
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = strCurrFilter

Notice that the use of filters and server filters is seriously bugged in ADP
and that instead of using filters, you should change the RecordSource of the
forms/subforms instead. You don't need to make a requery after changing the
record source of a form/subform.
 
T

Tom Wannabe

yeah I agree; I'm not sure that it's buggy--

I just only ever use the serverfilter property; or I set the recordsource



Sylvain Lafontaine said:
This is an old problem related to the fact that you are removing two
filters inside the same event. If you inverse the order of removal, for
example removing the filter on the second subform first, you will see
that's now the first form who doesn't get un filtered.

To correct this problem, place the line « ... Form.FilterOn = False »
before the line « Form.Filter = strCurrFilter » and add a new line « ...
Form.FilterOn = True » just before:

Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = True
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = False
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = strCurrFilter

Notice that the use of filters and server filters is seriously bugged in
ADP and that instead of using filters, you should change the RecordSource
of the forms/subforms instead. You don't need to make a requery after
changing the record source of a form/subform.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


obzerbin said:
Hello...

In Access 2003 Project...

I have two subforms on a parent form. One of these has a combo box
for selecting the filter value, and a button for clearing the filter.
On the AfterUpdate event, the subform filters and then
programmatically filters the other subform, which works fine. When
the button is clicked, it removes the filter from the subform, and
clears the filter from the other subform programmatically, but I can't
get the other subform to show it's records.

'combo box filter code
Private Sub cmbCAMPUS_SLCT_AfterUpdate()

Dim strCurrFilter As String

Me.Filter = ""

Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = ""

If (Not IsNull(Me.cmbCAMPUS_SLCT)) Then
strCurrFilter = "(RTRIM([CAMPUS_NAME]) LIKE '%" &
RTrim(Me.cmbCAMPUS_SLCT) & "%')"

Me.Filter = strCurrFilter
Me.FilterOn = True

Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter =
strCurrFilter
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = True

End If

End Sub



'command button filter removal code
Private Sub cmdCLR_FLTR_Click()

Me.Filter = ""
Me.FilterOn = False
Me.cmbCAMPUS_SLCT = Null

Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = ""
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = False

DoCmd.ShowAllRecords

End Sub

I have tried several variations on this including serverfilter
properties (but I have a Stored Procedure as the recordsource).

The filter lifts on Me. But not on the referenced subform.

The problem is somewhere in the command button code...or I am missing
the obvious or ???

Please help.

Hans
Click to expand...
Click to expand...
 
O

obzerbin

yeah I agree; I'm not sure that it's buggy--

I just only ever use the serverfilter property; or I set the recordsource

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message

This is an old problem related to the fact that you are removing two
filters inside the same event. If you inverse the order of removal, for
example removing the filter on the second subform first, you will see
that's now the first form who doesn't get un filtered.
Click to expand...
To correct this problem, place the line « ... Form.FilterOn = False»
before the line « Form.Filter = strCurrFilter » and add a new line « ...
Form.FilterOn = True » just before:
Click to expand...
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = True
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = False
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = strCurrFilter
Click to expand...
Notice that the use of filters and server filters is seriously bugged in
ADP and that instead of using filters, you should change the RecordSource
of the forms/subforms instead. You don't need to make a requery after
changing the record source of a form/subform.
Click to expand...
obzerbin said:
Hello...
In Access 2003 Project...
I have two subforms on a parent form. One of these has a combo box
for selecting the filter value, and a button for clearing the filter.
On the AfterUpdate event, the subform filters and then
programmatically filters the other subform, which works fine. When
the button is clicked, it removes the filter from the subform, and
clears the filter from the other subform programmatically, but I can't
get the other subform to show it's records.
'combo box filter code
Private Sub cmbCAMPUS_SLCT_AfterUpdate()
Dim strCurrFilter As String
Me.Filter = ""
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = ""
If (Not IsNull(Me.cmbCAMPUS_SLCT)) Then
strCurrFilter = "(RTRIM([CAMPUS_NAME]) LIKE '%" &
RTrim(Me.cmbCAMPUS_SLCT) & "%')"
Me.Filter = strCurrFilter
Me.FilterOn = True
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter =
strCurrFilter
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = True
End If
End Sub
'command button filter removal code
Private Sub cmdCLR_FLTR_Click()
Me.Filter = ""
Me.FilterOn = False
Me.cmbCAMPUS_SLCT = Null
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.Filter = ""
Forms![frmRP_PROJ_MGR]![sfrmRP_PROJ_TASKS].Form.FilterOn = False
DoCmd.ShowAllRecords
End Sub
I have tried several variations on this including serverfilter
properties (but I have a Stored Procedure as the recordsource).
The filter lifts on Me. But not on the referenced subform.
The problem is somewhere in the command button code...or I am missing
the obvious or ???
Please help.
Hans- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

Thanks I will give it a try...

Hans
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter form using multiple combo boxes selection 6
Selecting current record in subform after removing filter 1
ApplyFilter event on a subform 1
Filtering and Removing Filter based on a combo box 3
Filter Text Syntax 2
Filter from Main Form to SubForm 5
Filter Form Based on Subform Combo Field Problem 1
If else subform reference access 2007 2

Top