Filtering multiple subforms pulling from same query...

[Guest]

I have created a form that contains a tab control with 5 tabs. Each tab
contains identical subforms and all read from a single query. The subforms
of course are named differently, but the contents and layouys are all the
same.
The intension is to pull up a record on the main form and each tab would
show the subforms with different filters.
I have created filters for each subform that are applied at "onload" for
each subform. And then apply the filteron at that time as well.
This seems to work most of the time, but for some reason I cant get all 5
subforms to filter. I open the subforms separately and they all filter fine.
Then open the main form containing the surforms and the filters on 2 subform
are lost. I have even copied existing subforms that filter fine, change
nothing, and use them in place of another that doesn’t work, and it doesn’t
filter. I have been working on this for some time and am starting to wonder
if there isn’t something wrong with Access.


I know I can get this resolved by making a separate filtered queries for
each subform, but I am trying to avoid this. I would like to have a single
query driving several filtered subforms all viewable under a single main
form.
Is this reasonable or do you really need separate queries to drive subforms?
I am wondering if there is a better way to do this.

I would greatly appreciate any feedback!

 

[tina]

well, your user can only look at one tabpage at a time, so suggest you try a
different approach:

1. delete the subform controls from all the tab pages except one.
2. on that one tab page, select the subform control and "cut" (Ctrl+x) it
from the tab page.
3. then click in the main form's Detail section and paste the subform
directly onto the Detail section.
4. now move the subform control into position "under" the tab control; the
subform will show "through" the tab control, and be visible on every tab
page. to the user it will look like there is a subform on every tab page.
5. in a tab control, each tab page has a PageIndex property; the first page
is numbered as 0 (zero), the second page is 1, the third is 2, etc. the
"value" of the tab control is equal to the PageIndex property of the
currently selected page - for example, if the current tabpage is indexed as
3, then the value of the tab control = 3. you can use this value to change
the Filter property of the subform as the user moves from tabpage to
tabpage, as follows:
6. in the tab control's Change event procedure, add the following code, as

Select Case Me!TabControlName
Case 0
Me!SubformControlName.Form.Filter = _
"the filter string for the first tabpage"
Case 1
Me!SubformControlName.Form.Filter = _
"the filter string for the second tabpage"
Case 2
Me!SubformControlName.Form.Filter = _
"the filter string for the third tabpage"
Case 3
Me!SubformControlName.Form.Filter = _
"the filter string for the fourth tabpage"
Case 4
Me!SubformControlName.Form.Filter = _
"the filter string for the fifth tabpage"
End Select

Me!SubformControlName.Form.FilterOn = True

go ahead and use the subform's Load event to set the filter that's
appropriate for the "first" tab page; that way the user will see the
correctly filtered records when the main form is initially opened. using
this technique should also make your main form load faster when it is
opened, because you're only loading one subform object.

hth

 

[Guest]

Hi Tina,
Interesting approach.
I was doing something similar with option buttons on one of the tab pages,
but I like your idea as well.
Thank you !

 

[tina]

you're welcome

Hi Tina,
Interesting approach.
I was doing something similar with option buttons on one of the tab pages,
but I like your idea as well.
Thank you !