Filter by selection is inappropriately "sticky"

[Peter Danes]

Apologies for the repeated posting - I tried this in .Forms a few weeks ago
and got no response. Maybe someone in one of these other groups will have an
idea, or maybe it's more to do with a bad design decision somewhere rather
than the form itself. It's not a huge problem, I've invented workarounds,
but it still bugs me. If anyone can shed some light on the subject, I'd love
to hear about it.



I have a most unusual problem trying to filter a form. I've scanned the
archives and not found anyone with the same problem, although there are a
few similar ones.



The pertinent data structure is as table of lichens, with attendant genus
and species tables in one to many relationships, with a one to many also
between genus and species, as follows:



Genus 1 to many Lichens



Genus 1 to many Species



Species 1 to many Lichens



There is a form, whose recordset is simply the Lichens table. On the form
are two comboboxes, bound to the genus and species codes in the Lichens
table and populated by SQL queries from the Genus and Species tables. The
Genus combobox runs unattended; the Species combobox is requeried in the
form's OnCurrent event handler to limit the allowed species to those
appropriate for the genus. Both comboboxes are bound to their second column.



Here is the table structure and SQL for each - ZapisNalezu is the name of
both the form and main table, Rod means Genus, Druh means Species, Rody and
Druhy are plurals and Kod means code.



TABLE ZapisNalezu:



IDCislo (PK)



KodRodu (FK to Rody)



KodDruhu (FK to Druhy)



Etc.



TABLE Rody:



KodRodu(PK)



Rod



TABLE Druhy:



KodRodu(PK)



KodDruhu(PK)



Druh



Etc.



Genus combobox:



SELECT Rody.Rod, Rody.KodRodu



FROM Rody



ORDER BY Rody.Rod;



Species combobox:



SELECT Druhy.Druh, Druhy.KodDruhu, Druhy.KodRodu



FROM Druhy



WHERE (((Druhy.KodRodu)=[forms]![ZapisNalezu]![cboRod]))



ORDER BY Druhy.Druh;



The form and comboboxes behave quite nicely except when I try to filter the
recordset on the comboboxes. When I filter on anything else, it works fine.



I have a number of custom filters accessible from a custom menu bar as well
as filter buttons here and there on the form that activate special filters
based on what the user needed at one time or another. Everything there works
perfectly.



The problem arises when I attempt to use the "Filter by Selection"
(lightning bolt filter) button to filter recordsets by genus and species. I
move to the Genus combobox and click FBS, works great - I get only those
records with the same genus as the one displayed. I then move to the Species
combobox and click FBS again, still works great - my recordset is now down
to those with the same genus AND species, exactly as I would expect. I then
click the filter off, all records are displayed. I then move to another
record with a different classification, go to the Genus combobox, click FBS,
and once again get only those records with the same genus as the new record.



I then move to the Species combobox, click FBS and BOOM!, no records! None,
not even the one I was looking at when I clicked the filter button.



After a great deal of hair-pulling and invective, I discovered that somehow,
somewhere, the original Genus filter is still hiding and gets activated when
I click the FBS button in the Species combobox the second time. The result
is that the filter is attempting to get all records which have
non-corresponding genus and species codes, of which there are none, so the
form goes blank.



I set the forms properties to be visible at run time and examined what is
going into the Filter property:



Starts blank [6345 records]



After first FBS click in Genus: [71 records]



((Lookup_cboRod.Rod="Chaenotheca"))



After first FBS click in Species: [32 records]



(((Lookup_cboRod.Rod="Chaenotheca"))) AND
((Lookup_cboDruh.Druh="ferruginea"))



After clicking filter off (same, but all records displayed) [6345 records]



(((Lookup_cboRod.Rod="Chaenotheca"))) AND
((Lookup_cboDruh.Druh="ferruginea"))



After second FBS click in Genus: [76 records]



((Lookup_cboRod.Rod="Arthonia"))



After second FBS click in Species: [nothing]



(((Lookup_cboRod.Rod="Arthonia"))) AND ((Lookup_cboDruh.Druh="spadicea"))



Looks correct, but at this point there are no records displayed (blank form)
despite there being several such records in the table. I was just looking at
one, after all, and simply clicked the standard Access FBS button. There is
no code attached to any of this.



Manually changing the filter text to this:



((Lookup_cboDruh.Druh like "*"))



Gives me the same recordset [71 records] that I got in the first Genus FBS
filter, that is, all the records from this:



((Lookup_cboRod.Rod="Chaenotheca"))



When I close and re-open the form, everything resets and the first filter
attempts work correctly, subsequent ones do not.



It gets even weirder. If I click only the Species FBS, I get: [32 records]



((Lookup_cboDruh.Druh="ferruginea"))



I turn the filter off, move to another record, click FBS Species again and
get:



((Lookup_cboDruh.Druh="spadicea"))



And no records displayed. Manually changing the filter text again to:



((Lookup_cboDruh.Druh like "*"))



then clicking the filter off and on again gives me the recordset that I
would get by clicking FBS on the Genus combobox, that is, the same [71
records] that I got by using this filter:



((Lookup_cboRod.Rod="Chaenotheca"))



That is, a recordset I have never yet asked for.



If I select a species that occurs in more than one genus, it gets stranger
yet:



There are sets of records that have this taxonomy:



Cornutispora lichenicola



And others that have this:



Vouauxiella lichenicola



If I go to one of the records with the Cornutispora genus and click in FBS
in the Species combobox, I get only records with the Cornutispora
lichenicola genus and species, instead of all lichenicola species,
regardless of genus, which is what I would have expected. I then turn off
the filter, move to a record with the Vouauxiella lichenicola genus and
species, click Species FBS again and what do I get? The Cornutispora
recordset again, not the Vouauxiella set. The first genus is -somehow-
staying attached to the filtering mechanism, no matter what I do to clear it
away. I've even gone to the VBA immediate window to test and manually set or
clear the filter property. It's always set correctly, same as the properties
window shows (no extra lines hidden away by line feeds) but the behavior
remains the same. The fact that I get only one genus when I filter by common
species is likely a function of the fact that the two species are not
actually the same, they have different codes even though they have the same
name. I don't agree with the concept, I think it should filter by what is
shown, although it's probably reasonable enough, given what's actually under
the hood of the comboboxes. But the sticking of the genus filter is a
problem. Has anyone run into this? It happens 100% reliably in the described
circumstances, on several different machines with Win2K or WinXP, Access
2000 or Access 2003, all patches and updates current.



I found this



http://support.microsoft.com/kb/139042



MSKB article describing malfunctioning combobox filter behavior, but with
comboboxes populated from a list. I use queries, either stored or SQL right
in the combobox's rowsource property. Both ways exhibit the same behavior.



--



Pete



This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

 

[Allen Browne]

Peter, that's a very long post, and I haven't read the whole thing in
detail, but I take it the problem is that Access does not correctly clear
the form's Filter property when you click the Remove Filter button after
using Filter By Selection repeatedly with combos.

Judging from your example filters, this happens with combos where the bound
column is zero-width?

One thing that confused me: you say this happens under A2000 as well as
A2003. I had the impression this kind of filter was not introduced until
A2002.

To address the issue, it would be good to ensure the Name AutoCorrect boxes
are unchecked under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact/Repair

Next, open the table in design view. Open the Properties box, and clear
anything in the Filter and Order By properties. Save and Close.

Next, open the form in design view. Open the Properties box, and clear
anything in the Filter and Order By properties. Save and Close.

Now we need to discover whether this filter bug is related only to combos
that have the bound column zero-width. In form design view, change the
Column Widths property so the bound column is (say) half an inch. The combos
will probably show numbers now, and only show the text from the other
columns when dropped down.

Post back and tell us whether the filter bug shows under those conditions.

Then reset the Column Widths the way they were, and tell us whether the bug
comes back.

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

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

Apologies for the repeated posting - I tried this in .Forms a few weeks
ago
and got no response. Maybe someone in one of these other groups will have
an
idea, or maybe it's more to do with a bad design decision somewhere rather
than the form itself. It's not a huge problem, I've invented workarounds,
but it still bugs me. If anyone can shed some light on the subject, I'd
love
to hear about it.

I have a most unusual problem trying to filter a form. I've scanned the
archives and not found anyone with the same problem, although there are a
few similar ones.

The pertinent data structure is as table of lichens, with attendant genus
and species tables in one to many relationships, with a one to many also
between genus and species, as follows:

Genus 1 to many Lichens

Genus 1 to many Species

Species 1 to many Lichens

There is a form, whose recordset is simply the Lichens table. On the form
are two comboboxes, bound to the genus and species codes in the Lichens
table and populated by SQL queries from the Genus and Species tables. The
Genus combobox runs unattended; the Species combobox is requeried in the
form's OnCurrent event handler to limit the allowed species to those
appropriate for the genus. Both comboboxes are bound to their second
column.

Here is the table structure and SQL for each - ZapisNalezu is the name of
both the form and main table, Rod means Genus, Druh means Species, Rody
and
Druhy are plurals and Kod means code.

TABLE ZapisNalezu:
IDCislo (PK)
KodRodu (FK to Rody)
KodDruhu (FK to Druhy)
Etc.

TABLE Rody:
KodRodu(PK)
Rod

TABLE Druhy:
KodRodu(PK)
KodDruhu(PK)
Druh
Etc.

Genus combobox:
SELECT Rody.Rod, Rody.KodRodu
FROM Rody
ORDER BY Rody.Rod;

Species combobox:
SELECT Druhy.Druh, Druhy.KodDruhu, Druhy.KodRodu
FROM Druhy
WHERE (((Druhy.KodRodu)=[forms]![ZapisNalezu]![cboRod]))
ORDER BY Druhy.Druh;

The form and comboboxes behave quite nicely except when I try to filter
the
recordset on the comboboxes. When I filter on anything else, it works
fine.

I have a number of custom filters accessible from a custom menu bar as
well
as filter buttons here and there on the form that activate special filters
based on what the user needed at one time or another. Everything there
works
perfectly.

The problem arises when I attempt to use the "Filter by Selection"
(lightning bolt filter) button to filter recordsets by genus and species.
I
move to the Genus combobox and click FBS, works great - I get only those
records with the same genus as the one displayed. I then move to the
Species
combobox and click FBS again, still works great - my recordset is now down
to those with the same genus AND species, exactly as I would expect. I
then
click the filter off, all records are displayed. I then move to another
record with a different classification, go to the Genus combobox, click
FBS,
and once again get only those records with the same genus as the new
record.

I then move to the Species combobox, click FBS and BOOM!, no records!
None,
not even the one I was looking at when I clicked the filter button.

After a great deal of hair-pulling and invective, I discovered that
somehow,
somewhere, the original Genus filter is still hiding and gets activated
when
I click the FBS button in the Species combobox the second time. The result
is that the filter is attempting to get all records which have
non-corresponding genus and species codes, of which there are none, so the
form goes blank.

I set the forms properties to be visible at run time and examined what is
going into the Filter property:
Starts blank [6345 records]

After first FBS click in Genus: [71 records]
((Lookup_cboRod.Rod="Chaenotheca"))

After first FBS click in Species: [32 records]
(((Lookup_cboRod.Rod="Chaenotheca"))) AND
((Lookup_cboDruh.Druh="ferruginea"))

After clicking filter off (same, but all records displayed) [6345 records]
(((Lookup_cboRod.Rod="Chaenotheca"))) AND
((Lookup_cboDruh.Druh="ferruginea"))

After second FBS click in Genus: [76 records]
((Lookup_cboRod.Rod="Arthonia"))

After second FBS click in Species: [nothing]
(((Lookup_cboRod.Rod="Arthonia"))) AND ((Lookup_cboDruh.Druh="spadicea"))

Looks correct, but at this point there are no records displayed (blank
form)
despite there being several such records in the table. I was just looking
at
one, after all, and simply clicked the standard Access FBS button. There
is
no code attached to any of this.

Manually changing the filter text to this:
((Lookup_cboDruh.Druh like "*"))

Gives me the same recordset [71 records] that I got in the first Genus FBS
filter, that is, all the records from this:
((Lookup_cboRod.Rod="Chaenotheca"))

When I close and re-open the form, everything resets and the first filter
attempts work correctly, subsequent ones do not.

It gets even weirder. If I click only the Species FBS, I get: [32 records]
((Lookup_cboDruh.Druh="ferruginea"))

I turn the filter off, move to another record, click FBS Species again and
get:
((Lookup_cboDruh.Druh="spadicea"))

And no records displayed. Manually changing the filter text again to:
((Lookup_cboDruh.Druh like "*"))

then clicking the filter off and on again gives me the recordset that I
would get by clicking FBS on the Genus combobox, that is, the same [71
records] that I got by using this filter:
((Lookup_cboRod.Rod="Chaenotheca"))

That is, a recordset I have never yet asked for.

If I select a species that occurs in more than one genus, it gets stranger
yet:

There are sets of records that have this taxonomy:
Cornutispora lichenicola

And others that have this:
Vouauxiella lichenicola

If I go to one of the records with the Cornutispora genus and click in FBS
in the Species combobox, I get only records with the Cornutispora
lichenicola genus and species, instead of all lichenicola species,
regardless of genus, which is what I would have expected. I then turn off
the filter, move to a record with the Vouauxiella lichenicola genus and
species, click Species FBS again and what do I get? The Cornutispora
recordset again, not the Vouauxiella set. The first genus is -somehow-
staying attached to the filtering mechanism, no matter what I do to clear
it
away. I've even gone to the VBA immediate window to test and manually set
or clear the filter property. It's always set correctly, same as the
properties
window shows (no extra lines hidden away by line feeds) but the behavior
remains the same. The fact that I get only one genus when I filter by
common
species is likely a function of the fact that the two species are not
actually the same, they have different codes even though they have the
same
name. I don't agree with the concept, I think it should filter by what is
shown, although it's probably reasonable enough, given what's actually
under
the hood of the comboboxes. But the sticking of the genus filter is a
problem. Has anyone run into this? It happens 100% reliably in the
described
circumstances, on several different machines with Win2K or WinXP, Access
2000 or Access 2003, all patches and updates current.

I found this
http://support.microsoft.com/kb/139042

MSKB article describing malfunctioning combobox filter behavior, but with
comboboxes populated from a list. I use queries, either stored or SQL
right
in the combobox's rowsource property. Both ways exhibit the same behavior.

 

[Peter Danes]

Hello Allen,



Thank you for responding. Let me see if I can summarize it a bit, then
answer your points inline.



The problem seems to be with -cascading- comboboxes, with the underlying
tables linked in one-to-many relationships. At least, that’s the only place
it happens. I have other, single comboboxes on the form and they filter
perfectly. It’s just this one set of Genus-Species cascades that misbehaves
this strange way.

... but I take it the problem is that Access does

not correctly clear the form's Filter property when

you click the Remove Filter button after using

Filter By Selection repeatedly with combos.

Exactly. The first instance works fine, all subsequent ones do not, even if
I meanwhile filter on some other control on the form. Once I FBS on
the -second- combobox in this cascaded set, all subsequent filter attempts
for a different value on this same control do not work, until I restart the
form. As long as I restrict filtering to the first combo (Genus), or
anything else on the form, everything is fine.

Judging from your example filters, this happens with

combos where the bound column is zero-width?

The bound column is the second one and I specify only one column in the
number of columns to display, so the width is not set by me. Here is the SQL
for that combo:



SELECT Druhy.Druh, Druhy.KodDruhu, Druhy.KodRodu

FROM Druhy

WHERE (((Druhy.KodRodu)=[forms]![ZapisNalezu]![cboRod]))

ORDER BY Druhy.Druh;



Druhy.Druh is displayed,

Druhy.KodDruhu is the bound column and

Druhy.KodRodu is the many side of the one-to-many link from the Genus table.

I requery this combo in the OnCurrent event to make sure I have only the
species appropriate for the current genus.

One thing that confused me: you say this happens

under A2000 as well as A2003. I had the impression

this kind of filter was not introduced until A2002.

Umm, not sure about that. I definitely have A2K on the machine I am using
right now, for instance, where I am testing your suggestions and the FBS
button is there. I’m not sure about the original release, I make a point of
keeping all these machines updated, so it may have been introduced in a
service pack, but it’s most definitely there now.

To address the issue, it would be good to ensure the

Name AutoCorrect boxes are unchecked under:

Tools | Options | General

I checked, it’s already off.

Then compact the database:

Tools | Database

Utilities | Compact/Repair

I do that regularly, but I just did it again.

Next, open the table in design view. Open the

Properties box, and clear anything in the Filter and

Order By properties. Save and Close.

The species table had a sort order set, nothing else had any orders or
filters. I cleared the order, recompacted the DB, no change.

Next, open the form in design view. Open the

Properties box, and clear anything in the Filter and

Order By properties. Save and Close.

They were already clear, I intercept the close button in my code and force
the user to close the form with a command button, which manually closes the
form without saving changes, precisely to keep filters and sort orders from
getting saved. I don’t know why Access makes this necessary, but even when
you clear those properties in code, they get saved anyway when the form
closes. They were getting activated occasionally on startup, so I added
this. The users bitched about not being able to use the X close, and I
agree, it’s pain in the fundament, but I couldn’t figure out any other way
to keep the form clean.

Now we need to discover whether this filter bug is

related only to combos that have the bound column

zero-width. In form design view, change the Column

Widths property so the bound column is (say) half an

inch. The combos will probably show numbers now, and

only show the text from the other columns when

dropped down. Post back and tell us whether the

filter bug shows under those conditions.

Interesting. I changed the combo to the:



SELECT Druhy.KodDruhu, Druhy.Druh, Druhy.KodRodu

FROM Druhy

WHERE (((Druhy.KodRodu)=[forms]![ZapisNalezu]![cboRod]))

ORDER BY Druhy.Druh;



so that the bound column is first and specified three non-zero column
widths. As you say, that makes it show the autonumber ID code, instead of
the species name, and the filtering mechanism does indeed work correctly
this way.

Then reset the Column Widths the way they were, and

tell us whether the bug comes back.

Yes it does. When I set the first column width back to 0, it show the
species name, but the filter malfunctions again, just as it did when the
bound column was the second one.



As a side note, I tried removing and restoring all the relationships between
the tables in various combinations. Nothing helped, the filter malfunctions
whether the tables are linked or not.



Filter by Form, incidentally, doesn’t work at all in this second combo, but
it does lock it up. That is, when I click the Filter by form Button, fill in
a species name and hit the filter button, it finds nothing, but when I
subsequently try FBS in the species field, it behaves just as if it had used
the FBS button the first time – the Genus box value is stuck somewhere,
although nothing shows in the filter properties.



So thanks, that was a good call, you’ve pretty well pinpointed the
circumstances of the problem. I would say it’s definitely a bug, since it
does work properly once, after startup. If it never worked, I might be
inclined to wonder if this is deliberate for some odd reason, but this seems
to be an instance of something internal not getting tidied up properly. Any
thoughts on whether there’s a way to make it work correctly? I tried
intercepting the ApplyFilter event and setting the column widths to “1;1;1”
and back to what they were, “0;something;something” but that didn’t work. I
confess that nothing else occurs to me that I could even try.



Pete

 

[Allen Browne]

Hi Pete

Right: you have this narrowed down to Filter-By-Selection, involving the
combination of cascading combos where the bound-column is zero-width. The
bug is therefore in the code Microsoft introduced in Access 2002 that uses
the Lookup_xxx format in the filter.

I don't see anyway to work around the bug other than to avoid the
combination of conditions that cause it. What I normally do is to add
unbound controls to the Form Header section for the filtering the fields you
know the user will want to use. Like the screenshot here:
http://allenbrowne.com/ser-62.html
That allows you to build the filter string based on the real value of the
combo (in its hidden bound column), which avoids the Lookup_xxx format, and
so circumvents the bug.

Microsoft fixed several of the filter bugs in Access 2007. It would be
interesting to trace whether they fixed this one. My guess is that they did
not, but I don't have time to test it right now.

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

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

Hello Allen,

Thank you for responding. Let me see if I can summarize it a bit, then
answer your points inline.

The problem seems to be with -cascading- comboboxes, with the underlying
tables linked in one-to-many relationships. At least, that’s the only
place
it happens. I have other, single comboboxes on the form and they filter
perfectly. It’s just this one set of Genus-Species cascades that
misbehaves
this strange way.

... but I take it the problem is that Access does
not correctly clear the form's Filter property when
you click the Remove Filter button after using
Filter By Selection repeatedly with combos.

Exactly. The first instance works fine, all subsequent ones do not, even
if
I meanwhile filter on some other control on the form. Once I FBS on
the -second- combobox in this cascaded set, all subsequent filter attempts
for a different value on this same control do not work, until I restart
the
form. As long as I restrict filtering to the first combo (Genus), or
anything else on the form, everything is fine.

Judging from your example filters, this happens with
combos where the bound column is zero-width?

The bound column is the second one and I specify only one column in the
number of columns to display, so the width is not set by me. Here is the
SQL
for that combo:
SELECT Druhy.Druh, Druhy.KodDruhu, Druhy.KodRodu
FROM Druhy
WHERE (((Druhy.KodRodu)=[forms]![ZapisNalezu]![cboRod]))
ORDER BY Druhy.Druh;

Druhy.Druh is displayed,
Druhy.KodDruhu is the bound column and
Druhy.KodRodu is the many side of the one-to-many link from the Genus
table.

I requery this combo in the OnCurrent event to make sure I have only the
species appropriate for the current genus.

One thing that confused me: you say this happens
under A2000 as well as A2003. I had the impression
this kind of filter was not introduced until A2002.

Umm, not sure about that. I definitely have A2K on the machine I am using
right now, for instance, where I am testing your suggestions and the FBS
button is there. I’m not sure about the original release, I make a point
of
keeping all these machines updated, so it may have been introduced in a
service pack, but it’s most definitely there now.

To address the issue, it would be good to ensure the
Name AutoCorrect boxes are unchecked under:
Tools | Options | General

I checked, it’s already off.

Then compact the database:
Tools | Database
Utilities | Compact/Repair

I do that regularly, but I just did it again.

Next, open the table in design view. Open the
Properties box, and clear anything in the Filter and
Order By properties. Save and Close.

The species table had a sort order set, nothing else had any orders or
filters. I cleared the order, recompacted the DB, no change.

Next, open the form in design view. Open the
Properties box, and clear anything in the Filter and
Order By properties. Save and Close.

They were already clear, I intercept the close button in my code and force
the user to close the form with a command button, which manually closes
the
form without saving changes, precisely to keep filters and sort orders
from
getting saved. I don’t know why Access makes this necessary, but even when
you clear those properties in code, they get saved anyway when the form
closes. They were getting activated occasionally on startup, so I added
this. The users bitched about not being able to use the X close, and I
agree, it’s pain in the fundament, but I couldn’t figure out any other way
to keep the form clean.

Now we need to discover whether this filter bug is
related only to combos that have the bound column
zero-width. In form design view, change the Column
Widths property so the bound column is (say) half an
inch. The combos will probably show numbers now, and
only show the text from the other columns when
dropped down. Post back and tell us whether the
filter bug shows under those conditions.

Interesting. I changed the combo to the:
SELECT Druhy.KodDruhu, Druhy.Druh, Druhy.KodRodu
FROM Druhy
WHERE (((Druhy.KodRodu)=[forms]![ZapisNalezu]![cboRod]))
ORDER BY Druhy.Druh;

so that the bound column is first and specified three non-zero column
widths. As you say, that makes it show the autonumber ID code, instead of
the species name, and the filtering mechanism does indeed work correctly
this way.

Then reset the Column Widths the way they were, and
tell us whether the bug comes back.

Yes it does. When I set the first column width back to 0, it show the
species name, but the filter malfunctions again, just as it did when the
bound column was the second one.

As a side note, I tried removing and restoring all the relationships
between
the tables in various combinations. Nothing helped, the filter
malfunctions
whether the tables are linked or not.

Filter by Form, incidentally, doesn’t work at all in this second combo,
but
it does lock it up. That is, when I click the Filter by form Button, fill
in
a species name and hit the filter button, it finds nothing, but when I
subsequently try FBS in the species field, it behaves just as if it had
used
the FBS button the first time – the Genus box value is stuck somewhere,
although nothing shows in the filter properties.

So thanks, that was a good call, you’ve pretty well pinpointed the
circumstances of the problem. I would say it’s definitely a bug, since it
does work properly once, after startup. If it never worked, I might be
inclined to wonder if this is deliberate for some odd reason, but this
seems
to be an instance of something internal not getting tidied up properly.
Any
thoughts on whether there’s a way to make it work correctly? I tried
intercepting the ApplyFilter event and setting the column widths to
“1;1;1”
and back to what they were, “0;something;something” but that didn’t work.
I
confess that nothing else occurs to me that I could even try.

Pete

 

[Petr Danes]

Hello Allen,

Right: you have this narrowed down to

Filter-By-Selection, involving the combination of

cascading combos where the bound-column is

zero-width. The bug is therefore in the code

Microsoft introduced in Access 2002 that uses the

Lookup_xxx format in the filter.

Hadn't heard of that bug; my original code used non-zero-width columns, but
with an other-than-first bound column, which is probably why I didn't turn
up anything from searching the archives. Apparently both ways activate the
same problem, though. I didn't know that the Lookup_xxx format wasn't in the
original A2K release. I didn't start using this feature until a couple of
years ago and when I discovered it, all the machines here already had it.

I don't see anyway to work around the bug other than

to avoid the combination of conditions that cause

it.

I came up with one, actually. After studying your answers and thinking about
it for a while, I came up with the idea of intercepting the filter event. It
works quite well, at least so far. All I did was write a small event handler
for the Apply Filter event. Here's the whole thing:



Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

If ApplyType = acApplyFilter And ActiveControl.Name = "cboDruh" Then _

Filter = "KodRodu=" & cboRod.Column(1) & " AND KodDruhu=" &
cboDruh.Column(1)

End Sub



As it turns out, the point where this event is activated is after Access
assembles the filter text, but before it is applied. The code intercepts the
ApplyFilter event and checks to see whether the filter is being applied
(acApplyFilter) or cleared (acShowAllRecords), and whether the active field
is the troublesome Species combobox. When that is the case, it simply
substitutes its own filter text which avoids the Lookup_xxx format
altogether and lets the filter soldier on with the modified text. Any other
filter activity passes through unmolested.



No problems apparent with it yet. It does exactly what the Lookup_xxx format
filter would do if it worked correctly and doesn't seem to have any side
effects. As an added bonus, it gives the appearance of a properly operating
FBS button to the user, so I don't have to deal with the hassle of educating
the user that FBS works like this, EXCEPT when...



There is one peculiarity, but it doesn't seem to affect the functioning of
the filter. My form has a toolbar with many of the standard Access filter
buttons, including FilterOn, FilterOff and FilterToggle. When a filter is
applied, then deactivated (doesn't matter how) then reactivated with the
FilterOn button rather than the FilterToggle button, subsequent presses of
the FilterToggle button always come through with ApplyFilter set to
acApplyFilter, even when the FilterToggle button is turning the filter off.
What it does to my code is that the filter text is unnecessarily set again,
but when my event code finishes and Access goes on about its business, it
turns the filter off, just like it's supposed to, even though the
ApplyFilter parameter says the filter is being turned on. And only when an
existing filter previously turned off is turned back on with the FilterOn
button. If it's always turned on with the FilterToggle button, the
ApplyFilter value is correct. Even after it starts misbehaving, if it's
turned off with the FilterOff button, the FilterToggle button goes back to
giving correct values. Weird. LSD in the Redmond water supply again?

What I normally do is to add unbound controls to the

Form Header section for the filtering the fields you

know the user will want to use. Like the screenshot

http://allenbrowne.com/ser-62.html

Yes, that works quite well. (Nice website, BTW, lots of good info there. It
went into my favorites folder immediately.) I had a separate command button
doing something like that as a workaround, but it's not the way I want the
app to operate. Controls should either work correctly or not be there at
all, and the FBS button is extremely convenient for this particular DB. Most
of the filtering the principal user of this DB needs to do is either
extremely simple or quite complex, very little in between. The simple stuff
is well handled by the FBS and NOT FBS buttons and the complex stuff is
often well beyond the scope of even Filter By Form, involving counts of
records in several linked tables, various Boolean combinations of codes,
comparisons between values in the current record and any of several other
values in linked records...



Creating a set of fill-in controls that would let the user flexibly define
such filter criteria as:



..Filter = "IDCislo IN (SELECT ZapisNalezu.IDCislo FROM Rody INNER JOIN
(Druhy INNER JOIN ZapisNalezu ON Druhy.KodDruhu = ZapisNalezu.KodDruhu) ON
Rody.KodRodu = ZapisNalezu.KodRodu) AND IDCislo NOT IN (SELECT
ZapisNalezu.IDCislo FROM Rody INNER JOIN (Druhy INNER JOIN ZapisNalezu ON
Druhy.KodDruhu = ZapisNalezu.KodDruhu) ON (Rody.KodRodu =
ZapisNalezu.KodRodu) AND (Rody.KodRodu = Druhy.KodRodu))"



or



..Filter = "PRM In (SELECT PRM FROM (SELECT ZapisNalezu.*, Rody.Rod AS
SortRod, Druhy.Druh AS SortDruh, Akcesity.Akcesit AS SortAkcesit,
Lide.PrijmeniAJmeno AS SortSberatele, Lide_1.PrijmeniAJmeno AS
SortDeterminatora FROM Lide AS Lide_1 RIGHT JOIN (Rody RIGHT JOIN (Druhy
RIGHT JOIN (Akcesity RIGHT JOIN (Lide RIGHT JOIN ZapisNalezu ON
Lide.KodCloveka = ZapisNalezu.KodSberatele) ON Akcesity.KodAkcesitu =
ZapisNalezu.KodAkcesitu) ON Druhy.KodDruhu = ZapisNalezu.KodDruhu) ON
Rody.KodRodu = ZapisNalezu.KodRodu) ON Lide_1.KodCloveka =
ZapisNalezu.KodDeterminatora ORDER BY ZapisNalezu.IDCislo) GROUP BY PRM
HAVING Count(*)>2)"



is more than I care to tackle. If I did manage to assemble something that
could handle all that, it would be a monumental pain in the rear to fill it
out every time, even if I dolled it up with dropdown lists and select
buttons. And can you imagine the user's reaction to such a monstrosity? No
thanks.



What I've done is create a large set of pre-defined filters (over 50 in
three dropdown menus off a custom menubar, plus several command buttons
scattered around the form next to pertinent fields) that either give a set
of all records of interest in the entire DB or take something from the
current record as a starting point and find all records based on something
from there, sort of a supercharged FBS. It's simple to program and simple
for the user to operate. Adding new filters is a snap, once I figure out
what exactly the user needs. I just build it in the QBE window, switch to
SQL view and scoop out the part I need for the new filter. Generally takes
no more than a few minutes. The two abominations above were built just that
way. They look grotesque, but the graphic interface made them simple to
create and they work perfectly.

Microsoft fixed several of the filter bugs in Access

2007. It would be interesting to trace whether they

fixed this one. My guess is that they did not, but I

don't have time to test it right now.

Neither I nor the users have Access 2007, and are not likely to anytime
soon, so it's a moot point for now. I do wonder what combination of
screw-ups made zero-length and non-first bound columns give the same error,
but it's not likely the Access boyz will show any of us their code. In any
case, I have a working solution now, so it's probably time to put this to
bed. Many thanks for your advice; I was really out in the ozone on this one.



Cheers,



Pete

 

[Allen Browne]

Excellent. Using the Filter event to trap and replace the problem filter
text sounds ideal.

Regards

 

[Peter Danes]

One last note, in case anyone else is following this. It turns out that my
filter event handler, simple as it is, is still unnecessarily complicated. I
suspect that Allen noticed it, but was too polite to say anything.



Since the comboboxes are cascaded and the related tables are linked by one
to many relationships with referential integrity enforced, the design gives
each species its own unique identifier, even when there are several species
with the same name, so it is unnecessary to specify the Genus identifier in
the filter event. That is, although the table contains things like:



Cornutispora Lichenicola

Thelocarpon Lichenicola



I still don't need to specify the Genus in this case. When using FBS, I want
only the one Genus-Species combination, let's say Cornutispora lichenicola,
that is currently displayed on the form. Since my filter intercept
substitutes the actual autonumber ID code for the filter, rather than the
text of the species, I can use simply:



Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

If ApplyType = acApplyFilter And ActiveControl.Name = "cboDruh" Then _

Filter = "KodDruhu=" & cboDruh.Column(1)

End Sub



This will filter for the actual code of the Lichenicola that is currently
displayed on the form, the one that belongs to the Cornutispora genus and
ignore the one that belongs to the Thelocarpon genus, since they have
discrete codes, even though the names are the same.



Pete