Filtering subform records (datasheet view) after they're already filtered from parent

[R. Choate]

I have several comboboxes on my parent form, and I have a subform with textboxes set for datasheet view. I first filter the records
(from a table) using docmd.ApplyFilter from the afterupdate event of any one of the combos in the parent form. I need to further
filter the records, either by using (preferably) more of the combos on the parent, or by doing something with the subform. I don't
want the user to have to use the icon for filtering by selection. I don't even want them to see that little thing. I'm stumped and
have tinkered for hours. I always end up either having one filter replace the first filter, giving me a different dataset, or if I
turn the controls on the subform into combos, I end up with syntax errors and all kinds of stuff. I've got about 12 fields/controls
which are candidates for potentially further filtering the already filtered records, so I need a good solution that I can use
without, hopefully, resetting properties back and forth for every control, via code, whenever a user needs to select an entry in one
of the fields to further filter and reduce the recordset. How can I accomplish this?

Thanks in advance for your help!

 

[Allen Browne]

Presumably you have *unbound* combos for filtering the form, i.e. distinct
from any of the controls where you enter data into the form.

Add a pair of command buttons beside these unbound combo, for Filter and
Remove Filter. In the Click event of your cmdFilter button, build up a
string from the non-blank combos, and assign it to the form's Filter
property. The string needs to look just like the WHERE clause of a query.

This example shows how to build up a string from any number of combos. It
adds the " AND " at the end of every one, and the trailing " AND " is
chopped off at the end. Note that Text fields need quotes as delimiters,
date fields need # as delimiters, and number fields need no delimiter. The
example therefore shows how to handle each type.

If you are filtering both the main form and the subform, Access is likely to
get confused. Details in:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

Hope that helps.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save first.

If Not IsNull(Me.[Combo1]) Then
strWhere = strWhere & "([MyNumberField] = " & Me.[Combo1] & ") AND "
End If

If Not IsNull(Me.[Combo2]) Then
strWhere = strWhere & "([MyTextField] = """ & Me.[Combo2] & """) AND
"
End If

If Not IsNull(Me.[Combo3]) Then
strWhere = strWhere & "([MyDateField] = " & Format([Combo3],
"\#mm\/dd\/yyy\#") & " AND "
End If

'etc for other combos.

lngLen = Len(strWhere) - 5 'Without training " AND ".
If lngLen <= 0 Then
MsgBox "Enter some criteria"
Else
strWhere = Left(strWhere, lngLen)
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdRemoveFilter_Click()
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.FilterOn = False
End Sub

 

[R. Choate]

Thank you, Allen. This all makes good sense and I'm sure it will take care of my problem. This form is not used for data entry, only
viewing and editing existing data. I don't know if that would impact your suggestion at all, but I thought I should mention it. I'll
get busy trying this and will reply if I run into any snags.

Richard
--
RMC,CPA


Presumably you have *unbound* combos for filtering the form, i.e. distinct
from any of the controls where you enter data into the form.

Add a pair of command buttons beside these unbound combo, for Filter and
Remove Filter. In the Click event of your cmdFilter button, build up a
string from the non-blank combos, and assign it to the form's Filter
property. The string needs to look just like the WHERE clause of a query.

This example shows how to build up a string from any number of combos. It
adds the " AND " at the end of every one, and the trailing " AND " is
chopped off at the end. Note that Text fields need quotes as delimiters,
date fields need # as delimiters, and number fields need no delimiter. The
example therefore shows how to handle each type.

If you are filtering both the main form and the subform, Access is likely to
get confused. Details in:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

Hope that helps.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save first.

If Not IsNull(Me.[Combo1]) Then
strWhere = strWhere & "([MyNumberField] = " & Me.[Combo1] & ") AND "
End If

If Not IsNull(Me.[Combo2]) Then
strWhere = strWhere & "([MyTextField] = """ & Me.[Combo2] & """) AND
"
End If

If Not IsNull(Me.[Combo3]) Then
strWhere = strWhere & "([MyDateField] = " & Format([Combo3],
"\#mm\/dd\/yyy\#") & " AND "
End If

'etc for other combos.

lngLen = Len(strWhere) - 5 'Without training " AND ".
If lngLen <= 0 Then
MsgBox "Enter some criteria"
Else
strWhere = Left(strWhere, lngLen)
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdRemoveFilter_Click()
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.FilterOn = False
End Sub

 

[R. Choate]

Addendum:

My main form is only there to define the filter for the recordset to be shown on the subform. The Parent serves no "record viewing"
purpose at all in itself. It only has combos and the 2 cmdButtons you told me to add. I just need to be able to define, and then
continue to further define (pare down) the records shown in the subform.

Thanks again.

Richard
--
RMC,CPA


Presumably you have *unbound* combos for filtering the form, i.e. distinct
from any of the controls where you enter data into the form.

Add a pair of command buttons beside these unbound combo, for Filter and
Remove Filter. In the Click event of your cmdFilter button, build up a
string from the non-blank combos, and assign it to the form's Filter
property. The string needs to look just like the WHERE clause of a query.

This example shows how to build up a string from any number of combos. It
adds the " AND " at the end of every one, and the trailing " AND " is
chopped off at the end. Note that Text fields need quotes as delimiters,
date fields need # as delimiters, and number fields need no delimiter. The
example therefore shows how to handle each type.

If you are filtering both the main form and the subform, Access is likely to
get confused. Details in:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

Hope that helps.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save first.

If Not IsNull(Me.[Combo1]) Then
strWhere = strWhere & "([MyNumberField] = " & Me.[Combo1] & ") AND "
End If

If Not IsNull(Me.[Combo2]) Then
strWhere = strWhere & "([MyTextField] = """ & Me.[Combo2] & """) AND
"
End If

If Not IsNull(Me.[Combo3]) Then
strWhere = strWhere & "([MyDateField] = " & Format([Combo3],
"\#mm\/dd\/yyy\#") & " AND "
End If

'etc for other combos.

lngLen = Len(strWhere) - 5 'Without training " AND ".
If lngLen <= 0 Then
MsgBox "Enter some criteria"
Else
strWhere = Left(strWhere, lngLen)
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdRemoveFilter_Click()
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.FilterOn = False
End Sub