Replace the previous post with this one.
I was using code for filtering a form, then changing it for a subform, but I
forgot to change it in all places.
something like the untested air code below-->
Private Sub FilterBtn_Click()
Dim strWhere As String
Dim lngLen As Long
If Len(Me.lstProduct & vbNullString) > 0 Then
strWhere = strWhere & "(ProductID = """ & Me.lstProduct & """) AND "
End If
If Len(Me.lstCustomer & vbNullString) > 0 Then
strWhere = strWhere & "(CustomerID = """ & Me.lstCustomer & """) AND "
End If
'Chop off the trailing " AND ", and use the string as the subform's
Filter.
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
'show no records.
Me.[SubformControlName].FilterOn = False
Else
strWhere = Left$(strWhere, lngLen)
'Debug.Print strWhere
'Finally, apply the string as the subform's Filter.
Me.[SubformControlName].Filter = strWhere
Me.[SubformControlName].FilterOn = True
End If
End Sub
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Jeanette Cunningham said:
Hi Julia
something like the untested air code below-->
Private Sub FilterBtn_Click()
Dim strWhere As String
Dim lngLen As Long
If Len(Me.lstProduct & vbNullString) > 0 Then
strWhere = strWhere & "(ProductID = """ & Me.lstProduct & """) AND "
End If
If Len(Me.lstCustomer & vbNullString) > 0 Then
strWhere = strWhere & "(CustomerID = """ & Me.lstCustomer & """) AND
"
End If
'Chop off the trailing " AND ", and use the string as the form's
Filter.
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
'show no records.
Me.FilterOn = False
Else
strWhere = Left$(strWhere, lngLen)
'Debug.Print strWhere
'Finally, apply the string as the subform's Filter.
Me.[SubformControlName].Filter = strWhere
Me.[SubformControlName].FilterOn = True
End If
End Sub
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Julia said:
Hi,
I can't seem to figure out how to filter subform records.
I have a form with two list boxes, one with a list of products and one
with
a list of names. I want users to be able to click on a product and/or
name in
the listbox and filter the records in the subform according to their
selection. Ideally the records would filter after selecting an item in
the
listbox (the onclick event), but I could also have the action perform
after
clicking on a separate command button.
I can't seem to figure out how to specify that the subform records should
be
filtered based on the listbox selection.
Can anyone help me here?
Julia