Filter by Form behaving oddly?

[Jason M Owens]

In a few databases at work, when I try & filter by form it doesn't behave as
it should (or at least as I understand it should).

When viewing a table in datasheet view I select the Filter by Form button &
the table becomes just one empty row, as expected, with drop-down arrows in
each field (as expected), for the user to select a value(s) on which to
filter.

However, when the drop-downs are clicked, instead of a list of the unique
values within that field, there are just 2 options: Is Null & Is Not Null?

Can anyone advise what is going on?

Cheers,

Jay

 

[Guest]

Possibly some type of subtle corruption? Does this happen with all tables in
the database, just one table, or a few tables? What happens if you create a
new table and add some test data? Does it behave as expected?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[vikenk]

When viewing a table in datasheet view I select the Filter by Form button &
the table becomes just one empty row, as expected, with drop-down arrows in
each field (as expected), for the user to select a value(s) on which to
filter.

However, when the drop-downs are clicked, instead of a list of the unique
values within that field, there are just 2 options: Is Null & Is Not Null?

This also happens to me when using a form. When in the Main Form, I
select "Filter by Form" and a blank form shows up, as expected. I also
get the drop-down arrows, as expected, and I get two values - Null or
Not Null. I didn't realize that the data was supposed to be in the
drop-down box.

When I manually input the data, it works.

 

[Guest]

Hi Viken,

Your post prompted me to experiment a bit more with filter by form. I don't
tend to use it myself. It is disabled in databases that I distribute to
users, because I think it's just too confusing for the average user. A few
minutes of frustration using the built-in Filter by Form confirms to me why I
invested the time a few years ago to learn the more powerful Query by Form
(QBF) technique, where one builds the WHERE portion of a SQL statement
on-the-fly, using VBA code. I can send you a sample database,if you are
interested in seeing a better mouse trap. If you are interested, send me a
private e-mail message with a valid reply-to address.

My e-mail address is available at the bottom of the contributor's page
indicated below. Please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.

I found some interesting results using the Orders and Order Details tables
in the sample Northwind database. First, lookup fields can be problematic.
This is confirmed in the following KB article (I added the parenthesis around
the title of this article, because it likely applies to Access 2002 and 2003
equally well):

(ACC2000 Filter By Form Options Do Not Apply to Lookup Fields
http://support.microsoft.com/?id=208573

Try applying a filter to the Employee field in the Orders table. This is a
nasty lookup field. You will receive an error message. This KB article
includes the following paragraph in the section titled More Information:

<Begin Quote>
"If the form's recordset contains more records than the number specified on
the Edit/Find tab of the Options command, then Microsoft Access does not fill
the combo box or list box with unique values from the form's recordset.
Rather, it fills the list with two values: Is Null and Is Not Null. The only
exception is when a field is a Lookup field (that is, when the field has its
DisplayControl property set to combo box or list box in an underlying table).
Then, Microsoft Access uses the table's RowSource property setting to fill
the value list for a Filter By Form combo box or list box, instead of reading
records from the form's recordset."
<End Quote>


In the Order Details table, I do indeed get Is Null and Is Not Null as the
only choices for the (5) fields. This table has over 2000 records in my copy
of Northwind. The setting that I had, under Tools > Options | Edit/Find tab
for the "Don't display list where more than this number of records read:"
setting was set to 1000. So, the result is consistent with the KB article.

I added a new text data type field to the Order Details table. I entered
"Joe", "Fred", "Barney", and "Tom" into the first four records. Select Filter
by Form on this field, and you get Is Null and Is Not Null as your only
choices. Now, go back into table design view, and set an Index (Duplicates
OK) on this field. Repeat the filter by form test, and you will likely see
that you get a filtered list that includes five records, one that looks blank
(represents most of the records, where you did not enter anything) and four
records with the unique names that you entered. However, now go back into
table design view, and change that Index to No Duplicates. Apply a Filter by
Form. The resulting combo dropdown appears to include one blank row for each
record in the table that is null. Not terribly useful.

By the way, if that was your *real* e-mail address that you included, you
should re-configure your newsreader so that it includes a munged form of your
e-mail address. Otherwise, you are extending a welcome mat to all the
spammers who harvest e-mail addresses from newsgroup posts. I removed your
e-mail address in this reply.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jay]

Thanks for that Tom...It was really puzzling me why it worked it some
databases and not in others - I only tend to use it now & again to save
building a query if it's just a one-off & the criteria are simplistic enough
to allow it.

Regards

Jay

 

[Guest]

Hi Jay,

You're welcome. Attempting to answer this question was a learning experience
for me, too.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jay]

Hi Tom,

I've just had chance to study your post in more detail & appreciate the
time you must have put into it. I've bookmarked the KB article for a read
later, but haven't had much need of lookup fields yet, so will save it for
if & when I do

My tables affected just needed the Indexed property setting to Yes
(Duplicates OK) to make the filter by form work - just as you said What
I can't understand is this:- Microsoft tout the Filter by Form (taking just
table datasheet view as an example) as a standard tool - all the Microsoft
Press books I've read/owned teach this as a basic technique...but *never*
mention that the fields(s) must be indexed. And as the Yes (Dupl' OK) isn't
the default property in a new table designed from scratch I expect a few
people must have tried filtering by form & been confused why it didn't work.
(Although I find it interesting that Indexes *are* set automatically by the
Table wizard)

So that's why the tables I was dealing with at work had the null/not null
filter_by_form problem - they had all been set up from scratch and therefore
hadn't had the fields indexed by the table wizard. I think in future I'll
try get into the habit of indexing all fields in new tables (and let my
colleagues know also).

Many thanks for what has turned out to be a very informative process

Jay
___