And what's in strFilter when this happens?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message Ok I ran the code and when I first opened the form and tried to select
a
month it gave me an error and pointed to this part of the code:
Me.Filter = strFilter
It didn't even filter to the month I selected. It just gave me an
error.
:
Okay, let's step back and not be so fancy for a moment.
Private Sub cboMonthSelect_AfterUpdate()
' Find the record that matches the control.
Dim strFilter As String
If IsNull(Me!cboDivisions) = False Then
strFilter = "[FPRODCL] = " & Me!cboDivisions & " AND "
End If
strFilter = strFilter & "[MONTHCOUNT] = '" & Me!cboMonthSelect & "'"
Me.Filter = strFilter
Me.FilterOn = True
End Sub
If that still fails, put a break point inside the routine and
single-step
through it, checking to see which lines are executing.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message Actually I stand corrected. When I open the form and there is no
value
in
either combo box and then I select a month from the cboMonthSelect
box
it
errors out. The only way it will work when I first open the form is
to
make a
selection in the cboDivisions box and then choose a month it works
fine.
But
then I have to go back and clear the value from the cboDivisions box
to
have
it list all divisions for that month.
:
Damn. Another typo on my part. Left off an opening quote:
Me.Filter = ("[FPRODCL] = " + Me!cboDivisions + " AND ") _
& "[MONTHCOUNT] = '" & Me!cboMonthSelect & "'"
Sorry about that!
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
in
message I'm now getting a compile error (expected expression). It's
highlighting
the
' after the [MONTHCOUNT] parameter.
:
Sorry, typo on my part. One too many open parentheses:
Me.Filter = ("[FPRODCL] = " + Me!cboDivisions + " AND ") _
& [MONTHCOUNT] = '" & Me!cboMonthSelect & "'"
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"Secret Squirrel" <
[email protected]>
wrote
in
message
I tried that but the code stays red in the VB window. Doesn't
seem
to
want
to
accept the code as valid.
:
You could also try:
Me.Filter = ("([FPRODCL] = " + Me!cboDivisions + " AND ") _
& [MONTHCOUNT] = '" & Me!cboMonthSelect & "'"
This takes advantage of the fact that + propagates Nulls,
while
&
doesn't.
If nothing's selected in cboDivisions (so that it's Null),
the
result
of
("([FPRODCL] = " + Me!cboDivisions + " AND ") will be Null.
However,
Null
&
[MONTHCOUNT] = '" & Me!cboMonthSelect & "'" will continue to
populate.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Try this (assumes that cboDivisions will be Null if no
selection
has
been
made):
Me.Filter = "([FPRODCL] = " & Me!cboDivisions & _
" OR " & IsNull(Me!cboDivisions) & ")" & _
" AND [MONTHCOUNT] = '" & Me!cboMonthSelect & "'"
--
Ken Snell
<MS ACCESS MVP>
"Secret Squirrel"
<
[email protected]>
wrote
in
message
I figured out where to put the code.
What if I now wanted to only have it filter the month and
not
the
division?
I only want to use the division as an option not as a
requirement.
This
way
users can select the month and have it show all the
records
and
then
only
use
the divisions as an option. Basically they would just
leave
the
division
combo box blank or I can add an "All" choice to the list.
:
Me.Filter = "[FPRODCL] = " & Me!cboDivisions & _
" AND [MONTHCOUNT] = '" & Me!cboMonthSelect & "'"
Me.FilterOn = True
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"Secret Squirrel"
<
[email protected]>
wrote
in
message
I have 2 combo boxes on my form header that I want to
use
as
filters.
They
both work but not together. I want to be able to filter
by
month
(cboMonthSelect) and then filter those results by
division
(cboDivisions).
Right now this how I have it set up:
Private Sub cboDivisions_AfterUpdate()
Me.Filter = "[FPRODCL] = " & Me!cboDivisions
Me.FilterOn = True
End Sub
Private Sub cboMonthSelect_AfterUpdate()
' Find the record that matches the control.
'Dim rs As Object
'Set rs = Me.Recordset.Clone
'rs.FindFirst "[MONTHCOUNT] = '" &
Me![cboMonthSelect]
&
"'"
'If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Filter = "[MONTHCOUNT] = '" & Me!cboMonthSelect &
"'"
Me.FilterOn = True
End Sub
When I filter by one and then the other it cancels out
the
first
one.
How
can I get these to work together?
