Filter a Sub-Form Using Option Buttons

  • Thread starter Thread starter Paul Simon
  • Start date Start date
P

Paul Simon

I'm very inexperienced at this so I may have trouble even
explaining my problem.

1.) I have a form named "Form1" in Form View.
2.) Within that form, I have a sub-form named "frm Main"
in Datasheet View.
3.) This "frm Main" sub-form shows records generated by a
query named "qry Main". (This is the only query in the
database.)
4.) I have several macros that run the Action "OpenForm"
in which the Action Arguments indicate Form Name as "frm
Main", View as "Datasheet", Filter Name as "qry Main" and
a Where Condition specific to that macro. So, depending
on which macro I run, the "qry Main" query gives me
different results in the "frm Main" sub-form.

Now what I want to do is to further "filter" this sub-form
as follows.
1.) There is a field entitled "NA". For some records,
this field is blank; for others this field contains the
letter "v".
2.) I'm trying to create an option group (not a toggle
button) at the top of "Form1" that contains 2 option
buttons labeled "vYes" and "vNo".
3.) The purpose of these buttons is to further filter the
records in sub-form "frm Main" (which was developed by
whatever macro I ran).

For example: Let's say I have 1000 records in my
database. I run a macro that results in 20 records
showing in sub-form "frm Main". Of these 20 records, 15
are blank in the "NA" field and 5 have "v" in the "NA"
field. When I click the "vNo" option button, I want to
filter the sub-form so that only the 15 records that are
blank in the NA field show in the sub-form. Then, when I
then click the "vYes" option button, the original 20
records show again.

If another macro generates 30 records with 20 blanks and
10 with "v", then the "vNo" button is to filter the sub-
form to show only the 20 in the sub-form, then
clicking "vYes" would show the 30 again.

Lastly, I want the "vYes" button to be the default.

I know how to place the option group and option buttons on
the "Form1" form, so I don't need help there. I just
don't know how to "code" the buttons to accomplish the
above.

Any detailed help for this complete novice would be
greatly appreciated.

Many thanks,
Paul Simon
 
There's several ways of dealing with this:
1) On update
If Me!OptionGroupName = 1 (YES) then Me!Form!
NameOfSubform.RecordSource = "NameOfYesQuery.
If Me!OptionGroupName = 2 (NO) then Me!Form!
NameOfSubform.RecordSource = "NameOfNoQuery.
Me!SubformName.Requery

2) Make two or three subforms. Each one running it's own
query: 1) All, 2) Yes, 3) No. Make two of them NOT
VISIBLE and place them all on top of each other.

On Update:
If Me! OptionGroupName = 1 (All) then
Me!Subform1.Visible = -1
Me!Subform2.Visible = 0
Me!Subform3.Visible = 0

If Me! OptionGroupName = 2 (Yes) then
Me!Subform1.Visible = 0
Me!Subform2.Visible = -1
Me!Subform3.Visible = 0

If Me! OptionGroupName = 3 (No) then
Me!Subform1.Visible = 0
Me!Subform2.Visible = 0
Me!Subform3.Visible = -1

You could also use a Select Case. There are many ways to
skin this cat!
 
Dan,

Thank you so much for taking the time to respond to my
inquiry - I appreciate it very much.

Your solution doesn't exactly do what I'm looking for,
however. I do not have a "yes" query and a "no" query. I
just have 1 single query named "qry Main".

Depending on the particular macro I run, each with it's
own "Where" condition, I get different results in my "frm
Main" subform generated by the "qry Main" query.

I have 78 such macros (but again, just 1 query). If I
create a "yes" and "no" for each, I'll wind up with 234
macros. I'd like to avoid this (although, perhaps this is
the only way around this).

What I really want to do is further "filter" (if that's
the right term) the results of a macro.

For example, I have 3238 records in the database. If I run
macro #12, the "qry Main" query will result in 16 records
which then appear in the "frm Main" subform. 12 of those
are blank in the "NA" field and 4 have a "v" in the "NA"
field. What I'd like to do is click a "vNO" option button
that would result in the subform now showing only the 12
records which are blank in the "NA" field. Then, when I
click the "vYes" option button, the original 16 records
(which were the result of macro #12) would show in the
subform again.

I suspect this may not be possible, though. But again,
thank you very much for taking the time to repond. If
there is no other solution, and I indeed have to create
238 macros in place of the 78, then I will certainly use
your very handy suggestions.

Many thanks,
Paul
 
Back
Top