Q: How to set a filter on a sub-form?

[Brian Newman]

I've got what is actually a triple-layer nested form. That part works
fine. I've got the first subform related by the right key field to
the main form, then I've got the second subform related to the first
subform by their shared key field.

Browsing in that arrangement works beautifully.

Now, the third tier is the line item data, and there are a number of
different kinds of line items sharing the same table -- specifically,
there are records for Trees, Stumps, Ground Cover, Sapling clumps, and
so on. It's just as if they were different items in an inventory.

I have a number of buttons on the third tier form that I want to use
to filter those data records -- the first button shows only "Trees",
and so on. I'm using "DoCmd.ApplyFilter 'FilterName'", where
'FilterName' is a query on the third tier table.

Running the form stand-alone, the filters work fine (though they
filter on the entire table, of course).

Running the form nested, the DoCmd keeps wanting to apply the filter
to the currently active form, which is always the topmost form, i.e.
the first tier. So it asks for field values that don't exist and then
craps out.

I've tried every method I could think of for setting focus, setting
the active window, etc., and nothing seems to activate the third tier
window to do the filter and then return to the top level.

If I actively click in the third tier window and then apply the
filter, it works, but the first tier and second tier go blank because
they're out of context.

Any thoughts?

[Bob Quintal]

b2******@or.blm.gov (Brian Newman) wrote in
news:3a*************************@posting.google.co m:

I've got what is actually a triple-layer nested form. That
part works fine. I've got the first subform related by the
right key field to the main form, then I've got the second
subform related to the first subform by their shared key
field.

Browsing in that arrangement works beautifully.

Now, the third tier is the line item data, and there are a
number of different kinds of line items sharing the same table
-- specifically, there are records for Trees, Stumps, Ground
Cover, Sapling clumps, and so on. It's just as if they were
different items in an inventory.

I have a number of buttons on the third tier form that I want
to use to filter those data records -- the first button shows
only "Trees", and so on. I'm using "DoCmd.ApplyFilter
'FilterName'", where 'FilterName' is a query on the third tier
table.
I've tried every method I could think of for setting focus,
setting the active window, etc., and nothing seems to activate
the third tier window to do the filter and then return to the
top level.

Any thoughts?

Instead of using a stored query to filter, put a little bit of code
behind the buttons in the subform.

example
sub Btn_Tree_Click
me.filter = "[Item] = 'tree'"
me.filteron = true
end sub

sub Btn_All_Click
me.filter = ""
me.filteron = false
end sub

Bob Q

[Brian Newman]

Bob Quintal <bq******@generation.net> wrote in message news:<f0******************************@news.terane ws.com>...

Instead of using a stored query to filter, put a little bit of code
behind the buttons in the subform.

That works beautifully. Thanks.