Form filter combo box choices are Is Null/Is Not Null

[AccessMan]

I have an Access 2007 form bound to a table with <700 records. When I try
to filter by form and select a value for a key field, I am only given the
choices "Is Null" and "Is Not Null". Under Access Options/Current
Database/Filter Lookup Options, I have set the "Don't display lists ..."
threshold at 100,000 records. "Local Indexed" and "Local nonindexed" are
selected, "ODBC fields" is not selected.

How can I get the list of values to appear when I filter by form?

Thanks!

 

[Dirk Goldgar]

I have an Access 2007 form bound to a table with <700 records. When I try
to filter by form and select a value for a key field, I am only given the
choices "Is Null" and "Is Not Null". Under Access Options/Current
Database/Filter Lookup Options, I have set the "Don't display lists ..."
threshold at 100,000 records. "Local Indexed" and "Local nonindexed" are
selected, "ODBC fields" is not selected.

How can I get the list of values to appear when I filter by form?

What type of field are you trying to filter? I could be wrong, but I don't
think you can get a value list for memo fields.

 

[AccessMan]

I suspect you are right about Memo fields, but this is a Text 255 field.
More mysterious is the fact that I formerly was able to get the full listing
of selectable values, but now I can't. I haven't even modified the design of
the form lately.

 

[Dirk Goldgar]

I suspect you are right about Memo fields, but this is a Text 255 field.
More mysterious is the fact that I formerly was able to get the full
listing
of selectable values, but now I can't. I haven't even modified the design
of
the form lately.

I'm puzzled, so try some of these steps to investigate:

1. Open the form's recordsoruce table directly from the Nav Pane. Put that
in Filter by Form mode, and see if you get a list on this field.

2. If you don't, copy and paste the table structure only (no records) to a
new table. Add a couple of records to that table, then go to Filter by Form
and see if you get a list on the field in question.

 

[AccessMan]

The recordsource table did not yield the full list - it behaved the same as
the form. I don't think it matters, but it turns out that I lied about the
field being part of the ley; it is not.

The structure only copy behaved differently! The full list of field values
appeared in the filter by form! Do you know why this is the case?

Because this table is very high in my "food chain", I don't think that I can
simply delete it and rename the copy.

 

[Dirk Goldgar]

The recordsource table did not yield the full list - it behaved the same
as
the form. I don't think it matters, but it turns out that I lied about
the
field being part of the ley; it is not.

The structure only copy behaved differently! The full list of field
values
appeared in the filter by form! Do you know why this is the case?
No.

Because this table is very high in my "food chain", I don't think that I
can
simply delete it and rename the copy.

What happens if you run an append query to copy *all* the records from the
original table to the copy? Does the field list appear for the copied table
appear (in Filter by Form) then, with the full complement of records?

 

[AccessMan]

I deleted the sample records from the copy, then appended all of the records
from the original table to the copy. Filter by form works as I would like on
the table copy itself and on the form when I point it to the table copy.
When I repoint the form back to the original table, the Is Null/Is Not Null
situation returns.

I neglected to mention that I'm dealing with a split database. The table
copy was in the back-end with the original table when filter by form worked
the way I want. However, when I import the original table into the front-end
where the form resides under a different name, and point the form to it, the
filter by form works properly! If I repoint the form to the original table
in the back-end, the probelm returns.

Now the question becomes: How can I get the table copy to replace the
original table and maintain all of the internal links. Restablishing
relationships is not a problem, but I think there are a lot behind-the scenes
links and I worry about these.

 

[Dirk Goldgar]

I deleted the sample records from the copy, then appended all of the
records
from the original table to the copy. Filter by form works as I would like
on
the table copy itself and on the form when I point it to the table copy.
When I repoint the form back to the original table, the Is Null/Is Not
Null
situation returns.

I neglected to mention that I'm dealing with a split database. The table
copy was in the back-end with the original table when filter by form
worked
the way I want. However, when I import the original table into the
front-end
where the form resides under a different name, and point the form to it,
the
filter by form works properly! If I repoint the form to the original
table
in the back-end, the probelm returns.

So you get the same behavior whether you're working with a linked table or a
local table? It sure sounds like it's something about the table, but I
don't know what it might be. Have you done a Compact & Repair on the
back-end database since this problem manifested itself?

Now the question becomes: How can I get the table copy to replace the
original table and maintain all of the internal links. Restablishing
relationships is not a problem, but I think there are a lot behind-the
scenes
links and I worry about these.

I'm not sure what you mean by "internal links", if you aren't talking about
relationships. I do have code to backup and restore relationships, though I
haven't verified that it works in Access 2007.

 

[AccessMan]

So you get the same behavior whether you're working with a linked table or a
local table? It sure sounds like it's something about the table, but I
don't know what it might be. Have you done a Compact & Repair on the
back-end database since this problem manifested itself?

Yes, I did it to both the front-end and back-end right after my last posting
- no change.

I'm not sure what you mean by "internal links", if you aren't talking about
relationships. I do have code to backup and restore relationships, though I
haven't verified that it works in Access 2007.

I'm sure I have foreign key fields in several other tables that specify this
table for row source. Also, there are many queries that include this table.
I need to determine if, after deleting this table and renaming the copy, the
row source references and query references are intact. I can do this in my
non-production copy of the database.

 

[Dirk Goldgar]

Yes, I did it to both the front-end and back-end right after my last
posting
- no change.

I'm currently at a loss to explain the behavior.

I'm sure I have foreign key fields in several other tables that specify
this
table for row source. Also, there are many queries that include this
table.
I need to determine if, after deleting this table and renaming the copy,
the
row source references and query references are intact. I can do this in
my
non-production copy of the database.

You will need to rebuild the relationships themselves, but your queries and
rowsources should be okay. If not, you could try it again after turning off
Name AutoCorrect.

 

[AccessMan]

Replacing the table with the copy did not work, with Name AutoCorrect on and
off.

I tried something else with revealing results - importing the form into the
back-end. The form filter works fine after doing this, so the problem has
something to do with the split database.

 

[Dirk Goldgar]

Replacing the table with the copy did not work, with Name AutoCorrect on
and
off.

I tried something else with revealing results - importing the form into
the
back-end. The form filter works fine after doing this, so the problem has
something to do with the split database.

I've done some testing, and it appears that linked tables work differently
than local (non-linked) tables, when it comes to the filter lists. For
local tables, depending on what you have selected in the Access options, you
get filter lists for both indexed fields and non-indexed fields. For linked
tables, though, you only get filter lists for indexed fields. I don't see
this mentioned in the help file (though I may have overlooked it), but it
appears to be the case.

I'm guessing that, when you used to get filter lists for this form and
table, the database wasn't split, but now that it is split, you don't. Is
that correct?

If you need to have the drop-down filter lists for fields on this form, what
I believe you must do is go to the table in the back-end database, go into
design view, and create indexes for those fields. Then relink the table in
your front-end. Note, though, that there are still field types that can't
be displayed in filter lists: memo, hyperlink, Yes/No, and OLE object
fields.

 

[AccessMan]

You truly are wise! Yes, this worked - thanks!!! I doubt I would have
identified that as the problem. I thank you for sticking it out with me!

 

[Dirk Goldgar]

I thank you for sticking it out with me!

You're welcome. You had me scratching my head there for a while, and I
learned something in the process, so I thank you, too.