Access 2007 filtering issue

[Mark]

Hi all,

I develop using Access 97, convert to Access 2000 on an Office XP machine
and distribute the frontend.

I ran into a problem awhile back on Access 2007 machines when they first
started showing up in our office where forms opened to the last filter
applied during development. Somehow, I fixed that but cannot remember what I
did! I don't have any code involving the filter property in the Open or Load
events.

When I distribute the new frontend to these "older" computers -- one running
Access 2007 on a Windows XP machine, and the other running Access 2007 under
Vista, the forms still open as desired showing all records based on the
underlying query for the form. In Design view, you can still see the last
filter applied (Filter ...), but the form actually opens showing all
records, which is what I want.

We just got a new machine with Access 2007 running under Windows XP. Darn if
the filtering problem isn't back on this new machine. I'm distributing the
same frontend as with the two other Access 2007 computers. Those other two
open forms showing all records, this new one opens filtered to whatever was
left in the "Filter ..." property during development.

Anyone know what the difference could be between these "older" Access 2007
computers and this new one? Again, they're all running the same frontend.

Do I need to add "DoCmd.ShowAllRecords" in the Open event on all my forms,
or is there some "option" under Access 2007 that I've forgotten to set?

Thanks for any insight or suggestions,
Mark

 

[Allen Browne]

Forms and reports in A2007 have new properties named:
Filter on Load
Order By on Load

Open each form/report in design view, and set both properties to No. Save.
Close.

 

[FPS, Romney]

Thanks, Allen
.... but wouldn't I have to redo this each time that I issue a revised
frontend since development occurs using Access 97?
.... and why on this new computer with Access 2007 and not on the other two
computers which also run Access 2007? They each receive the same frontend.
Mark

 

[Allen Browne]

You won't be able to use the A97 interface to set these properties, since
you won't find them in the Properties box in that version.

If you create an MDE in A97, you won't be able to open it in design view and
change anything in A2007.

 

[FPS, Romney]

Allen,
Yes, thanks, I know that's true. My questions remain:
1. I don't understand why this hasn't been a common issue when earlier
versions of frontends (e.g., Access 2000) are distributed to Access 2007
machines. (In other words, why is this only happening to me!?)
2. I don't understand why it's occuring on one Access 2007 machine and not
on the other two.

.... I guess if there's no obvious answers to those questions, the easiest
thing would be to place DoCmd.ShowAllRecords in the Open event of each form.
I can do this in Access 97 and it should carry over fine to the Access 2007
machines. Would you agree?

Thanks so much for your time.
Mark

 

[FPS, Romney]

naw ... I remember why I didn't use DoCmd.ShowAllRecords:
Some of the forms can be opened not only on the main switchboard, but also
off of other forms which have navigated to a particular record.
For example, you can open Treatment Plans off the switchboard and then
navigate to the client you want. However, if you're on the form for Services
Provided and have navigated to a particular client, you can click a button
for Treatment Plan and it will open the Treatment Plans form, but be
filtered for the particular client on the Services Provided form.
.... hope this makes sense.
Anyway, this doesn't work if the Treatment Plans form has
DoCmd.ShowAllRecords in the Open event.

 

[FPS, Romney]

Allen,
Ok, I was incorrect. The earlier Access 2007 machines *do* still have the
problem, but only on 1 out of 11 forms. The difference being that on that
particular form the "Filter On Load" property is set to "Yes". The other 10
forms have it set to "No".

All I've done is distribute the converted frontend (converted from '97 to
2000) to the 2007 machines. I can see no difference on any of the 11 forms
in the '97 version in terms of properties which have to do with filtering. I
certainly haven't gone to the 2007 versions, opened the database, and made
any changes to the "Filter On Load" properties on the different forms after
distributing the new frontend. Even if I would have done this long ago, I
would think that replacing the frontend (which occurs rather frequently)
would have overwritten any changes made during prior versions of the
frontend.

Is there something in the '97 version that I'm missing which would result in
changes to the "Filter On Load" property setting in Access 2007?

Is there something in 2007 that the casual user can change (i.e., other than
going into Design view) which could cause this setting to change from "Yes"
to "No" and vice versa?

Mark

 

[Allen Browne]

Perhaps you could set the property in Form_Open, if the version >= 12.

 

[FPS, Romney]

Allen,
I apologize for taking up so much of your time on this issue -- it pales in
comparison to the other issues you help with in this newsgroup. But, yes, I
could check the version number, if that's what your suggesting, but I would
also have to check where the form is being loaded from: Is it from the
switchboard, in which case I want to set "Filter On Load" to "No", or is the
form being opened from another form (filtered to a particular client) and I
want "Filter On Load" set to "Yes". [or, I could use 2 different forms --
one which opens from the switchboard, and one which opens from some other
form].

I may try going through these 11 forms on my Access 97 machine just before
converting to 2000 and deleting the "Filter ..." entry. It seems to be this
last filter value in the '97 version which gets passed along to the
converted frontend and then on to the Access 2007 machines.

Thanks,
Mark

 

[Allen Browne]

My suggestion would be to have the form open without a filter.

If you want to open it with a filter, you can then pass an argument in its
WhereCondition.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I apologize for taking up so much of your time on this issue -- it pales
in
comparison to the other issues you help with in this newsgroup. But, yes,
I
could check the version number, if that's what your suggesting, but I
would
also have to check where the form is being loaded from: Is it from the
switchboard, in which case I want to set "Filter On Load" to "No", or is
the
form being opened from another form (filtered to a particular client) and
I
want "Filter On Load" set to "Yes". [or, I could use 2 different forms --
one which opens from the switchboard, and one which opens from some other
form].

I may try going through these 11 forms on my Access 97 machine just before
converting to 2000 and deleting the "Filter ..." entry. It seems to be
this
last filter value in the '97 version which gets passed along to the
converted frontend and then on to the Access 2007 machines.

Thanks,
Mark

Perhaps you could set the property in Form_Open, if the version >= 12.

 

[Mark]

Thanks, Allen

But it seems that DoCmd.ShowAllRecords would need to be in the form's Open
event to override the "File ... " entry leftover from the 97 development
version. The problem with using DoCmd.ShowAllRecords in the Open event of
the form is that it negates any attempt to open the form to a specific
record via a command button on some other form ... it negates the
WhereCondition specified in the DoCmd.OpenForm procedure of that command
button.

Your previous suggestion of code which applies certain steps depending upon
the version of the frontend does seem to be one way to go, although I think
I would still need to determine whether the form was opened via the
switchboard (where I want it opened unfiltered), or from some other form
(where I want it filtered via a command button and a WhereCondition).

I've only got 11 forms that are problematic, however, and the simplest
solution may be just to remove the "Filter ... " entry in the '97 version
before converting and distributing. If I forget, then the 2007 user just has
to remember to toggle the "Filtered" button, or simply re-filter to the
client they want -- which is probably what they'll want to do anyway.

This wasn't a problem with 2000/2002-2003, and it's not a terrible problem
for 2007 users. Just one of those unexpected changes that can be a little
annoying.

Thanks again for your help.
Mark

My suggestion would be to have the form open without a filter.

If you want to open it with a filter, you can then pass an argument in its
WhereCondition.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I apologize for taking up so much of your time on this issue -- it pales
in
comparison to the other issues you help with in this newsgroup. But, yes,
I
could check the version number, if that's what your suggesting, but I
would
also have to check where the form is being loaded from: Is it from the
switchboard, in which case I want to set "Filter On Load" to "No", or is
the
form being opened from another form (filtered to a particular client) and
I
want "Filter On Load" set to "Yes". [or, I could use 2 different forms --
one which opens from the switchboard, and one which opens from some other
form].

I may try going through these 11 forms on my Access 97 machine just before
converting to 2000 and deleting the "Filter ..." entry. It seems to be
this
last filter value in the '97 version which gets passed along to the
converted frontend and then on to the Access 2007 machines.

Thanks,
Mark

Perhaps you could set the property in Form_Open, if the version >= 12.

Allen,
Ok, I was incorrect. The earlier Access 2007 machines *do* still have the
problem, but only on 1 out of 11 forms. The difference being that on that
particular form the "Filter On Load" property is set to "Yes". The
other
10
forms have it set to "No".

All I've done is distribute the converted frontend (converted from

'97
to

2000) to the 2007 machines. I can see no difference on any of the 11 forms
in the '97 version in terms of properties which have to do with filtering.
I
certainly haven't gone to the 2007 versions, opened the database, and made
any changes to the "Filter On Load" properties on the different forms
after
distributing the new frontend. Even if I would have done this long

ago,
I

would think that replacing the frontend (which occurs rather
frequently)
would have overwritten any changes made during prior versions of the
frontend.

Is there something in the '97 version that I'm missing which would result
in
changes to the "Filter On Load" property setting in Access 2007?

Is there something in 2007 that the casual user can change (i.e., other
than
going into Design view) which could cause this setting to change from
"Yes"
to "No" and vice versa?

Mark



You won't be able to use the A97 interface to set these properties, since
you won't find them in the Properties box in that version.

If you create an MDE in A97, you won't be able to open it in design view
and
change anything in A2007.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
Thanks, Allen
... but wouldn't I have to redo this each time that I issue a
revised
frontend since development occurs using Access 97?
... and why on this new computer with Access 2007 and not on the other
two
computers which also run Access 2007? They each receive the same
frontend.
Mark

Forms and reports in A2007 have new properties named:
Filter on Load
Order By on Load

Open each form/report in design view, and set both properties to
No.
Save.
Close.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

I develop using Access 97, convert to Access 2000 on an Office XP
machine
and distribute the frontend.

I ran into a problem awhile back on Access 2007 machines when
they
first
started showing up in our office where forms opened to the last
filter
applied during development. Somehow, I fixed that but cannot
remember
what
I
did! I don't have any code involving the filter property in the Open
or
Load
events.

When I distribute the new frontend to these "older"

computers --
one

running
Access 2007 on a Windows XP machine, and the other running Access
2007
under
Vista, the forms still open as desired showing all records

based
on

the
underlying query for the form. In Design view, you can still

see
the

last
filter applied (Filter ...), but the form actually opens

showing
all

records, which is what I want.

We just got a new machine with Access 2007 running under

Windows
XP.

Darn
if
the filtering problem isn't back on this new machine. I'm
distributing
the
same frontend as with the two other Access 2007 computers. Those
other
two
open forms showing all records, this new one opens filtered to
whatever
was
left in the "Filter ..." property during development.

Anyone know what the difference could be between these "older"
Access
2007
computers and this new one? Again, they're all running the same
frontend.

Do I need to add "DoCmd.ShowAllRecords" in the Open event on

all
my

forms,
or is there some "option" under Access 2007 that I've forgotten
to
set?

Thanks for any insight or suggestions,
Mark

 

[Robin]

Mark, you may have already thought of this or tried it, but for the forms you
want to open to a specific record if it's opened from a certain form, you
could use the IsOpen module/function and on load event procedure. I use that
quite often when I have a form that opens different depending on where it's
opening from.

I have not upgraded to '07 due to many things we see on this site, but it
sounds like this should work if '97 and '07.

Just a suggestion.
--
Thanks - Robin

Thanks, Allen

But it seems that DoCmd.ShowAllRecords would need to be in the form's Open
event to override the "File ... " entry leftover from the 97 development
version. The problem with using DoCmd.ShowAllRecords in the Open event of
the form is that it negates any attempt to open the form to a specific
record via a command button on some other form ... it negates the
WhereCondition specified in the DoCmd.OpenForm procedure of that command
button.

Your previous suggestion of code which applies certain steps depending upon
the version of the frontend does seem to be one way to go, although I think
I would still need to determine whether the form was opened via the
switchboard (where I want it opened unfiltered), or from some other form
(where I want it filtered via a command button and a WhereCondition).

I've only got 11 forms that are problematic, however, and the simplest
solution may be just to remove the "Filter ... " entry in the '97 version
before converting and distributing. If I forget, then the 2007 user just has
to remember to toggle the "Filtered" button, or simply re-filter to the
client they want -- which is probably what they'll want to do anyway.

This wasn't a problem with 2000/2002-2003, and it's not a terrible problem
for 2007 users. Just one of those unexpected changes that can be a little
annoying.

Thanks again for your help.
Mark

My suggestion would be to have the form open without a filter.

If you want to open it with a filter, you can then pass an argument in its
WhereCondition.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I apologize for taking up so much of your time on this issue -- it pales
in
comparison to the other issues you help with in this newsgroup. But, yes,
I
could check the version number, if that's what your suggesting, but I
would
also have to check where the form is being loaded from: Is it from the
switchboard, in which case I want to set "Filter On Load" to "No", or is
the
form being opened from another form (filtered to a particular client) and
I
want "Filter On Load" set to "Yes". [or, I could use 2 different forms --
one which opens from the switchboard, and one which opens from some other
form].

I may try going through these 11 forms on my Access 97 machine just before
converting to 2000 and deleting the "Filter ..." entry. It seems to be
this
last filter value in the '97 version which gets passed along to the
converted frontend and then on to the Access 2007 machines.

Thanks,
Mark


Perhaps you could set the property in Form_Open, if the version >= 12.

Allen,
Ok, I was incorrect. The earlier Access 2007 machines *do* still have
the
problem, but only on 1 out of 11 forms. The difference being that on
that
particular form the "Filter On Load" property is set to "Yes". The
other
10
forms have it set to "No".

All I've done is distribute the converted frontend (converted from '97
to
2000) to the 2007 machines. I can see no difference on any of the 11
forms
in the '97 version in terms of properties which have to do with
filtering.
I
certainly haven't gone to the 2007 versions, opened the database, and
made
any changes to the "Filter On Load" properties on the different forms
after
distributing the new frontend. Even if I would have done this long ago,
I
would think that replacing the frontend (which occurs rather
frequently)
would have overwritten any changes made during prior versions of the
frontend.

Is there something in the '97 version that I'm missing which would
result
in
changes to the "Filter On Load" property setting in Access 2007?

Is there something in 2007 that the casual user can change (i.e., other
than
going into Design view) which could cause this setting to change from
"Yes"
to "No" and vice versa?

Mark



You won't be able to use the A97 interface to set these properties,
since
you won't find them in the Properties box in that version.

If you create an MDE in A97, you won't be able to open it in design
view
and
change anything in A2007.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
Thanks, Allen
... but wouldn't I have to redo this each time that I issue a
revised
frontend since development occurs using Access 97?
... and why on this new computer with Access 2007 and not on the
other
two
computers which also run Access 2007? They each receive the same
frontend.
Mark

Forms and reports in A2007 have new properties named:
Filter on Load
Order By on Load

Open each form/report in design view, and set both properties to
No.
Save.
Close.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

I develop using Access 97, convert to Access 2000 on an Office XP
machine
and distribute the frontend.

I ran into a problem awhile back on Access 2007 machines when
they
first
started showing up in our office where forms opened to the last
filter
applied during development. Somehow, I fixed that but cannot
remember
what
I
did! I don't have any code involving the filter property in the
Open
or
Load
events.

When I distribute the new frontend to these "older" computers --
one
running
Access 2007 on a Windows XP machine, and the other running Access
2007
under
Vista, the forms still open as desired showing all records based
on
the
underlying query for the form. In Design view, you can still see
the
last
filter applied (Filter ...), but the form actually opens showing
all
records, which is what I want.

We just got a new machine with Access 2007 running under Windows
XP.
Darn
if
the filtering problem isn't back on this new machine. I'm
distributing
the
same frontend as with the two other Access 2007 computers. Those
other
two
open forms showing all records, this new one opens filtered to
whatever
was
left in the "Filter ..." property during development.

Anyone know what the difference could be between these "older"
Access
2007
computers and this new one? Again, they're all running the same
frontend.

Do I need to add "DoCmd.ShowAllRecords" in the Open event on all
my
forms,
or is there some "option" under Access 2007 that I've forgotten
to
set?

Thanks for any insight or suggestions,
Mark

 

[FPS, Romney]

Thanks, Robin
Yes, that would work for that part of the problem.
Sooner or later, however, it seems that the forms in question would need to
be opened on an Access 2007 computer in Design View and changes made to the
new filter options in that version.

My solution at this point is to simply remember to click a button on the 97
frontend that I've been making changes to before I convert that frontend to
a 2000 version for 2007 users.

The button simply opens each of the 11 forms in Design View and sets the
Filter property to an empty string:
e.g.,
DoCmd.OpenForm "Names_ActiveOnly", acDesign
Forms!Names_ActiveOnly.Filter = ""
DoCmd.Close , , acSaveYes 'repeat for the other 10 forms

This is a quick way to fix the problem and I don't have to have access to a
computer with 2007, nor does the 2007 user have to see the forms being
opened in Design View while some code changes the filter properties.

Mark

Mark, you may have already thought of this or tried it, but for the forms you
want to open to a specific record if it's opened from a certain form, you
could use the IsOpen module/function and on load event procedure. I use that
quite often when I have a form that opens different depending on where it's
opening from.

I have not upgraded to '07 due to many things we see on this site, but it
sounds like this should work if '97 and '07.

Just a suggestion.
--
Thanks - Robin

Thanks, Allen

But it seems that DoCmd.ShowAllRecords would need to be in the form's Open
event to override the "File ... " entry leftover from the 97 development
version. The problem with using DoCmd.ShowAllRecords in the Open event of
the form is that it negates any attempt to open the form to a specific
record via a command button on some other form ... it negates the
WhereCondition specified in the DoCmd.OpenForm procedure of that command
button.

Your previous suggestion of code which applies certain steps depending upon
the version of the frontend does seem to be one way to go, although I think
I would still need to determine whether the form was opened via the
switchboard (where I want it opened unfiltered), or from some other form
(where I want it filtered via a command button and a WhereCondition).

I've only got 11 forms that are problematic, however, and the simplest
solution may be just to remove the "Filter ... " entry in the '97 version
before converting and distributing. If I forget, then the 2007 user just has
to remember to toggle the "Filtered" button, or simply re-filter to the
client they want -- which is probably what they'll want to do anyway.

This wasn't a problem with 2000/2002-2003, and it's not a terrible problem
for 2007 users. Just one of those unexpected changes that can be a little
annoying.

Thanks again for your help.
Mark

My suggestion would be to have the form open without a filter.

If you want to open it with a filter, you can then pass an argument in its
WhereCondition.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
Allen,
I apologize for taking up so much of your time on this issue -- it pales
in
comparison to the other issues you help with in this newsgroup. But, yes,
I
could check the version number, if that's what your suggesting, but I
would
also have to check where the form is being loaded from: Is it from the
switchboard, in which case I want to set "Filter On Load" to "No", or is
the
form being opened from another form (filtered to a particular

client)
and

I
want "Filter On Load" set to "Yes". [or, I could use 2 different forms --
one which opens from the switchboard, and one which opens from some other
form].

I may try going through these 11 forms on my Access 97 machine just before
converting to 2000 and deleting the "Filter ..." entry. It seems to be
this
last filter value in the '97 version which gets passed along to the
converted frontend and then on to the Access 2007 machines.

Thanks,
Mark


Perhaps you could set the property in Form_Open, if the version >= 12.

Allen,
Ok, I was incorrect. The earlier Access 2007 machines *do* still have
the
problem, but only on 1 out of 11 forms. The difference being that on
that
particular form the "Filter On Load" property is set to "Yes". The
other
10
forms have it set to "No".

All I've done is distribute the converted frontend (converted

from
'97

to
2000) to the 2007 machines. I can see no difference on any of the 11
forms
in the '97 version in terms of properties which have to do with
filtering.
I
certainly haven't gone to the 2007 versions, opened the database, and
made
any changes to the "Filter On Load" properties on the different forms
after
distributing the new frontend. Even if I would have done this

long
ago,

I
would think that replacing the frontend (which occurs rather
frequently)
would have overwritten any changes made during prior versions of the
frontend.

Is there something in the '97 version that I'm missing which would
result
in
changes to the "Filter On Load" property setting in Access 2007?

Is there something in 2007 that the casual user can change (i.e., other
than
going into Design view) which could cause this setting to change from
"Yes"
to "No" and vice versa?

Mark



You won't be able to use the A97 interface to set these properties,
since
you won't find them in the Properties box in that version.

If you create an MDE in A97, you won't be able to open it in design
view
and
change anything in A2007.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
Thanks, Allen
... but wouldn't I have to redo this each time that I issue a
revised
frontend since development occurs using Access 97?
... and why on this new computer with Access 2007 and not on the
other
two
computers which also run Access 2007? They each receive the same
frontend.
Mark

Forms and reports in A2007 have new properties named:
Filter on Load
Order By on Load

Open each form/report in design view, and set both properties to
No.
Save.
Close.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

I develop using Access 97, convert to Access 2000 on an

Office
XP

machine
and distribute the frontend.

I ran into a problem awhile back on Access 2007 machines when
they
first
started showing up in our office where forms opened to the last
filter
applied during development. Somehow, I fixed that but cannot
remember
what
I
did! I don't have any code involving the filter property in the
Open
or
Load
events.

When I distribute the new frontend to these "older" computers --
one
running
Access 2007 on a Windows XP machine, and the other running Access
2007
under
Vista, the forms still open as desired showing all records based
on
the
underlying query for the form. In Design view, you can

still
see

the
last
filter applied (Filter ...), but the form actually opens showing
all
records, which is what I want.

We just got a new machine with Access 2007 running under Windows
XP.
Darn
if
the filtering problem isn't back on this new machine. I'm
distributing
the
same frontend as with the two other Access 2007 computers. Those
other
two
open forms showing all records, this new one opens filtered to
whatever
was
left in the "Filter ..." property during development.

Anyone know what the difference could be between these "older"
Access
2007
computers and this new one? Again, they're all running the same
frontend.

Do I need to add "DoCmd.ShowAllRecords" in the Open event

on
all

my
forms,
or is there some "option" under Access 2007 that I've forgotten
to
set?

Thanks for any insight or suggestions,
Mark