Removing filter from multiple subforms

A

Ann in CA

Hello,
I have a form with 5 subforms all based on different tables. There is a
toggle at the top of the form to "Apply group filter" where I would like all
5 subforms to filter out records whose discipline is not equal to the user's
discipline (stored in glDiscipline). The filter applies fine to all five
subforms, but when I click the button again to remove the filter, it only
removes it from the first subform in the If toggle51 = 0 event list
(currently, frmMainFormSubE in the code below, but if I change the order, it
will remove from any other).
So how would I go about getting the filter removed from all five subforms at
once?

Here is the code:
Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
Me.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub


TIA!
 
A

Allen Browne

Ann, I think you have hit this bug in Access:
FilterOn not set/removed correctly for form and subform combination
described at:
http://allenbrowne.com/bug-02.html

Some workarounds:
a) Avoid filtering both the main form and the subforms.
b) Set the RecordSource instead of applying a filter.
 
A

Ann in CA

Allen, I thought I had read through that bug on your site and made sure I was
avoiding it, but I could be mistaken. There is no filter on my main form, and
the property AllowFilters is set to no, but there is a combo box with a
recordset clone to bookmark whichever as the first record?
Hate to sound silly, but I am rather a novice--in a nutshell, how would I
set the recordsource?

Allen Browne said:
Ann, I think you have hit this bug in Access:
FilterOn not set/removed correctly for form and subform combination
described at:
http://allenbrowne.com/bug-02.html

Some workarounds:
a) Avoid filtering both the main form and the subforms.
b) Set the RecordSource instead of applying a filter.

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

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

Ann in CA said:
Hello,
I have a form with 5 subforms all based on different tables. There is a
toggle at the top of the form to "Apply group filter" where I would like
all
5 subforms to filter out records whose discipline is not equal to the
user's
discipline (stored in glDiscipline). The filter applies fine to all five
subforms, but when I click the button again to remove the filter, it only
removes it from the first subform in the If toggle51 = 0 event list
(currently, frmMainFormSubE in the code below, but if I change the order,
it
will remove from any other).
So how would I go about getting the filter removed from all five subforms
at
once?

Here is the code:
Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
Me.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub


TIA!
Click to expand...
Click to expand...
 
A

Allen Browne

Okay, if you have not applied a Filter to your main form, your issue is not
the bug I referred to. Finding a record by using the bookmark does not
involve applying a filter, so it must be a different issue.

However, your code does turn the main form's FilterOn property off in the
case where Toggle51 = 0. You might want to omit this line.

Visually, you should see "(Filtered)" in the horizontal scrollbar of the
subforms (next to the navigation buttons) when the subform is filtered. Do
you still see this after running your code to turn the filters off?

If you want to set the the RecordSource, it would be somehing like this:
Dim strSql As String
strSql = "SELECT * FROM Table2 WHERE Table2.Discipline <> " & _
glDiscipline & ";"
Me.[Sub1].Form.RecordSource = strSql

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

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

Ann in CA said:
Allen, I thought I had read through that bug on your site and made sure I
was
avoiding it, but I could be mistaken. There is no filter on my main form,
and
the property AllowFilters is set to no, but there is a combo box with a
recordset clone to bookmark whichever as the first record?
Hate to sound silly, but I am rather a novice--in a nutshell, how would I
set the recordsource?

Allen Browne said:
Ann, I think you have hit this bug in Access:
FilterOn not set/removed correctly for form and subform combination
described at:
http://allenbrowne.com/bug-02.html

Some workarounds:
a) Avoid filtering both the main form and the subforms.
b) Set the RecordSource instead of applying a filter.

Ann in CA said:
Hello,
I have a form with 5 subforms all based on different tables. There is a
toggle at the top of the form to "Apply group filter" where I would
like
all
5 subforms to filter out records whose discipline is not equal to the
user's
discipline (stored in glDiscipline). The filter applies fine to all
five
subforms, but when I click the button again to remove the filter, it
only
removes it from the first subform in the If toggle51 = 0 event list
(currently, frmMainFormSubE in the code below, but if I change the
order,
it
will remove from any other).
So how would I go about getting the filter removed from all five
subforms
at
once?

Here is the code:
Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
Me.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Ann in CA

I took the line you suggested out, but it did not help. Here's the weird
part: The filter is removed when I click the button, but only the first
subform refreshes--in each subform I have headers that sort the columns
ascending or descending. So I click to remove the filter, and no additional
records show up, but if I click a header in any of the non-refreshed
subforms, then the records show up?

Allen Browne said:
Okay, if you have not applied a Filter to your main form, your issue is not
the bug I referred to. Finding a record by using the bookmark does not
involve applying a filter, so it must be a different issue.

However, your code does turn the main form's FilterOn property off in the
case where Toggle51 = 0. You might want to omit this line.

Visually, you should see "(Filtered)" in the horizontal scrollbar of the
subforms (next to the navigation buttons) when the subform is filtered. Do
you still see this after running your code to turn the filters off?

If you want to set the the RecordSource, it would be somehing like this:
Dim strSql As String
strSql = "SELECT * FROM Table2 WHERE Table2.Discipline <> " & _
glDiscipline & ";"
Me.[Sub1].Form.RecordSource = strSql

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

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

Ann in CA said:
Allen, I thought I had read through that bug on your site and made sure I
was
avoiding it, but I could be mistaken. There is no filter on my main form,
and
the property AllowFilters is set to no, but there is a combo box with a
recordset clone to bookmark whichever as the first record?
Hate to sound silly, but I am rather a novice--in a nutshell, how would I
set the recordsource?

Allen Browne said:
Ann, I think you have hit this bug in Access:
FilterOn not set/removed correctly for form and subform combination
described at:
http://allenbrowne.com/bug-02.html

Some workarounds:
a) Avoid filtering both the main form and the subforms.
b) Set the RecordSource instead of applying a filter.

Hello,
I have a form with 5 subforms all based on different tables. There is a
toggle at the top of the form to "Apply group filter" where I would
like
all
5 subforms to filter out records whose discipline is not equal to the
user's
discipline (stored in glDiscipline). The filter applies fine to all
five
subforms, but when I click the button again to remove the filter, it
only
removes it from the first subform in the If toggle51 = 0 event list
(currently, frmMainFormSubE in the code below, but if I change the
order,
it
will remove from any other).
So how would I go about getting the filter removed from all five
subforms
at
once?

Here is the code:
Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" & glDiscipline &
"'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
Me.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Perhaps the filter is being removed, but you need to scroll up to see the
previous records? Does scrolling up show the other records?

Do you have the Navigation Buttons showing at the foot of your subforms?
Does the "(Filtered)" text get removed? Does the number of records increase
as it should when the filter is removed?

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

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

Ann in CA said:
I took the line you suggested out, but it did not help. Here's the weird
part: The filter is removed when I click the button, but only the first
subform refreshes--in each subform I have headers that sort the columns
ascending or descending. So I click to remove the filter, and no
additional
records show up, but if I click a header in any of the non-refreshed
subforms, then the records show up?

Allen Browne said:
Okay, if you have not applied a Filter to your main form, your issue is
not
the bug I referred to. Finding a record by using the bookmark does not
involve applying a filter, so it must be a different issue.

However, your code does turn the main form's FilterOn property off in the
case where Toggle51 = 0. You might want to omit this line.

Visually, you should see "(Filtered)" in the horizontal scrollbar of the
subforms (next to the navigation buttons) when the subform is filtered.
Do
you still see this after running your code to turn the filters off?

If you want to set the the RecordSource, it would be somehing like this:
Dim strSql As String
strSql = "SELECT * FROM Table2 WHERE Table2.Discipline <> " & _
glDiscipline & ";"
Me.[Sub1].Form.RecordSource = strSql

Ann in CA said:
Allen, I thought I had read through that bug on your site and made sure
I
was
avoiding it, but I could be mistaken. There is no filter on my main
form,
and
the property AllowFilters is set to no, but there is a combo box with a
recordset clone to bookmark whichever as the first record?
Hate to sound silly, but I am rather a novice--in a nutshell, how would
I
set the recordsource?

:

Ann, I think you have hit this bug in Access:
FilterOn not set/removed correctly for form and subform
combination
described at:
http://allenbrowne.com/bug-02.html

Some workarounds:
a) Avoid filtering both the main form and the subforms.
b) Set the RecordSource instead of applying a filter.

Hello,
I have a form with 5 subforms all based on different tables. There
is a
toggle at the top of the form to "Apply group filter" where I would
like
all
5 subforms to filter out records whose discipline is not equal to
the
user's
discipline (stored in glDiscipline). The filter applies fine to all
five
subforms, but when I click the button again to remove the filter, it
only
removes it from the first subform in the If toggle51 = 0 event list
(currently, frmMainFormSubE in the code below, but if I change the
order,
it
will remove from any other).
So how would I go about getting the filter removed from all five
subforms
at
once?

Here is the code:
Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
Me.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Ann in CA

To test this, I have two records on the subform, one matches and one doesn't.
My subform record navigation says "record 1 of 2." When I click the button,
the filter is applied, one record disappears, and navigation says "record 1
of 1 (filtered)". When I click the button again, it says "record 1 of 1" and
my other record doesn't show up (except on whichever tab of the main form the
"filteron = false" line is directily under the line "If Me.Toggle51 = 0 Then"

Then I click on a header (clicking the subform itself doesn't do anything)
in the subform whose onclick function is set to
=SetSortOrder("[ProgramName]") and voila! My missing record and "record 2 of
2" are restored...

I'm perplexed...

Allen Browne said:
Perhaps the filter is being removed, but you need to scroll up to see the
previous records? Does scrolling up show the other records?

Do you have the Navigation Buttons showing at the foot of your subforms?
Does the "(Filtered)" text get removed? Does the number of records increase
as it should when the filter is removed?

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

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

Ann in CA said:
I took the line you suggested out, but it did not help. Here's the weird
part: The filter is removed when I click the button, but only the first
subform refreshes--in each subform I have headers that sort the columns
ascending or descending. So I click to remove the filter, and no
additional
records show up, but if I click a header in any of the non-refreshed
subforms, then the records show up?

Allen Browne said:
Okay, if you have not applied a Filter to your main form, your issue is
not
the bug I referred to. Finding a record by using the bookmark does not
involve applying a filter, so it must be a different issue.

However, your code does turn the main form's FilterOn property off in the
case where Toggle51 = 0. You might want to omit this line.

Visually, you should see "(Filtered)" in the horizontal scrollbar of the
subforms (next to the navigation buttons) when the subform is filtered.
Do
you still see this after running your code to turn the filters off?

If you want to set the the RecordSource, it would be somehing like this:
Dim strSql As String
strSql = "SELECT * FROM Table2 WHERE Table2.Discipline <> " & _
glDiscipline & ";"
Me.[Sub1].Form.RecordSource = strSql

Allen, I thought I had read through that bug on your site and made sure
I
was
avoiding it, but I could be mistaken. There is no filter on my main
form,
and
the property AllowFilters is set to no, but there is a combo box with a
recordset clone to bookmark whichever as the first record?
Hate to sound silly, but I am rather a novice--in a nutshell, how would
I
set the recordsource?

:

Ann, I think you have hit this bug in Access:
FilterOn not set/removed correctly for form and subform
combination
described at:
http://allenbrowne.com/bug-02.html

Some workarounds:
a) Avoid filtering both the main form and the subforms.
b) Set the RecordSource instead of applying a filter.

Hello,
I have a form with 5 subforms all based on different tables. There
is a
toggle at the top of the form to "Apply group filter" where I would
like
all
5 subforms to filter out records whose discipline is not equal to
the
user's
discipline (stored in glDiscipline). The filter applies fine to all
five
subforms, but when I click the button again to remove the filter, it
only
removes it from the first subform in the If toggle51 = 0 event list
(currently, frmMainFormSubE in the code below, but if I change the
order,
it
will remove from any other).
So how would I go about getting the filter removed from all five
subforms
at
once?

Here is the code:
Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
Me.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Ann in CA

So I have just found that if I add the line me.filteron = false as the very
first line under the toggle off value, AND keep all the lines underneath, it
works as I want it to...not sure why or how, but it does:


Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.FilterOn = False
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub


Ann in CA said:
To test this, I have two records on the subform, one matches and one doesn't.
My subform record navigation says "record 1 of 2." When I click the button,
the filter is applied, one record disappears, and navigation says "record 1
of 1 (filtered)". When I click the button again, it says "record 1 of 1" and
my other record doesn't show up (except on whichever tab of the main form the
"filteron = false" line is directily under the line "If Me.Toggle51 = 0 Then"

Then I click on a header (clicking the subform itself doesn't do anything)
in the subform whose onclick function is set to
=SetSortOrder("[ProgramName]") and voila! My missing record and "record 2 of
2" are restored...

I'm perplexed...

Allen Browne said:
Perhaps the filter is being removed, but you need to scroll up to see the
previous records? Does scrolling up show the other records?

Do you have the Navigation Buttons showing at the foot of your subforms?
Does the "(Filtered)" text get removed? Does the number of records increase
as it should when the filter is removed?

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

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

Ann in CA said:
I took the line you suggested out, but it did not help. Here's the weird
part: The filter is removed when I click the button, but only the first
subform refreshes--in each subform I have headers that sort the columns
ascending or descending. So I click to remove the filter, and no
additional
records show up, but if I click a header in any of the non-refreshed
subforms, then the records show up?

:

Okay, if you have not applied a Filter to your main form, your issue is
not
the bug I referred to. Finding a record by using the bookmark does not
involve applying a filter, so it must be a different issue.

However, your code does turn the main form's FilterOn property off in the
case where Toggle51 = 0. You might want to omit this line.

Visually, you should see "(Filtered)" in the horizontal scrollbar of the
subforms (next to the navigation buttons) when the subform is filtered.
Do
you still see this after running your code to turn the filters off?

If you want to set the the RecordSource, it would be somehing like this:
Dim strSql As String
strSql = "SELECT * FROM Table2 WHERE Table2.Discipline <> " & _
glDiscipline & ";"
Me.[Sub1].Form.RecordSource = strSql

Allen, I thought I had read through that bug on your site and made sure
I
was
avoiding it, but I could be mistaken. There is no filter on my main
form,
and
the property AllowFilters is set to no, but there is a combo box with a
recordset clone to bookmark whichever as the first record?
Hate to sound silly, but I am rather a novice--in a nutshell, how would
I
set the recordsource?

:

Ann, I think you have hit this bug in Access:
FilterOn not set/removed correctly for form and subform
combination
described at:
http://allenbrowne.com/bug-02.html

Some workarounds:
a) Avoid filtering both the main form and the subforms.
b) Set the RecordSource instead of applying a filter.

Hello,
I have a form with 5 subforms all based on different tables. There
is a
toggle at the top of the form to "Apply group filter" where I would
like
all
5 subforms to filter out records whose discipline is not equal to
the
user's
discipline (stored in glDiscipline). The filter applies fine to all
five
subforms, but when I click the button again to remove the filter, it
only
removes it from the first subform in the If toggle51 = 0 event list
(currently, frmMainFormSubE in the code below, but if I change the
order,
it
will remove from any other).
So how would I go about getting the filter removed from all five
subforms
at
once?

Here is the code:
Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
Me.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Ann in CA

So I have just found that by adding the line me.filteron = false as the very
first line under the negative toggle value, and keeping all of the individual
filter-off lines, it works!!! Not sure how or why, but the positive is that
there will never be a filter on the main form so if this is the workaround I
have to use, then I guess it's fine?


Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" & glDiscipline & "'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then

Me.FilterOn = False

Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub


Ann in CA said:
To test this, I have two records on the subform, one matches and one doesn't.
My subform record navigation says "record 1 of 2." When I click the button,
the filter is applied, one record disappears, and navigation says "record 1
of 1 (filtered)". When I click the button again, it says "record 1 of 1" and
my other record doesn't show up (except on whichever tab of the main form the
"filteron = false" line is directily under the line "If Me.Toggle51 = 0 Then"

Then I click on a header (clicking the subform itself doesn't do anything)
in the subform whose onclick function is set to
=SetSortOrder("[ProgramName]") and voila! My missing record and "record 2 of
2" are restored...

I'm perplexed...

Allen Browne said:
Perhaps the filter is being removed, but you need to scroll up to see the
previous records? Does scrolling up show the other records?

Do you have the Navigation Buttons showing at the foot of your subforms?
Does the "(Filtered)" text get removed? Does the number of records increase
as it should when the filter is removed?

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

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

Ann in CA said:
I took the line you suggested out, but it did not help. Here's the weird
part: The filter is removed when I click the button, but only the first
subform refreshes--in each subform I have headers that sort the columns
ascending or descending. So I click to remove the filter, and no
additional
records show up, but if I click a header in any of the non-refreshed
subforms, then the records show up?

:

Okay, if you have not applied a Filter to your main form, your issue is
not
the bug I referred to. Finding a record by using the bookmark does not
involve applying a filter, so it must be a different issue.

However, your code does turn the main form's FilterOn property off in the
case where Toggle51 = 0. You might want to omit this line.

Visually, you should see "(Filtered)" in the horizontal scrollbar of the
subforms (next to the navigation buttons) when the subform is filtered.
Do
you still see this after running your code to turn the filters off?

If you want to set the the RecordSource, it would be somehing like this:
Dim strSql As String
strSql = "SELECT * FROM Table2 WHERE Table2.Discipline <> " & _
glDiscipline & ";"
Me.[Sub1].Form.RecordSource = strSql

Allen, I thought I had read through that bug on your site and made sure
I
was
avoiding it, but I could be mistaken. There is no filter on my main
form,
and
the property AllowFilters is set to no, but there is a combo box with a
recordset clone to bookmark whichever as the first record?
Hate to sound silly, but I am rather a novice--in a nutshell, how would
I
set the recordsource?

:

Ann, I think you have hit this bug in Access:
FilterOn not set/removed correctly for form and subform
combination
described at:
http://allenbrowne.com/bug-02.html

Some workarounds:
a) Avoid filtering both the main form and the subforms.
b) Set the RecordSource instead of applying a filter.

Hello,
I have a form with 5 subforms all based on different tables. There
is a
toggle at the top of the form to "Apply group filter" where I would
like
all
5 subforms to filter out records whose discipline is not equal to
the
user's
discipline (stored in glDiscipline). The filter applies fine to all
five
subforms, but when I click the button again to remove the filter, it
only
removes it from the first subform in the If toggle51 = 0 event list
(currently, frmMainFormSubE in the code below, but if I change the
order,
it
will remove from any other).
So how would I go about getting the filter removed from all five
subforms
at
once?

Here is the code:
Private Sub Toggle51_Click()
On Error GoTo Proc_Error
If Me.Toggle51 = -1 Then
Me.frmMainFormSubA.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubA.Form.FilterOn = True
Me.frmMainFormSubB.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubB.Form.FilterOn = True
Me.frmMainFormSubC.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubC.Form.FilterOn = True
Me.frmMainFormSubD.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubD.Form.FilterOn = True
Me.frmMainFormSubE.Form.Filter = "[Discipline] = '" &
glDiscipline &
"'"
Me.frmMainFormSubE.Form.FilterOn = True
End If
If Me.Toggle51 = 0 Then
Me.frmMainFormSubE.Form.FilterOn = False
Me.frmMainFormSubA.Form.FilterOn = False
Me.frmMainFormSubB.Form.FilterOn = False
Me.frmMainFormSubC.Form.FilterOn = False
Me.frmMainFormSubD.Form.FilterOn = False
Me.FilterOn = False
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox Err.Number & Err.Description
End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Can't clear filter from multiple subforms with code. 2
Filtering and Removing Filter based on a combo box 3
Toggle Filter in multiple subforms 3
Selecting current record in subform after removing filter 1
Mimic Split Form with Two Subforms 0
same filter code for 2 subforms in 1 mainform - only 1 work 2
Looping through subforms ? 4
Subform filtering on form with multiple (tabbed) subforms; filteron property turns false at random 3

Top