REMOVING FILTERS from subforms.

  • Thread starter Thread starter Bill Mitchell
  • Start date Start date
B

Bill Mitchell

I want to write some code I can use in a shortcut menu
that will remove a filter from the active subform without
effecting the parent form.

Any ideas?

Also, the built in docmd.runcommand.filterbyselection
seems to work well at filtering the active subform by
whatever happens to be the activecontrol. Does anyone
know the underlying code for this action?

It would be helpful to know this because I seem to be
having a hard time writing code once which can be used in
many shortcut menus that will work on the active form
whenever it is called.
 
The easiest way to set a filter for a form is to set its Filter property
directly:
Me.Filter = ...
Me.FilterOn = True

Even though that works with subforms, and Access maintains each form's
filter independently, there is a bug in the way Access removes filters. If
you have a filter applied to both a main form and a subform at the same
time, when you remove one of them Access gets confused. The records in the
other are still filtered, but Access falsely reports that its FilterOn is
False. As a result, there is no way to remove the filter other than to close
the form and reopen it.
 
I would assume the following code should do the trick:

Dim frm As Form
Dim frmSub As Form

Set frm = Screen.ActiveForm
Set frmSub = frm.Controls(frm.ActiveControl.Name).Form

frmSub.Filter = ""
frmSub.FilterOn = False

You don't mention if the subform is being controlled by the link
master/child fields, or if you actually set the source of the sub-form by
code?

Anyway, give the above code a try. The above does work for me, and in my
case I am using the sub-form with the link "master/child" field set. Hence,
I am able to filter for the given record. If you are to navigate to another
record in the "main" form, then the sub-form filter still does remain (it is
not clear what you want to do in this case, or intend to do when the main
form record is changed?. The sub-form filter will remain active, but the
link/master set still functions also in this case. If one needs more
control, then I would consider dumping the use of the link/master setup (it
just depends on what you are trying to accomplish in your case).
 
If you are to navigate to another
record in the "main" form, then the sub-form filter still does remain
Click to expand...

Just to be clear on the above, my sample code does remove the filter, and
the filter is thus gone. My only point above is that when you do apply a
filter to a sub-form, it remains active even when you navigate to a
different record on the *parent* form.

So, my sample code does remove the filter, and thus when you navagate to
another record, the filter is thus removed, all is just well and fine.
 
Thanks Allen,

But what if I am trying to write code for a generic
shortcut menu that would work anywhere it is called? In
that case "Me." won't work. Is there something I could
use instead of Me. that would apply the filter to the
active form, whatever it is, even if it is a subform?

Is there some code that will accurately generate the full
nomenclature for the current form wven if it is a
subform? I could then use that in a With statement and
get my desired reults:

Is there something that will generate the .Name of the
active subform? If then I could generate the full path.
I have often wondered why Access doesnt just have this
functionality built in. It seems like something everyone
could use.

With FormLocation()...
 
You could try Screen.ActiveForm, but I've not found that to be reliable.

Better to pass a Form variable to your generic routine that identifies the
form you are running the code on. You can code the receiving routine like
this:
Public Function FormLocation(frm As Form)
and call it from VBA code like this:
Call FormLocation(Me)
or from an event property:
=FormLocation([Form])
 
Did you take a look at my other post in this thread? I am betting that the
code posted should work just fine. That code is also generic, and will work
from a menu/shortcut menu. It picks up the active sub form, and removes the
filter.
 
Yes, thanks so much. Is it possible to make this also
work for a subform within the subform?>

Bill
 
Well, you do have to write for at lest a specific sub form level.

Given a basic understanding of how collections work, then lodgically, the
follwing would work:

Dim frm As Form
Dim frmSub As Form
Dim frmSubSub As Form

Set frm = Screen.ActiveForm
Set frmSubSub = frmSub.Controls(frmSub.ActiveControl.Name).Form

frmSubSub.Filter = ""
frmSubSub.FilterOn = False

However, it would be intresting to make the above code work for both forms,
sub-forms, and sub-sub forms. I have not given this problem some
thought...but I thinking that code would be written to handle all three
cases.

The follwing code should would work for all cases:

Dim frm As Form
Dim frmSub As Form
Dim c As Control

Set frm = Screen.ActiveForm
Set frmSub = frm
Set c = frm.ActiveControl
Do While c.ControlType = acSubform
Set frmSub = frmSub.ActiveControl.Form
Set c = frmSub.ActiveControl
Loop

frmSub.Filter = ""
frmSub.FilterOn = False
 
Back
Top