B
bob92122
Is there a way to use VBA to "re-filter" an open form other than the sequence
below?
formname.filter = "filtertext"
formname.filteron = "true"
Here's why I ask: I have a large table that I need to view in several ways
simultaneously. Therefore, I have a main form with three subforms, two of
which are nested with additional sub-subforms, for a total of eight sub- or
sub-subforms on the page. I know--it's crazy--but that's how I need to show
the data. To make things worse, the records jump around among the eight
subforms frequently as data change.
Right now, I have each of the eight subforms populating from a separate query.
It works fine, but I wind up having to run each of the eight queries (with
code) several hundred times a day. It seems to me that it would have to be
much faster if, instead of doing eight requeries of 10,000 records over and
over, I could instead do one query, one time, to collect a smaller dataset
(those with today's date) and then filter and re-filter those 50 or so
records eight ways.
So far, so good. But here's the problem. The filters themselves would have
to be fairly intricate and, because there are nested subforms, even the names
of the fields would be complicated. So, back to my question: if I query once
and then open my forms with filters, is there a way for me to make the eight
filters current without using the code above to re-enter them in full? Using
formname.requery doesn't do it, nor does formname.refresh or .repaint.
Any thoughts or suggestions would be appreciated.
below?
formname.filter = "filtertext"
formname.filteron = "true"
Here's why I ask: I have a large table that I need to view in several ways
simultaneously. Therefore, I have a main form with three subforms, two of
which are nested with additional sub-subforms, for a total of eight sub- or
sub-subforms on the page. I know--it's crazy--but that's how I need to show
the data. To make things worse, the records jump around among the eight
subforms frequently as data change.
Right now, I have each of the eight subforms populating from a separate query.
It works fine, but I wind up having to run each of the eight queries (with
code) several hundred times a day. It seems to me that it would have to be
much faster if, instead of doing eight requeries of 10,000 records over and
over, I could instead do one query, one time, to collect a smaller dataset
(those with today's date) and then filter and re-filter those 50 or so
records eight ways.
So far, so good. But here's the problem. The filters themselves would have
to be fairly intricate and, because there are nested subforms, even the names
of the fields would be complicated. So, back to my question: if I query once
and then open my forms with filters, is there a way for me to make the eight
filters current without using the code above to re-enter them in full? Using
formname.requery doesn't do it, nor does formname.refresh or .repaint.
Any thoughts or suggestions would be appreciated.