Applyfilter works, sometimes

[Leif]

I've built a query form that returns a string I want to apply to the main
form. Sometimes that works fine, other times I get the message "You can't
use the ApplyFilter action on this window". To try to be sure I'm on the
correct window I do a docmd.SelectObject command first, but that does not
help. My code looks like the following:

If qFilter <> "" Then
DoCmd.SelectObject acForm, "Project"
DoCmd.ApplyFilter , qFilter
End If

where qFilter is the filter string and "Project" is the name of form I want
to apply the filter to. As a work around I tried to set qFilter to the form
filter property, however, this only works if the string is not too big. I
need the 32K character length that apply filter gives me.

In as case where it works the string is:
"ProjectID IN (597)"

In a case where it did not work the string is:
"ProjectID IN (4)"

This is a hard one. Thanks for your help.

 

[Rob Oldfield]

It's something to do with data being out of date when you try to run the
code. Try adding in a requery...

If qFilter <> "" Then
me.requery
DoCmd.SelectObject acForm, "Project"
DoCmd.ApplyFilter , qFilter
End If

....and if that doesn't work, then what context is this being run in? Which
event? What is the form based on: a straight table, a query of a single
table, or a query of multiple tables?

 

[Leif]

Rob,

Thanks for your reply. I found the problem, and it does not seem to relate
to the error message I got. The recordsource I had did not include the items
I was using in my filtering (which is similar to what you suggest with the
requery).

Don't you think it is odd that using applyfilter you can have a string up to
32K, but with the filter property the size is much more limited? My query
form sets a string variable for applyfilter to use. I found that if I try to
set the filter property of the form instead that at 1800 characters it fails.
I don't know what is the actual limit.

Thanks,
Leif

 

[Rob Oldfield]

Glad to hear it.

Yes... the filter length thing is a bit hard to explain. There is an
alternative method... write the values that you wish to filter on into a
temporary table, and then use that table in the record source of the form to
limit it to just those records with a match.