2 cboBox criteria in filter

[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.

 

[Brian]

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

 

[Guest]

May I use query instead of SQL?
Since query may be very complex SQL to maitain.
If the SQL comes very complex then it will be hard to maintiian the code.

 

[Brian]

May I use query instead of SQL?
Since query may be very complex SQL to maitain.
If the SQL comes very complex then it will be hard to maintiian the code.

My personal opinion is that having objects e.g. queries proliferating
unnecessarily throughout the database is the real maintenance headache. I
would far rather figure out what's going on by understanding one VBA
procedure than by having to go off here and there finding and understanding
parametrized queries. The setup you are looking for, where the recordsource
of form 2 is a parameter query object which gets it it's parameters from
another form, form 1, and which then has filters being applied and removed
according to user actions, strikes me as very convoluted and hard to
maintain. Personally I wouldn't even have form 2 get parameter values from
form 1: assuming that form 2 is being opened from form 1, I would pass the
parameter values in the OpenArgs argument, and use them in form 2's Open
event to set the recordsource appropriately. It's all about avoiding
complex networks of objects all referencing each other, which can become
very hard to maintain.

There is an argument that using query objects is more efficient than using
dynamic queries, but, in my opinion, so long as performance is good enough
(which it usually is) maintainability is more important than optimal
efficiency.