P
Peter Danes
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.
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.