Re: 2 cboBox criteria in filter

From: Brian (bcap_at_IHATESPAMclara.co.uk)
Date: 03/26/05 

Date: Sat, 26 Mar 2005 10:19:21 -0000

"rgrantz" <rgrantz@hotmail.com> wrote in message
news:3ak28aF6c2o2gU1@individual.net...
> 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.
>
>

Right, first thing: if you take my advice, you'll replace those "Show All"
buttons with "Show All" checkboxes or, if you prefer, toggle buttons. That
way, the user gets a visual reminder that he/she has chosen to "Show All",
and it's easier to create a generic filtering routine rather than having to
fiddle around with specific coding in each command button's Click event.

Then, something like the following procedure should apply the desired
filter (actually, it's not applying a filter, it's changing the form's
record source,
which I always find to be far preferable to messing around with Access
filters). Call it from wherever you feel is appropriate in Form2's events.
You will need to fill in the bit relating to Form1, because you haven't made
it clear how the date criteria from Form1 are actually applied. It would
have helped if you'd posted the actual SQL for the initial RecordSource!

Sub ApplyFilter()

    Dim strSQL As String

    strSQL = "SELECT * FROM some_table_or_other WHERE (some criteria
relating to Form1)"

    If Not chk1 And Not Nz(cboBox1) = "" Then
        strSQL = strSQL & " AND S.some_other_field1 = " & cboBox1
    End If
    If Not chk2 And Not Nz(cboBox2) = "" Then
        strSQL = strSQL & " AND S.some_other_field2 = " & cboBox2
    End If

    Me.RecordSource = strSQL

End Sub