how to remove filter created by synchronized combo on continuous f

F

fred75

Hello to all,
Used synchronized combo boxes under Access 97, 2000, 2003, to filter a form
in continuous mode.
After selecting items in one or more combos, using a command button, I
filter the form by calling a query. Used to work fine with previous Acces
versions, when changing the selection, the filtering was updated and new
results applied.

However, with Access 2007, it works fine the first time, then I keep on
getting the same filter (the filter is displayed in the Properties: filter
(in SQL form).

Tried to eleiminate the filter by: docmd ShowAllRecords before calling the
query,
but it does not work)
Question: how do I reset the filter ?

thanks for your help

fred - Paris - France
 
F

fred75

Thank you Maurice,
Tried all that, but no success !
When I click on the command button I use the following:

Me.FilterOn = False
Me.Filter = ""

DoCmd.ApplyFilter "rF_MembresSelect"

this query (used as a filter on the opened form) appears as a SQL syntax in
the Filter (Properties) of the form and remains there.
If I open the form in Design Mode and clear this "Filter", and revert back
to Normal Form, then it is OK (only once - the filter remains after I select
data in the combos and that's my problem, under the previous versions of
Access, I never had this problem - is this a "bug" with 2007 ?)

appreciate your help
fred
 
M

Maurice

Hi fred,

Tried to reproduce your problem and could do so only once. When I used the
me.filter=false setting i got rid of the sql statement. so give that a try.

So -> me.filter=false
and not -> me.filter =""
also make sure the option filter on load is set to no.

hth
 
F

fred75

Hello Maurice,
Thanks for your efforts - and I will have a go at your idea !

But oddly enough, while trying to find a way out, I ended with a
"satisfactory" solution:

quote:

Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click

DoCmd.Echo False

' Me.FilterOn = False 'is it useful ? So I bypassed it
Me.Filter = ""
SendKeys "{F5}" 'this does he trick !!!!!!!!!

DoCmd.ApplyFilter "rF_MembresSelect"
DoCmd.Echo True

Exit_FindRecord_Click:
Exit Sub

unquote

I tried it on another database, created with Access 2007 (giving identical
problems)
and it does work !

What do you think ?

Thanks again for your help and concern.
Regards

fred
 
F

fred75

Hi again,

Just tried your solution:

me.filter=false
and the option filter on load is effectively set to no.

(in fact I tried those before, even used me.FilterOn=False)
but does not work, looks like the SendKeys "{F5}" is, so far,
my best bet !!

Off to bed (it is 02:03 AM)
Thanks Maurice
 
M

Maurice

Glad to see you found a solution. I've noticed this behaviour before but i'm
not always to reproduce this. This makes it hard to define it as a bug.
Personally i'm not a big fan of the send keys option but if it does the trick
for you right now it takes care of the problem. I will dive into it however
because i'd really like to know what causes this and how we can bypass it in
the future.
Thanks for the feedback. (and yes we do need our sleep ;-)
 
F

fred75

Hello Maurice,

In browsing the manual "Access 2000" Developper's Handbook" written by Ken
Getz & al
there is in chapter 8 a full discussion about filtering by form.
It is obvious that normally, at least with Access 2000, it is easy to handle
the filter by form
and clear the filter, several actions are described.
But somehow, the filter should reset and respond to every new command !
Will have a go !

Tried on an old database (Access 2003) with 8 combos used for filtering
(worked very well !)

Opened with ACCESS 2007, filter by form works first time, then refuses to
update with combos changes.

In Filter Property, tried with both options: Filter On Load = True, then
False,
no success, the first filter remains (though if Filter On Load = False, then
the
SQL sequence is empty (when I check in Form Design Mode)).
if Filter On Load = True, then the SQL filter is there but in both cases,
the form is "stuck" on the first filter demand !

Tried several config setup !
=========================================================
test 1:

with Property set at Filter On Load = No

..FilterOn = True
..Filter = ""
DoCmd.ApplyFilter "myCombosFilter", ""

even after DoCmd.ShowAllRecords, no success !
(in fact this command does not eliminate the filter, just shows all the
records !)

SQL not there
=========================================================
test 2:

with Property set at Filter On Load = Yes

..FilterOn = True
..Filter = ""
DoCmd.ApplyFilter "myCombosFilter", ""

even after DoCmd.ShowAllRecords, no success !
(in fact this command does not eliminate the filter, just shows all the
records !)

SQL not there
=========================================================

test 3:

with Property set at Filter On Load = Yes

..FilterOn = False
..Filter = ""
DoCmd.ApplyFilter "myCombosFilter", ""

even after DoCmd.ShowAllRecords, no success !
(in fact this command does not eliminate the filter, just shows all the
records !)

SQL not there
=========================================================

test 4:

with Property set at Filter On Load = Yes

..FilterOn = False
..Filter = ""
DoCmd.ApplyFilter "myCombosFilter", ""

even after DoCmd.ShowAllRecords, no success !
(in fact this command does not eliminate the filter, just shows all the
records !)

SQL not there
=========================================================

test 5:

with Property set at Filter On Load = No

..FilterOn = False
..Filter = ""
SendKeys "{F5}"
DoCmd.ApplyFilter "myCombosFilter", ""

SQL not there
=========================================================
Conclusion:

adding the SendKeys "{F5}" command, the filter is OK

by setting .Filter = "" or removing this option, then the SQL is there or not
but that does not change anything in the fact that, somehow, ACCESS 2007
keeps,
somewhere, the filter memorized and refuses to reset it !!!!

========================================================

I will keep this procedure, (SendKeys), until anything better comes up !

Thank you, Maurice, for your friendly contribution

fred
 
M

Maurice

Wow, i'll take a deeper look at the book (got it on the shelf as well). And
I'll certainly will look into your samples. Talking about extensive testing
(applaud here...)

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top