Filters and subforms

[John Crighton]

Hi all,

I thought I knew a way - at least I assumed it would be
easy...

Is there any way to apply a filter to a subform??? I
think I've tried everything, but I'm conviced there must
be a way. It's not feasible in this case to change the
recordsource of the subform.

Thanks in advance!

John C

 

[Allen Browne]

With Me.[NameOfYourSubformHere].Form
.Filter = "Surname = ""Jones"""
.FilterOn = True
End With

If you also filter the main form at the same time as you filter the subform,
there is a bug in Access when you remove the filter. It loses track of the
fact that the other filter is still in place, and you have to close the form
before you can unfilter and get the other records again.

 

[Marshall Barton]

Is there any way to apply a filter to a subform??? I
think I've tried everything, but I'm conviced there must
be a way. It's not feasible in this case to change the
recordsource of the subform.

You can set the subform's Filter property:

Me.subform.Form.Filter = "filed = " & value
Me.subform.Form.FilterOn = True

BUT, there are a lot of idiosyncracies with the Filter
property. E.g. setting a filter on one subform will clear
the Filter on another subform and/or the main form.

In my experience, setting the subform's RecordSource is the
only reliable way to do what you want. Could you explain
why you think it not feasible in your case?

 

[John Crighton]

Allen, thanks...

Could have sworn I'd tried that already, but it (or
something similar) works.

I think it's just one of those days...

Thanks again,

John C

-----Original Message-----
With Me.[NameOfYourSubformHere].Form
.Filter = "Surname = ""Jones"""
.FilterOn = True
End With

If you also filter the main form at the same time as you filter the subform,
there is a bug in Access when you remove the filter. It loses track of the
fact that the other filter is still in place, and you have to close the form
before you can unfilter and get the other records again.

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

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

I thought I knew a way - at least I assumed it would be
easy...

Is there any way to apply a filter to a subform??? I
think I've tried everything, but I'm conviced there must
be a way. It's not feasible in this case to change the
recordsource of the subform.

.

 

[iCS]

Hi Marshall,

In this case, the recordsource its self is fairly messy,
it has several where statements and is very long already.

On top of this, I need to change the recordsource
depending on something else.

I need to make a filter up in code depending on the state
of 10 or so controls, and although it may be possible, it
doesn't seem like the easiest option to modify the
recordsource.

It's all a bit complicated, and if I don't have to change
the recordsource, then I won't - but then again if there
are problems then I may have to.

There are no other filters on any other subforms, however
there is one on the main form, but I can get rid of that
if need be with a bit of ADO.

Thanks for the advice.

John.