Access 2007 Open forms with FilterOn and form fails to open

[merwcat]

Running a large 2003 database in compatibility mode. I have always used in
VBA FilterOn =False at the open form event to remove previously applied
filters. Also some forms I use DoCmd.ApplyFilter to open active records only.
Worked OK for years but now in 2007 I get a message telling me that forms
cannot be found or if a query is the filter it cannot be found. Forms open
perfectly if I remove VBA code filters from the Open or Load events.
If I use a Macro ApplyFilter to open the forms they open with a filter no
problem.
Building a replacement Database in 2007 so I am hoping the issue only
affects the compatibilty mode. Any ideas? I have a feeling using the On
Error resume next statement with a filter in the load event may just work! I
notice you get an error message but the filters operate in lthe load event.

 

[Allen Browne]

Does it make any difference if you set the FilterOnLoad property to No?

There should then be no need to set the form's FilterOn property to False in
Form_Load or Form_Open, as that is the default behavior in previous versions
(unless the form is explicitly opened with a WhereCondition.)

 

[merwcat]

Hi Allen thanks for the reply but slightly confused by the terminology if you
mean set the Allow Filters property to No then yes I believe the forms will
open, I will try it at work tonight. Unfortunately I have to have the allow
filters property set to yes because once the information is in the database I
use the ability to open one form and then have that form open all connected
incidents and other data to the main record using filters to open the other
forms correctly.
I also make extensive use of the access forms ability to filter data in the
main forms and this is one of the main uses of the database.
If I set Allow Filters to No I lose that ability. I have never tried to set
the Allow Filters property on the fly in code maybe I should give it a try.

While I have your attention I would like to say thanks for your website, the
ideas I have pulled from your tips and code have been a great source of help
over the past few years.

 

[merwcat]

Addendum to previous - Allen had a look at why I used the FilterOn = False
command at Open. A major use of my forms is people use them to filter, find
and identify. The users are not particularly computer savvy so I make things
as simple as possible to use. When a user opens a form to filter the default
action of the forms is to open but retain any previous filter in the
background. If the user wants a new record he has to remember to select clear
grid to dispose of the previous filter otherwise they fail to notice a check
box has a previous filter in it. I have always used the Apply Filter
FilterOn = false because I noticed it deleted any filters applied by a
previous user and enabled them to just put in the info they require. I used
it in conjunction with Isloaded so that in situations where a filter was
needed at open it opened correctly. Rgds

 

[Allen Browne]

FilterOnLoad is a new property in A2007. You should be able to find it there
if you are using the new version.

My experience with previous versions is that, although they may retain the
last-used filter, it is not automatically applied when the form opens. This
behavior is different in A2007, and depends on the setting of the new
FilterOnLoad property.

If you open a form with a WhereCondition, this becomes the form's filter,
and you would not want to clear that in Form_Load. Example:
DoCmd.OpenForm "Form1", WhereCondition:="Field1=99"

Hope I have understood what you intend.

 

[merwcat]

Hi Allen thanks for your help but I have full 2003 access security on, hidden
objects, disabled design menus and mde files for users and find I cant make
design changes in 2007 so I am using 2003 to fix the problems on and then
loading the mde on to a 2007 machine. I will be trying out your suggestions
on my new 2007 database but for now I think I may have found the answer to my
problem. The method I was using for removing previous filters is not the one
recommended by MS for 2003. The 2003 development site recommends applying a
zero length string in the filter event I have just tried it and found it
will work as it does not affect any of my open or load events and I can still
open one form with a filter from another. Shame to move on from Access 2003
but like a lot of company Access people if the IT department want to keep
with the latest software the user has to go with them even if i suggested
otherwise.

Rgds and thanks for your help will post again if problem reappears