Split form Quick Filters

[Denise2020]

I found another thread about this here, but it unfortunately has not helped me solve this problem.

Problem With Datasheet Quick Filters When Form Record Source is SQL

In a datasheet form I am usually able to use the "quick filters" built into each column heading to filter specific values for multiple fields. My problem is when I replace the Record Source of the datasheet form with a SQL statement instead of a table or query name. Then I can set a quick filter...

www.access-programmers.co.uk

In the datasheet part of the form, some of the text fields show up as a checkbox list and others have a text filter option only. I am wondering why this is. What determines what sort of filter option is available for a field?

But more importantly, when I am using the quick filters, selecting one quick filter seems to change the options for the next quick filter to be text filter only (like picture 2).

For example, by Subtype and Country: both show up initially as checkbox lists to filter; however, if I select a Subtype filter, then the Country quick filter changes to look like picture 2 with a text filter option only. BUT if I FIRST quick filter by Country, using the checkbox options, then the other quick filters are STILL a checkbox option. So it works if I filter by Country first, but not the other way around. Country seems to be the only one that allows other quick filters like picture 1 (checkboxes).

What could cause this? It is frustrating my coworkers who aren't experienced in Access and don't use the text quick filters.

Apologies in advance if I haven't provided enough information, but the form source is a query that joins three tables, the main table, plus two parent tables which fill the Type and Country lists.

Thanks in advance!

 

[arnelgp]

on the Ribbon->File->Options->Current Database
on the Right pane, browse to the end.
when you come to "dont display list when more than....", you type as many 9 to the textbox.
close and re-open your db.

 

[Denise2020]

Thank you for your reply. I am a bit unsure what I am supposed to do. I found the "Don't display lists when more than...", which was set to 2,000.

But what do you mean by "type as many 9 to the textbox?" My database currently has just over 10,000 records, and the number climbs regularly, so I tried putting 20,000 in but it did not work. Am I misunderstanding? Thank you again.

 

[arnelgp]

try Typing:

999999999

 

[Denise2020]

Thank you, but it is still behaving the same way. If I quick filter one field, the other fields turn to text filters.

 

[arnelgp]

what is the sourcd of your form? table or query?

 

[Denise2020]

The source of the form is a query, however, I noticed something. Somehow the Control Source on the Country field was from the table, while the row source was still listed as from the query. I have no idea how that happened. But that is the one that "works". As a test, I changed the Control Source for AmType to the table from the query and it also works now. Do you know why would that be?

I have always tried to base forms on queries because it was explained to me that it was "better", but not really explained why, other than if you change field names that it automatically changes for queries, rather than having to go in and manually change them everywhere they are referenced as a table. I am not familiar enough with Access to know if there are other reasons or benefits to using queries over tables, unfortunately.

I am always thankful for your help and tutelage!