Filter subform somehow!

[alex]

Hello all,

Using Access ’03…

I have a form with three unbound combo boxes. Under them is a subform
based on a query.

I’m trying to filter the subform based on the value of a combo box or
combination of combo boxes. I cannot (I think) set the child/master
fields of the subform to a combo box, because it would be too
restrictive (if I had only one combo box this would work).

So, e.g.; the form opens and the user sees three combo boxes and the
subform in datasheet view with about 5000 records.

The user can select a value in the first combo box (which is ByPerson)
to limit the results to a particular person. The user can then select
a value from the second combo box (which is ByCase) to limit even
further, etc... The combo boxes, however, should run independent of
each other.

I thought I found some code in this group to apply and manipulate a
filter with the combo box value. I could only get one of them to work
and for the life of me; I could not programmatically clear the filter
with VBA. I tried everything:
Me.filter = “”
Me.filter = “’’”
Docmd…
Nothing worked! The filter never cleared from the properties.

Any suggestions would be appreciated.
alex

 

[Steve Schapel]

Alex,

I suppose the resetting of the filter may be because it is the Filter
property of the subform, but you are calling it from the main form. So
instead of Me.Filter it should be Me.NameOfSubform.Form.Filter ... just
guessing.

Anyway, here's some skeleton code for how I would do it:

Private Sub FilterSubform()
Dim strSQL As String
strSQL = "SELECT * FROM YourSubformsTable WHERE True"
If IsNull(Me.ByPerson) Then
' carry on
Else
strSQL = strSQL & " AND PersonID = " & Me.ByPerson
End If
If IsNull(Me.ByCase) Then
' carry on
Else
strSQL = strSQL & " AND CaseNo = " & Me.ByCase
End If
If IsNull(Me.3rdCombo) Then
' carry on
Else
' maybe this is a text and the others are numbers
strSQL = strSQL & " AND OtherField = '" & Me.3rdCombo & "'"
End If
Me.NameOfSubform.Form.RecordSource = strSQL
End Sub

And then just put like this:
Call FilterSubform
.... on the After Update event of each of the comboboxes.

 

[Steve Schapel]

Alex,

Just be aware that if you use the (perfectly fine) approach suggested by
Ken, when you go back to the design view of the query, you will see that
Access has re-organised the criteria to suit its own purposes, and it will
look different. Don't worry about this.

 

[alex]

Alex,

Just be aware that if you use the (perfectly fine) approach suggested by
Ken, when you go back to the design view of the query, you will see that
Access has re-organised the criteria to suit its own purposes, and it will
look different.  Don't worry about this.

--
Steve Schapel, Microsoft Access MVP










- Show quoted text -

Thanks guys for your help. I'll see what I can do with your much
appreciated advice.
alex

 

[kate]

Alex,

I suppose the resetting of the filter may be because it is the Filter
property of the subform, but you are calling it from the main form. So
instead of Me.Filter it should be Me.NameOfSubform.Form.Filter ... just
guessing.

Anyway, here's some skeleton code for how I would do it:

Private Sub FilterSubform()
Dim strSQL As String
strSQL = "SELECT * FROM YourSubformsTable WHERE True"
If IsNull(Me.ByPerson) Then
' carry on
Else
strSQL = strSQL & " AND PersonID = " & Me.ByPerson
End If
If IsNull(Me.ByCase) Then
' carry on
Else
strSQL = strSQL & " AND CaseNo = " & Me.ByCase
End If
If IsNull(Me.3rdCombo) Then
' carry on
Else
' maybe this is a text and the others are numbers
strSQL = strSQL & " AND OtherField = '" & Me.3rdCombo & "'"
End If
Me.NameOfSubform.Form.RecordSource = strSQL
End Sub

And then just put like this:
Call FilterSubform
... on the After Update event of each of the comboboxes.

 

[kate]

Alex,

Just be aware that if you use the (perfectly fine) approach suggested by
Ken, when you go back to the design view of the query, you will see that
Access has re-organised the criteria to suit its own purposes, and it will
look different. Don't worry about this.

 

[kate]

Alex:

You should be able to do it by basing the subform on a query which
references
the three combo boxes as parameters, testing each parameter independently
for
a value or for Null:

SELECT *
FROM YourTable
WHERE (PersonID = Forms!YourForm!cboByPerson
OR Forms!YourForm!cboByPerson IS NULL)
AND (CaseID = Forms!YourForm!cboByCase
OR Forms!YourForm!cboByCase IS NULL)
AND (WhateverID = Forms!YourForm!cboByWhatever
OR Forms!YourForm!cboByWhatever IS NULL);

Then in each of the three combo boxes requery the subform with:

Me.YourSubformControl.Requery

Each of the parenthesised Boolean OR operations will evaluate
independently,
so if a value is selected in cboByPerson only, then the first expression
will
evaluate to TRUE for those rows where the value of PersonID equals the
selected value. Each of the other tow expressions will evaluate to True
for
all rows by virtue of the combo boxes being Null. So any row where the
value
of PersonID equals the selected value will be returned irrespective of the
values in the CaseID and WhateverID columns.

If a value is also selected in cboCaseID then the expression will evaluate
to
true for those rows where the value of CaseID equals the selected value.
Consequently, by virtue of the Boolean AND operation, only rows where both
expressions evaluate to True will be returned, i.e. the result will be
narrowed down by case in addition to the restriction by person.

So, any permutation of selections or non-selections (Nulls) in the three
combo boxes can be applied to restrict the results.

Ken Sheridan
Stafford, England