Access 2007 filters on forms change the properties

[M Skabialka]

Some of my database users like to filter items on forms so they can scroll
through a small number of records looking for something. However in Access
2007 if they then close the form without removing the filter it becomes part
of the properties for the form and next time it opens filtered. I tried
adding each of these:

DoCmd.RunCommand acCmdRemoveFilterSort
Me.FilterOn = False
DoCmd.ShowAllRecords

when the form closes but it doesn't remove the filter. In fact removing the
filter before closing the form also doesn't stop it from saving the filter
anyway.

Is there a setting I can change that removes filters when forms are closed?
It seems odd that a user can add a filter which changes a form's properties
and are not asked if they want to save the changes, but if I go into design
mode and remove that filter I have to approve the change when it is saved.
Or is this an Access glitch that I need a patch for?
Mich

 

[Danny J. Lesandrini]

I'm just guessing here, but there's a new property on tables in Access 2007
that might come into play. (I just took the Access Certification exam and it
was mentioned there.) It's found in the table properties, while in design mode
only. [Filter On Load]. Make sure it's set to No.

As it turns out, forms have this property too. Check the [Filter On Load]
property of the form too ... maybe even first.

 

[M Skabialka]

You are right - the Filter on Load on the form was on. This is an Access
2003 database, originally created in Access 2000, running in Access 2007.
Looking at other forms it seems as though the default when run in Access
2007 is "Filter on Load" I will have to change all of the forms.

Thanks for solving this mystery.
Mich

I'm just guessing here, but there's a new property on tables in Access
2007
that might come into play. (I just took the Access Certification exam and
it
was mentioned there.) It's found in the table properties, while in design
mode
only. [Filter On Load]. Make sure it's set to No.

As it turns out, forms have this property too. Check the [Filter On Load]
property of the form too ... maybe even first.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Some of my database users like to filter items on forms so they can
scroll through a small number of records looking for something. However
in Access 2007 if they then close the form without removing the filter it
becomes part of the properties for the form and next time it opens
filtered. I tried adding each of these:

DoCmd.RunCommand acCmdRemoveFilterSort
Me.FilterOn = False
DoCmd.ShowAllRecords

when the form closes but it doesn't remove the filter. In fact removing
the filter before closing the form also doesn't stop it from saving the
filter anyway.

Is there a setting I can change that removes filters when forms are
closed? It seems odd that a user can add a filter which changes a form's
properties and are not asked if they want to save the changes, but if I
go into design mode and remove that filter I have to approve the change
when it is saved.
Or is this an Access glitch that I need a patch for?
Mich

 

[David W. Fenton]

You are right - the Filter on Load on the form was on. This is an
Access 2003 database, originally created in Access 2000, running
in Access 2007. Looking at other forms it seems as though the
default when run in Access 2007 is "Filter on Load" I will have
to change all of the forms.

You also might want to eliminate the X button for closing the forms
and have your own CLOSE button that would run this code:

DoCmd.Close acForm, Me.Name, acSaveNo

This will discard any filters or sorts that the user has used in the
current form instance.

You might also want to have the form's OnLoad event clear the
filter/sort properties (and you might save the form then, too). This
will insure that there is no filter/sort in place when you open the
form.