Subform filtering on form with multiple (tabbed) subforms; filteron property turns false at random

[Alex]

Hi,

I have a (main) form with a tab control. On each page of the tabcontrol
there's a subform. Each subform (datasheet view) has it's own recordsource
(simple query or table). All of the subforms BUT ONE are ment to do simple
filter operations on the recordsource feeding the individual forms. ONE of
the subforms has a more complex query as recordsource involving the tables
and queries of the other subforms. There's no master detail connection
between the main form and the subforms. When the user rightclicks a field
in a subform, and the field is of type combobox, i'll handle the applyfilter
event, and present the user with a form containing a list with the
categories in the combobox, user can select the items and choose AND, OR,
NOT and ORNOT (this for keeping the selection within limits of filter
propery; see furtheron).
When user selects and clicks operand button i'll traverse the selecteditems
and give this back in a global variable as a string (e.g. "NOT ORG_CLASS_ID
in (345,347,1,7)"), I then close the (modal) form. This string is then
added to the subforms filter property, and the filter property is shown in a
(multiline) tekstbox on the page section of the tabcontrol of the main form.
So far this al works properly, and one can nicely handle filtering
operations.
In the last subform (the ONE subform) the user has some extra features. Here
the user can AND, OR, NOT the filters of the other subforms as the filter
property. The use of this is to allow the user to accumulate on nonrelated
criteria e.g.:
select (All the people walking on two legs AND wearing hats)
OR (everyone over 50 AND wearing gloves)
To accomodate the user in doing multiple rounds of criteria i have a button
on the main form, thats supposed to "reset" the filter on all the other
subforms (but not the ONE), so a new fresh selectionround can be started.
I'm using code like for the reset button

dim myForm as form
set myForm = me.frmSelectionORG.form
myform.filter = ""
myform.filteron = false ' I've done this the other way around too; e.g.
first filteron=false then filter = ""
set myForm = me.frmSelectionRES.form
[etc..]
... actually i've put this in a sub, which i call for all the formnames, but
the result is the same.

In my testsituation, each of the subforms has a filter. If I click the
"reset" button, only the subform on the first page of the tabcontrol gets
unfiltered. The rest of the subforms kinda loose their filterON property
(when traced its false), however the filtering remains. Sometimes (when i
seem to get lucky ;-))) even TWO subforms get unfiltered (which means 4 do
not)
As a bypass, I do stuff like this.

public sub RemovetheFilterAnyhow(frmSub as form)
dim strGRR as string 'sound i'm making when
stuf like this happens
strGRR = frmSub.recordset.fields(1).name 'get the name of the first field
of the recordset,
strGRR = strGRR & " IS NULL" ' I'm no wizard, but i believe
this condition will never deliver a type mismatch
frmSub.filter = strGRR
frmSub.filteron = true
frmSub.filter = ""
frmSub.filteron = False
end sub

So i create a dummy filter that does work, but this means that there's an
extra round of filtering..
After all they ARE gonna kill me for filtering instead of using dynamic
queries for selection, now they're gonna kill me Twice! ;-((
(I'ts not that simple, I need to filter, for i remove some parts of the
filter in some cases, to give the user a clear overview of its actions)

I've searched google, and read quite some stuff there, but it seems the odds
are inconclusive..

Is there anyone out there that can clarify on the subject?!
(I will stay alive for al long as its needed to read all replies in the
newsgroup)
Thanks!
Alex.

 

[Allen Browne]

Hi Alex

When you specify:
myform.Filter
you probably expect that Access could not possibly misinterpret which form
you are referring to. Unfortunately, the line is intercepted by some middle
layer in Access which does not apply the Filter to the form you specified,
but makes some guesses about what it is supposed to do and gives it a shot.
The result (as you found) is not reliable when you are working with multiple
subforms in forms, or with multiple instances of forms or reports.

For my experience with the filter bugs as far as I have been able to trace
them, see:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

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

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

Hi,

I have a (main) form with a tab control. On each page of the tabcontrol
there's a subform. Each subform (datasheet view) has it's own recordsource
(simple query or table). All of the subforms BUT ONE are ment to do simple
filter operations on the recordsource feeding the individual forms. ONE of
the subforms has a more complex query as recordsource involving the tables
and queries of the other subforms. There's no master detail connection
between the main form and the subforms. When the user rightclicks a field
in a subform, and the field is of type combobox, i'll handle the applyfilter
event, and present the user with a form containing a list with the
categories in the combobox, user can select the items and choose AND, OR,
NOT and ORNOT (this for keeping the selection within limits of filter
propery; see furtheron).
When user selects and clicks operand button i'll traverse the selecteditems
and give this back in a global variable as a string (e.g. "NOT ORG_CLASS_ID
in (345,347,1,7)"), I then close the (modal) form. This string is then
added to the subforms filter property, and the filter property is shown in a
(multiline) tekstbox on the page section of the tabcontrol of the main form.
So far this al works properly, and one can nicely handle filtering
operations.
In the last subform (the ONE subform) the user has some extra features. Here
the user can AND, OR, NOT the filters of the other subforms as the filter
property. The use of this is to allow the user to accumulate on nonrelated
criteria e.g.:
select (All the people walking on two legs AND wearing hats)
OR (everyone over 50 AND wearing gloves)
To accomodate the user in doing multiple rounds of criteria i have a button
on the main form, thats supposed to "reset" the filter on all the other
subforms (but not the ONE), so a new fresh selectionround can be started.
I'm using code like for the reset button

dim myForm as form
set myForm = me.frmSelectionORG.form
myform.filter = ""
myform.filteron = false ' I've done this the other way around too; e.g.
first filteron=false then filter = ""
set myForm = me.frmSelectionRES.form
[etc..]
.. actually i've put this in a sub, which i call for all the formnames, but
the result is the same.

In my testsituation, each of the subforms has a filter. If I click the
"reset" button, only the subform on the first page of the tabcontrol gets
unfiltered. The rest of the subforms kinda loose their filterON property
(when traced its false), however the filtering remains. Sometimes (when i
seem to get lucky ;-))) even TWO subforms get unfiltered (which means 4 do
not)
As a bypass, I do stuff like this.

public sub RemovetheFilterAnyhow(frmSub as form)
dim strGRR as string 'sound i'm making when
stuf like this happens
strGRR = frmSub.recordset.fields(1).name 'get the name of the first field
of the recordset,
strGRR = strGRR & " IS NULL" ' I'm no wizard, but i believe
this condition will never deliver a type mismatch
frmSub.filter = strGRR
frmSub.filteron = true
frmSub.filter = ""
frmSub.filteron = False
end sub

So i create a dummy filter that does work, but this means that there's an
extra round of filtering..
After all they ARE gonna kill me for filtering instead of using dynamic
queries for selection, now they're gonna kill me Twice! ;-((
(I'ts not that simple, I need to filter, for i remove some parts of the
filter in some cases, to give the user a clear overview of its actions)

I've searched google, and read quite some stuff there, but it seems the odds
are inconclusive..

Is there anyone out there that can clarify on the subject?!
(I will stay alive for al long as its needed to read all replies in the
newsgroup)
Thanks!
Alex.

 

[Marshall Barton]

This is a tough one, Alex. When Access first introduced the
Filter property, I thought I could clean up a bunch of code,
but, after a lot of problems similar to yours, I gave up and
went back to my old A2 approach of setting the subform's
RecordSource property instead.

Find a place to keep a subform's unfiltered SQL string (e.g.
use the Load event to copy it from the RecordSource to its
Tag property). Then, when you want to apply a filter
concatenate the word "WHERE" and your constructed cirteria
to the original SQL to reset the subform's RecordSource.
--
Marsh
MVP [MS Access]

I have a (main) form with a tab control. On each page of the tabcontrol
there's a subform. Each subform (datasheet view) has it's own recordsource
(simple query or table). All of the subforms BUT ONE are ment to do simple
filter operations on the recordsource feeding the individual forms. ONE of
the subforms has a more complex query as recordsource involving the tables
and queries of the other subforms. There's no master detail connection
between the main form and the subforms. When the user rightclicks a field
in a subform, and the field is of type combobox, i'll handle the applyfilter
event, and present the user with a form containing a list with the
categories in the combobox, user can select the items and choose AND, OR,
NOT and ORNOT (this for keeping the selection within limits of filter
propery; see furtheron).
When user selects and clicks operand button i'll traverse the selecteditems
and give this back in a global variable as a string (e.g. "NOT ORG_CLASS_ID
in (345,347,1,7)"), I then close the (modal) form. This string is then
added to the subforms filter property, and the filter property is shown in a
(multiline) tekstbox on the page section of the tabcontrol of the main form.
So far this al works properly, and one can nicely handle filtering
operations.
In the last subform (the ONE subform) the user has some extra features. Here
the user can AND, OR, NOT the filters of the other subforms as the filter
property. The use of this is to allow the user to accumulate on nonrelated
criteria e.g.:
select (All the people walking on two legs AND wearing hats)
OR (everyone over 50 AND wearing gloves)
To accomodate the user in doing multiple rounds of criteria i have a button
on the main form, thats supposed to "reset" the filter on all the other
subforms (but not the ONE), so a new fresh selectionround can be started.
I'm using code like for the reset button

dim myForm as form
set myForm = me.frmSelectionORG.form
myform.filter = ""
myform.filteron = false ' I've done this the other way around too; e.g.
first filteron=false then filter = ""
set myForm = me.frmSelectionRES.form
[etc..]
.. actually i've put this in a sub, which i call for all the formnames, but
the result is the same.

In my testsituation, each of the subforms has a filter. If I click the
"reset" button, only the subform on the first page of the tabcontrol gets
unfiltered. The rest of the subforms kinda loose their filterON property
(when traced its false), however the filtering remains. Sometimes (when i
seem to get lucky ;-))) even TWO subforms get unfiltered (which means 4 do
not)
As a bypass, I do stuff like this.

public sub RemovetheFilterAnyhow(frmSub as form)
dim strGRR as string 'sound i'm making when
stuf like this happens
strGRR = frmSub.recordset.fields(1).name 'get the name of the first field
of the recordset,
strGRR = strGRR & " IS NULL" ' I'm no wizard, but i believe
this condition will never deliver a type mismatch
frmSub.filter = strGRR
frmSub.filteron = true
frmSub.filter = ""
frmSub.filteron = False
end sub

So i create a dummy filter that does work, but this means that there's an
extra round of filtering..
After all they ARE gonna kill me for filtering instead of using dynamic
queries for selection, now they're gonna kill me Twice! ;-((
(I'ts not that simple, I need to filter, for i remove some parts of the
filter in some cases, to give the user a clear overview of its actions)

 

[Alex]

Thanks for the replies.
I'm going for the recordset.
Wouldn't it be a good idea if Microsoft fixed kinda buggy stuff like this?
This filter property can indeed be very handy, when trusted ;-(. I'm in
favor of backward compatibility, not backward buggability! I know some buggy
behaviour gets us access developers jobs, but he, so does cleaning up the
workarounds..
Thanks again and let the stars shine bright for all of us..
Alex.

Hi,

I have a (main) form with a tab control. On each page of the tabcontrol
there's a subform. Each subform (datasheet view) has it's own recordsource
(simple query or table). All of the subforms BUT ONE are ment to do simple

[.. see pervious poating]