I misunderstood your post. I thought you wanted to *always* use
both criteria. If you want to use the option groups to selectively
filter the recordset, then you will need to remove those form
references from the query (you will still need the ProjectMonth
field though).Then you could do something like the following.
First, add another option button to each group with a description of
"All" and a value of 0 (make this the default value of the option group).
Then you would put code like the following in the After Update event
of each group.
Dim strFilter As String
Dim lngLen As Long
strFilter = ""
If Me.ogpProjectNumber > 0 Then
strFilter = strFilter & "([ProjectNumber]=" & Me.ogpProjectNumber & ")
And "
End If
If Me.ogpProjectMonth > 0 Then
strFilter = strFilter & "([ProjectMonth]=" & Me.ogpProjectMonth & ") And "
End If
lngLen = Len(strFilter) - 5 ' trim off the trailing And
If lngLen <= 0 Then 'show all records
Me.FilterOn = False
Else
strFilter = Left(strFilter, lngLen)
Me.Filter = strFilter
Me.FilterOn = True
End If
The above code assumes that ProjectNumber is a number data type.
If it is text you will need additional quote delimiters. Also, you may
want to to put the following line in the forms Open event to make
sure it shows all records when it is first opened.
Me.Filter = False
--
_________
Sean Bailey
Ann said:
Hi, That didn't work. Neither option group works without the other one.
Since both fields are in the same query it wants criteria for both. Any
other suggetions?
Also, that was the first time I used an option group that way and I really
liked it. I can see where I can use that in the future so thanks for
teaching me something new.
Beetle said:
I would use the same method as described previously and add
criteria in the query for the PlanNumber also, so your query
would have two fields with criteria;
PlanNumber
Forms!frmAllPlans!ogpPlanNumber
PlanMonth: Month([PlanDate])
Forms!frmAllPlans!ogpPlanMonth
Then the only code you would need is a Requery in the After Update
event of each option group.
--
_________
Sean Bailey
Ann said:
Thanks, that did work the way I wanted it to but it caused my original option
group for all the plans to no longer show me all the records. When I clicked
Plan One a filter was applied to only pull Plan One records for the year...a
total of 45. Now I don't see any. I can only see plan/month records after I
click the second option group for the month
The first option group is by plan, on a click event and runs an apply filter
action using the forms query qryAllPlans with a where condition of the plan
name. Any ideas how I can get them back?
:
I have one table called tblAllPlans (5000 records for 8 different plans).
.....the data is the same for all of the records for all of the plans.
Sounds like your data is not properly normalized and that the Plan
infor should be in a separate table.
However, to answer your question, you can add another column to
your query like;
PlanMonth: Month([PlanDate])
and in the criteria row of this field use a reference to the option
group on the form;
Forms!frmAllPlans!ogpMonth
Then you just need to requery the form in the After Update event of
the option group (assuming the options in your option group are 1
through 12).
--
_________
Sean Bailey
:
I have one table called tblAllPlans (5000 records for 8 different plans). I
have one form (frmAllPlans) that all of the plans can use since the data is
the same for all of the records for all of the plans. I also have one query
(qryAllPlans). I want to stay on the same form once it's opened and not flip
between 8 different plan forms. To do that I had an option group called
optPlan but now I've been asked to filter that data again by month.
The first filter will give me a recordset of the plan I want depending on
the value of the option clicked. For an example I'll use Plan One. So, if I
click option button value 1 I get 21 records all for Plan One from a total of
5000 records. Now I want to filter those 21 records by months using another
option group called optMonth. So, if I click April I only get April's data
out of the total 21 records.
How do I let the second option group (Month) know that it should be using
the recordset filtered from the first option group (Plan)? Thanks in advance
for the help.
