Link to home
Start Free TrialLog in
Avatar of Bill Ross
Bill RossFlag for United States of America

asked on

Access 2010 Filter By Form Forces Default Values into filter

I have an access 2003 form that works fine in 2003/2007.  When I use filter by form in 2010 the default values from the Combo Box populate the filter fields and I can't get rid of them.  For example I have a combo box cbState with "CO" as the default.  If I use filter by form to filter on last name of "Smith" it forces the state = "CO" so all I get are "Smith" in "CO" - not all "Smith".  This form works fine in 2003.

Any ideas?

Bill
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

So you're using FBF directly in the User interface - that is, you are right-clicking, setting the values, etc etc?

If so, do you have a sample database that exhibits this behavior? I can't reproduce it, but then I might not be working with it correctly. If you do provide a db, make sure you provide explicit instructions to recreate the behavior.

Also - be sure that you've installed all needed updates/patches to your Office/Access installs, as well as Windows.
Avatar of Bill Ross

ASKER

Open the atached DB in AC2010
Open frmNameTest
Right click LastName field and apply a TextFilter on "Smith"
Note 13 records
Now try to get 13 records using Sort & Filter, Advanced, Filter By Form - no can do.

Try that in  AC2003/2007 - can do.

Ideas



FFExample.zip
This appears to be because you have a filter set in the form's properties.
When I removed this filter, it worked fine in 2010.

As for the *Why*, ...I can't say.

JeffCoachman
Hi Jeff,

Where is the filter you are referring to?  I cannot get the correct number of records no matter what I do in Access 2010.  All is fine in 2007 and earlier...

Thanks,

Bill
Open the form in design view
Open the property sheet
Click the "All tab"
Scroll to the "Filter" Property
Hi,

I checked that and there is no filter applied.

Bill
Yes, you are correct...

Does seem odd.

Not sure of the fix though...

I will admit that I don't really use Filter by form in my apps.
Part of the reason it that I can't control things like this.
If I create my own filtering system, it can be just as it needs to be.

As I am sure you are aware, many developers go hog-wild with custom filters when Filter by form is already available.
So the theory here is to use the filtering built into access before going crazy with anything custom.

For the most part, this is true.
However, I find that the built in filtering options in Access can be a bit confusing and a overwhelming for my average user, so I build my own.

Perhaps there is a service pack coming that will address this?


Let's see if other Experts chime in...


Jeff

Hi Jeff,

I'm certainly willing to include a custom filter - just haven't needed one.   Do you have a sample that i could look at?

Also, a work around is just to populate the values in the before insert action in VBA.

Thanks,

Bill
A custom filter can be anything from a one field filter to something way more complex (like multiple fields, multiple wild cards, AND/OR Logic, ...etc)

Filter by form will allow you to filter by *every* field on the form.

For an end user, this is rarely needed.
Typically only one or two fields need ever be filtered.

The other issue here is filtering the form "before" or "while" it is opened.
I prefer filtering the form before it is opened because it avoids the confusion of the user not knowing what "Mode" (Edit, Filter, Read, Add, Delete) the form is in.

So you will have to be specific as to what your need is.

Jeff
Has anyone else had this issue?

Bill
ASKER CERTIFIED SOLUTION
Avatar of Bill Ross
Bill Ross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This appears to be a bug in MS Access 2010 that is not present in prior vwesions.