Solved

Access 2010 Filter By Form Forces Default Values into filter

Posted on 2011-09-14
13
485 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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