• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

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
0
Bill Ross
Asked:
Bill Ross
  • 7
  • 5
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Bill RossAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Bill RossAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
Open the form in design view
Open the property sheet
Click the "All tab"
Scroll to the "Filter" Property
0
 
Bill RossAuthor Commented:
Hi,

I checked that and there is no filter applied.

Bill
0
 
Jeffrey CoachmanMIS LiasonCommented:
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

0
 
Bill RossAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Bill RossAuthor Commented:
Has anyone else had this issue?

Bill
0
 
Bill RossAuthor Commented:
Hi All,

FYI - after much testing I removed all defaults from AC2003 forms and set them at before update with a test for null.  This corrected the symptom but I think it's still a bug in AC 2010.

Bill
0
 
Jeffrey CoachmanMIS LiasonCommented:
...just make sure to test this in all scenarios, ...in case there are unintended consequences...
0
 
Bill RossAuthor Commented:
This appears to be a bug in MS Access 2010 that is not present in prior vwesions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now