Solved

Access 2010 Filter By Form Forces Default Values into filter

Posted on 2011-09-14
13
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 85
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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