P
Paul Simon
Here's the structure of my database. A table (Table1) containing
about 4000 records. A query (Query1) which runs off Table1. A form
(Form1) containing a sub-form (SubForm1). Form1 itself contains no
data - just controls. SubForm1 is in datasheet view.
I have 78 macros, each of which runs Query1 with different parameters.
Basically, each macro initiates the OpenForm action, with SubForm1 as
the Form Name and Query1 as the Filter Name. The only difference
between each macro is the Where condition.
Thus, when I run a particular macro, my main form (Form1) comes up,
with the sub-form (Subform1) showing the results of the query (Query1)
generated by that particular macro.
I now want to have controls (preferably Option Buttons) on Form1 which
will further filter SubForm1. I will have about 14 such filters which
I will either use individually of in combination. I also want to be
able to "turn off" these filters and return to the original queried
data in SubForm1.
Here's an example: I run Macro1 which runs Query1 with a certain
Where condition. This results 15 records, which are then shown in
Subform1. Of those 15 records, 4 have the letter "V" in a field named
VHS. One of the 14 filters I want to create will filter out those 4
records, now leaving 11 showing in SubForm1. When I "turn the filter
off", I want the original 15 records to reappear in SubForm1.
In experimenting with how to accomplish this, I created a Command
Button on the main form Form1 with the following code in it:
With me.[SubForm1].Form
.Filter = "VHS <> ""V""
.FilterOn = True
End With
To "turn off" the filter, I created a 2nd Command Button with the
following code:
With me.[SubForm1].Form
.FilterOn = False
End With
(I'm using Command Buttons when experimenting, but again, I'd prefer
Option Buttons in the final format.)
The problem is that the above code is NOT filtering the sub-form.
Instead, it's running the filter against the full 4000-record
database. In other words, using the example above, Macro1 generates
15 records in SubForm1 (embedded in Form1) 4 of which have "V" in the
VHS field. When I run the first code shown above, instead of SubForm1
now just showing the 11 remaining records of the 15, it shows ALL the
records from the full 4000 that don't have a "V" in that field.
1.) How do I filter data in a query-generated sub-form?
2.) How can I then "turn off" that filter to get back to original
queried data in that sub-form?
All help would be greatly appreciated.
Many thanks,
Paul Simon
about 4000 records. A query (Query1) which runs off Table1. A form
(Form1) containing a sub-form (SubForm1). Form1 itself contains no
data - just controls. SubForm1 is in datasheet view.
I have 78 macros, each of which runs Query1 with different parameters.
Basically, each macro initiates the OpenForm action, with SubForm1 as
the Form Name and Query1 as the Filter Name. The only difference
between each macro is the Where condition.
Thus, when I run a particular macro, my main form (Form1) comes up,
with the sub-form (Subform1) showing the results of the query (Query1)
generated by that particular macro.
I now want to have controls (preferably Option Buttons) on Form1 which
will further filter SubForm1. I will have about 14 such filters which
I will either use individually of in combination. I also want to be
able to "turn off" these filters and return to the original queried
data in SubForm1.
Here's an example: I run Macro1 which runs Query1 with a certain
Where condition. This results 15 records, which are then shown in
Subform1. Of those 15 records, 4 have the letter "V" in a field named
VHS. One of the 14 filters I want to create will filter out those 4
records, now leaving 11 showing in SubForm1. When I "turn the filter
off", I want the original 15 records to reappear in SubForm1.
In experimenting with how to accomplish this, I created a Command
Button on the main form Form1 with the following code in it:
With me.[SubForm1].Form
.Filter = "VHS <> ""V""
.FilterOn = True
End With
To "turn off" the filter, I created a 2nd Command Button with the
following code:
With me.[SubForm1].Form
.FilterOn = False
End With
(I'm using Command Buttons when experimenting, but again, I'd prefer
Option Buttons in the final format.)
The problem is that the above code is NOT filtering the sub-form.
Instead, it's running the filter against the full 4000-record
database. In other words, using the example above, Macro1 generates
15 records in SubForm1 (embedded in Form1) 4 of which have "V" in the
VHS field. When I run the first code shown above, instead of SubForm1
now just showing the 11 remaining records of the 15, it shows ALL the
records from the full 4000 that don't have a "V" in that field.
1.) How do I filter data in a query-generated sub-form?
2.) How can I then "turn off" that filter to get back to original
queried data in that sub-form?
All help would be greatly appreciated.
Many thanks,
Paul Simon