R
rgrantz
I saw several posts on this topic, but messing with the code in all the
examples given didn't work:
I have a form that opens showing data based on textboxes (dates) on the
PREVIOUS form set as the parameters for the recordsource query (2nd form's
recordsource has crieria using first form's user-entered unbound field
values). On the second form, I want the user to be able to filter the
results based on the combination of 2 (Non-Date, but Number) comboboxes on
the 2ND form. Each combobox has a button next to it that I'm trying to use
as "show all" for THAT specific combobox, but still using the criteria
(filter) from the OTHER combobox.
In the AfterUpdate of each cboBox, I'd like the form (which is based on a
query, already showing only results based on earlier form's Date values) to
show results for what's chosen in BOTH comboboxes at that time. Likewise,
when "show all" button next to THAT combobox is chosen, form shows records
for ALL on THAT cboBox, but still using criteria on from OTHER cbBox.
Example:
Assume that on Form_Open, both cboBoxes have no criteria, and so all records
based on recordsource are shown. THEN, however, the user gets fancy:
User chooses "1" from cboBox1:
- form shows all records from recordsource where Field1 = Me.cboBox1 (which
= 1) AND Field2 = All (because nothing's been specified in cboBox2)
User THEN chooses "2" from cboBox2:
- form shows all records from recordsource where Field1 = Me.cboBox1 (which
right now = 1) AND Field2 = Me.cboBox2 = 2.
BUT, if user then clicks button next to cboBox1 (which shows ALL, or Removes
filter for this particular field), then form shows records where Field1 =
ALL (no filter) and Field2 = Me.cboBox2.
Same deal for if they click "Show All" next to cboBox2.
You can see where I'm going with this. User can filter using cboBox1 AND
cboBox2, each having a button next to them which "Shows All" for THAT
cboBox. Meaning, the "Show All" button's not a complete removal of the
filter of the form, but rather the removal of THAT cboBox's filter, while
still showing records depending on the OTHER cboBox's filter. OR, the
AfterUpdate event of each cboBox shows the COMBINED filter of BOTH cboBoxes.
Both cbBox's have a button next to them which sets THAT's cboBox filter to
"All", while still using filter from the other. Likewise, if a criteria is
chosen from both (or either), form shows THOSE results. Likewise, if the
"Show ALL" button is clicked for cbBox1, and then the "Show ALL" button is
clicked next to cboBox2, then records show ALL for both.
So:
- - - - - - - - -
cboBox1 = 1
cboBox2 = 2
Form shows all records where Field1 = 1 AND Field2 = 2
- - - - - - -
cboBox1 = ALL (button next to cboBox1 is clicked, which removes criteria and
shows ALL for Field1)
cboBox2 = 3 ("3" chosen from list)
Form shows records where Field1 = Anything (no criteria) AND Field2 = 3
- - - - - - - - - -
cboBox1 = ALL (button next to cboBox1 is clicked, which removes criteria and
shows ALL for Field1)
cboBox2 = ALL (button next to cboBox2 is clicked, which removes criteria and
shows ALL for Field2)
Form shows ALL records, period.
- - - - - - - - - - -
cboBox1 = 2
cboBox2 = 3
Form shows records where Field1 = 2 AND Field2 = 3
So, you see, in the AfterUpdate event of EACH cboBox, I'd like to apply a
filter which takes into account each cboBox's value, whether they be set by
choosing a value from their own recordsource, or whether one (or the other,
or both) has had "show all" clicked.
Is there a way to do this? I saw an example in the posts that seemed
applicable to my situation, but replacing field names and control names
didn't work. I thought at first it was because the fields in the example
were text rather than numbers, but removing the extra " and ' still didn't
make it work right.
Thanks for any help on this, it would be greatly appreciated. I apologize
if I'm overexplaining, but I see a lot of posts in which there's a lot of
back-and-forth regarding what the original poster's looking for, and so I
try to detail it out specifically in my first post. Which, ironically,
makes people less likely to read through the whole thing. So, if you've
made it this far, kudos to you, and thanks again.
examples given didn't work:
I have a form that opens showing data based on textboxes (dates) on the
PREVIOUS form set as the parameters for the recordsource query (2nd form's
recordsource has crieria using first form's user-entered unbound field
values). On the second form, I want the user to be able to filter the
results based on the combination of 2 (Non-Date, but Number) comboboxes on
the 2ND form. Each combobox has a button next to it that I'm trying to use
as "show all" for THAT specific combobox, but still using the criteria
(filter) from the OTHER combobox.
In the AfterUpdate of each cboBox, I'd like the form (which is based on a
query, already showing only results based on earlier form's Date values) to
show results for what's chosen in BOTH comboboxes at that time. Likewise,
when "show all" button next to THAT combobox is chosen, form shows records
for ALL on THAT cboBox, but still using criteria on from OTHER cbBox.
Example:
Assume that on Form_Open, both cboBoxes have no criteria, and so all records
based on recordsource are shown. THEN, however, the user gets fancy:
User chooses "1" from cboBox1:
- form shows all records from recordsource where Field1 = Me.cboBox1 (which
= 1) AND Field2 = All (because nothing's been specified in cboBox2)
User THEN chooses "2" from cboBox2:
- form shows all records from recordsource where Field1 = Me.cboBox1 (which
right now = 1) AND Field2 = Me.cboBox2 = 2.
BUT, if user then clicks button next to cboBox1 (which shows ALL, or Removes
filter for this particular field), then form shows records where Field1 =
ALL (no filter) and Field2 = Me.cboBox2.
Same deal for if they click "Show All" next to cboBox2.
You can see where I'm going with this. User can filter using cboBox1 AND
cboBox2, each having a button next to them which "Shows All" for THAT
cboBox. Meaning, the "Show All" button's not a complete removal of the
filter of the form, but rather the removal of THAT cboBox's filter, while
still showing records depending on the OTHER cboBox's filter. OR, the
AfterUpdate event of each cboBox shows the COMBINED filter of BOTH cboBoxes.
Both cbBox's have a button next to them which sets THAT's cboBox filter to
"All", while still using filter from the other. Likewise, if a criteria is
chosen from both (or either), form shows THOSE results. Likewise, if the
"Show ALL" button is clicked for cbBox1, and then the "Show ALL" button is
clicked next to cboBox2, then records show ALL for both.
So:
- - - - - - - - -
cboBox1 = 1
cboBox2 = 2
Form shows all records where Field1 = 1 AND Field2 = 2
- - - - - - -
cboBox1 = ALL (button next to cboBox1 is clicked, which removes criteria and
shows ALL for Field1)
cboBox2 = 3 ("3" chosen from list)
Form shows records where Field1 = Anything (no criteria) AND Field2 = 3
- - - - - - - - - -
cboBox1 = ALL (button next to cboBox1 is clicked, which removes criteria and
shows ALL for Field1)
cboBox2 = ALL (button next to cboBox2 is clicked, which removes criteria and
shows ALL for Field2)
Form shows ALL records, period.
- - - - - - - - - - -
cboBox1 = 2
cboBox2 = 3
Form shows records where Field1 = 2 AND Field2 = 3
So, you see, in the AfterUpdate event of EACH cboBox, I'd like to apply a
filter which takes into account each cboBox's value, whether they be set by
choosing a value from their own recordsource, or whether one (or the other,
or both) has had "show all" clicked.
Is there a way to do this? I saw an example in the posts that seemed
applicable to my situation, but replacing field names and control names
didn't work. I thought at first it was because the fields in the example
were text rather than numbers, but removing the extra " and ' still didn't
make it work right.
Thanks for any help on this, it would be greatly appreciated. I apologize
if I'm overexplaining, but I see a lot of posts in which there's a lot of
back-and-forth regarding what the original poster's looking for, and so I
try to detail it out specifically in my first post. Which, ironically,
makes people less likely to read through the whole thing. So, if you've
made it this far, kudos to you, and thanks again.