Filtering query results

[jwreding]

On the query results datasheet view, you can obviously filter values using the built-in dropdown on the column header. When just running a generic query, the dropdown contains value options that can be checked (similar to the way it is in Excel). However, I've noticed that once you add criteria to the query, these values go away. The dropdown is still there, and you can still use generic filtering (Equals, Contains, etc.), but the value list is gone.

Having these value options is a critical component of convincing our employees to embrace Access datasheets and work within our application as opposed to using older, less flexible Excel spreadsheets with data connections to the database. Therefore, I'm very motivated to figure out a way around this.

Has anyone else noticed this issue? If so, is there any workaround for this? I would prefer to avoid making special forms and solving this programmatically, as I have come to really like all of the features and flexibility of datasheets (one being this built-in filter value list).

Thank you.

[June7]

I am not seeing this behavior. I filtered query and still see the value list.

[jwreding]

Ahh, I just isolated it while trying to figure out how to explain it to you.

It only happens when I use the value of a combo box on another form as the criteria. Could someone try that and see if they can recreate this, and a possible solution?

Thanks.

[June7]

Sorry, I cannot replicate this issue. Do you want to provide project for analysis?

[jwreding]

Thanks for offering, and sorry for taking forever to post. The attached file is an extremely simplified setup of what's going on. If you run qryRatesNoReference, you'll see that the dropdown filter works fine. Then, open frmSelector, choose a name, and run qryRatesWithReference. The filter values will be gone.

[June7]

Okay, but I can't look at it until after 15th (vacation). If you don't see another post from me next week, kick this up with a new post of your own as a reminder.

[June7]

I tested your project in both Access 2007 and 2010. 2007 exhibits the behavior you describe, 2010 does not.

[jwreding]

Thank you for checking this for me. So, do you think this means I'm up the creek, or any idea of a possible workaround? I have searched the web for others having this same issue, but I guess I'm not using the right language, because I can't find any other instances of it. Surely I am not the only person who has noticed this.

[June7]

I don't know why this behavior occurs and I don't know any solution.

I think most developers build projects that don't rely on the quickfilter. Usually a 'search form' is created for this purpose.

[jwreding]

Thank you for looking into this for me. The quick filter is a good tool for employees who are comfortable using Excel. I'm constantly trying to get all of our systems out of Excel and into the database. It's usually a tough sell because everyone is so used to Excel...this dropdown filter was a good way to say "hey look, the interface is just like what you're used to using in Excel." With a search form or the like, people get quickly frustrated and question the increase in productivity by converting to a database, since they don't know how much more efficient it is on the back-end.

I guess I could create a datasheet subform, then put selectors on the main form for the filter choices. However, it would get cumbersome, because people like to filter by multiple states. I'm not advanced enough to have multiple criteria (using OR logic) for the same field as a VBA-based filter. That's why the built-in dropdowns were so nice.

[June7]

Yes, coding for OR criteria would be very complicated. Your options appear to be: 1) eliminate the form parameter; 2) upgrade to Access2010.

[jwreding]

Hmm, I wonder if I just installed the 2010 version of Access Runtime on all of the end users' systems that it would correct the problem without the need for me to buy Access 2010 for development. It's worth a try, and I'll post results just in case anyone else happens to be reading this thread.

[jwreding]

Well, if anyone else is following this thread, I installed Access 2010 Runtime on an end user's system without making any changes to the application, and it worked!!