Change subform filter after lookup?

[Dennis]

In Access2003, I have a subform that I set a filter for on initial lookup. It
works fine. Then, I want to "re-filter" the data (for example, to limit the
records displayed to those matching a certain Job Number for a given Tech),
but the subform doesn't seem to requery.

I've tried the following to no effect:

Me.HoursDetail.Form.Filter = FilterString

where HoursDetail is the name of the subform control on the parent form, and
FilterString is the text of the filter. If I close and reopen the subform as
stand-alone, the new filter gets applied properly, but of course that defeats
the purpose of having a subform.

I have also tried to use:

Me.HoursDetail.Requery

afterward, but also to no effect

Any ideas would be most helpful.

TIA!

 

[Marshall Barton]

In Access2003, I have a subform that I set a filter for on initial lookup. It
works fine. Then, I want to "re-filter" the data (for example, to limit the
records displayed to those matching a certain Job Number for a given Tech),
but the subform doesn't seem to requery.

I've tried the following to no effect:

Me.HoursDetail.Form.Filter = FilterString

where HoursDetail is the name of the subform control on the parent form, and
FilterString is the text of the filter. If I close and reopen the subform as
stand-alone, the new filter gets applied properly, but of course that defeats
the purpose of having a subform.

I have also tried to use:

Me.HoursDetail.Requery

afterward, but also to no effect

Did you forget to use:
Me.HoursDetail.Form.FilterOn = True

 

[Daryl S]

Dennis -

Try these (note the .Form in the statements):

Me.HoursDetail.Form.FilterOn
Me.HoursDetail.Form.Requery

 

[Dennis]

Daryl, I did the filterOn = True. But do you have to have that set to true
BEFORE changing the filter text?

marshall, the reQuery was ineffective. I've done this successfully in times
past, so I KNOW it's possible. But this thread was started because I had no
freakin' clue as to why it wouldn't work. So I guess I'm still kind-of at a
loss as to what's not working properly. Any other ideas? (Oh, I just
remembered - the subform is not actually tied to the parent form. It's a
fully independent query that drives it, and I set an initial filter when I
open the parent.)

Thanks guys!

Dennis

 

[Daryl S]

Dennis -

No, I should have indicated these were two different things to try. I would
make sure the filter was turned on after providing the filter criteria. I
assume the filter criteria you are writing is good for the recordsource of
the subform, since that data is not related to the parent form. You
shouldn't need to requery if you are just applying a filter that is
consistent with the records in the subform.

When you run your code, does the filter not work? What happens if you
toggle the Apply Filter button when the focus is in the subform - can you see
the filter being applied and removed? If so, the syntax of the filter string
is probably fine, and you may want to try the ApplyFilter of the DoCmd...

 

[Dennis]

I don't use the Aplpy Filter button. I want to do everything in VBA. I use
the ".Filter = " and then the ".FilterOn = True". But I do these from the
parent form. As I said, it works fine on the initial lookup, but then once
the subform is opened to the records from the initial Filter statement, I
cannot load a filter over that one and requery. If I CLOSE the subform, THEN
it works, but now while the subform is OPEN. But when I reopen the subform,
it becomes stand-alone, which is not what I want at all.

Thanks!

 

[Douglas J. Steele]

What's the exact code you're using to set the Filter and FilterOn
properties?

 

[Marshall Barton]

I did the filterOn = True. But do you have to have that set to true
BEFORE changing the filter text?

The reQuery was ineffective. I've done this successfully in times
past, so I KNOW it's possible. But this thread was started because I had no
freakin' clue as to why it wouldn't work. So I guess I'm still kind-of at a
loss as to what's not working properly. Any other ideas? (Oh, I just
remembered - the subform is not actually tied to the parent form. It's a
fully independent query that drives it, and I set an initial filter when I
open the parent.)

Note that FilterOn is normally used after setting the Filter
Property. That is not a rule, but the behavior may be a
little different if it's before. (Think about not changing
the filter and just turning it on and off, or a situation
where FilterOn is already True and you just set it to a
different condition.)

A Requery is automatic after changing the Filter and
FilterOn is True so it's never needed in those situations.

I suspect your problem is because you have something else
going on in your code, so please post a Copy/Pates of the
code and we'll look at it.

 

[Daryl S]

Dennis -

Can you post your code? Maybe we can spot the issue there...

 

[Dennis]

Here's the exact code I'm using:

Me.HoursDetailNEW.Form.FilterOn = False
Me.HoursDetailNEW.Form.Filter = FilterString
me.hoursdetailNEW.Form.FilterOn = True

Now mind you, the form HoursDetailNEW is the parent, and the subform is
bound by a drop-down's selected value. Then if the user pulls up a separate
form, they can select additional filtering by JobNumber, StartDate and
EndDate, or any combination of those. I recreate the filter text
(FilterString) to match the following example's syntax:

Tech = 'TA021609160214984001' and JobID = '4312' and (Date GE #3/1/2010# and
Date LE #3/22/2010#)

I know I could use BETWEEN for the dates, but I'm building the filter string
dynamically, and don't know whether or not both dates are being filled in for
selection criteria ahead of time. (Oh, I guess I could test that before
building the string couldn't I? Funny how just talking about it can help
sometimes.)


Thanks for trying to help me out on this everyone; I really appreciate it!!

Dennis

PS: I suppose as a fallback, I could unbind the parent/child relationship of
the two forms and do an ADO lookup in the subform, then set "Me.Recordset" =
the ADO.Recordset. But to me, that's a kludge...

 

[Daryl S]

Dennis -

Are you really using LE and GE? Also, I hope you don't have a field called
'Date' - that is a reserved word. If so, put square brackets around the
fieldname. How about this:

Tech = 'TA021609160214984001' and JobID = '4312' and ([Date] >= #3/1/2010#
and
[Date] <= #3/22/2010#)