Solved

Access 2010 Filter By Form Forces Default Values into filter

Posted on 2011-09-14
13
484 Views
Last Modified: 2013-11-29
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
Comment
Question by:Bill Ross
  • 7
  • 5
13 Comments
 
LVL 84
ID: 36541902
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
 
LVL 14

Author Comment

by:Bill Ross
ID: 36545898
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36552042
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
 
LVL 14

Author Comment

by:Bill Ross
ID: 36568923
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36570576
Open the form in design view
Open the property sheet
Click the "All tab"
Scroll to the "Filter" Property
0
 
LVL 14

Author Comment

by:Bill Ross
ID: 36570585
Hi,

I checked that and there is no filter applied.

Bill
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36580786
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
 
LVL 14

Author Comment

by:Bill Ross
ID: 36580887
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36582020
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
 
LVL 14

Author Comment

by:Bill Ross
ID: 36596448
Has anyone else had this issue?

Bill
0
 
LVL 14

Accepted Solution

by:
Bill Ross earned 0 total points
ID: 36965348
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 36976394
...just make sure to test this in all scenarios, ...in case there are unintended consequences...
0
 
LVL 14

Author Closing Comment

by:Bill Ross
ID: 37010499
This appears to be a bug in MS Access 2010 that is not present in prior vwesions.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now