Bug in DoCmd.ApplyFilter?

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

This was supposed to be an easy little tweak to a form and
has cost me a day tearing out my hair...

When I finally boiled the problem down to its roots,
here's the problem I'm left with...

DoCmd.ApplyFilter , "[CDB]![Status] = " & t

.... works once. If I change the value of t and run the
command again it ignores the new value of t and uses
whatever the previous value was.

.... and continues to do so until I do one of 3 things...
1. Close the form it's triggered from and reopen it.
2. Right Click "Remove filter" (Button bar remove filter
does NOT affect the problem).
3. Amend the module code and save it.
.... at which point it works once more with the next value
of t and sticks on that.

Can anyone give me any clues as to why this doesn't work
and what can be done to get around it?
 
If I remember this problem correctly from an earlier post, ACCESS doesn't
actually rerun the filter so long as it "sees" the same filter being used.
It doesn't note that the value is changed.

And of course I can't find my copy of this older post..... :-(

Try this:

When you want to change the value being used in the filter, first turn the
filter off, then set the new value into the filter, and then turn it back
on.

Let me know if this doesn't work, and I'll keep looking for that old post
about this issue.
 
DoCmd.ApplyFilter , "[CDB]![Status] = " & t
... works once. If I change the value of t and run the
command again it ignores the new value of t and uses
whatever the previous value was.
Click to expand...

Try commenting out your line of code and adding these lines to see if it makes a
difference:

'************CODE START
'Set the form's filter
Me.Filter = "[CDB]![Status] = " & t
'If "t" is a string, use the following line instead
'Me.Filter = "[CDB]![Status] = """ & t & """"

'Make sure that the filter is applied
If Me.FilterOn = False then
Me.FilterOn = True
End If
'************CODE END
 
I did have a ShowAllRecords line in the macro and its
equivalent translated into the VBA. That didn't make a
difference.

I was able to make a difference by creating quotes around
the string; ...![CDB]="'" & t & "'".

That worked but when I tried to do the eqivalent in the
Macro it wouldn't let me.

So I'm stuck with VBA.

Has this been reported as a bug? How would you even do
that?
-----Original Message-----
If I remember this problem correctly from an earlier post, ACCESS doesn't
actually rerun the filter so long as it "sees" the same filter being used.
It doesn't note that the value is changed.

And of course I can't find my copy of this older post..... :-(

Try this:

When you want to change the value being used in the filter, first turn the
filter off, then set the new value into the filter, and then turn it back
on.

Let me know if this doesn't work, and I'll keep looking for that old post
about this issue.

--
Ken Snell
<MS ACCESS MVP>

Neil said:
This was supposed to be an easy little tweak to a form and
has cost me a day tearing out my hair...

When I finally boiled the problem down to its roots,
here's the problem I'm left with...

DoCmd.ApplyFilter , "[CDB]![Status] = " & t

... works once. If I change the value of t and run the
command again it ignores the new value of t and uses
whatever the previous value was.

... and continues to do so until I do one of 3 things...
1. Close the form it's triggered from and reopen it.
2. Right Click "Remove filter" (Button bar remove filter
does NOT affect the problem).
3. Amend the module code and save it.
... at which point it works once more with the next value
of t and sticks on that.

Can anyone give me any clues as to why this doesn't work
and what can be done to get around it?
Click to expand...


.
Click to expand...
 
I was able to make a difference by creating quotes around
the string; ...![CDB]="'" & t & "'".
Click to expand...

No bug. Apparently "t" contains a string value, not a number. That approach was
included in my response to you.

"[CDB]![Status] = """ & t & """"

:-)
 
Back
Top