Filter records in a subform based on list box selection

[Julia]

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

 

[Jeanette Cunningham]

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

 

[Jeanette Cunningham]

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

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

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

 

[Julia]

Hi Jeannette,

I tried to use your code but there was an error and the debugger came back
with this section highlighted:
Me.[SubformControlName].FilterOn = True

Since it took more than 24 hours I actually tried a different approach,
which is explained here:
http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html
It's done by modifying the criteria in the query view of the subform's
record source to filter records based on the list box selection. I used the
phrase "Like "*" & [forms]![frmMyForm].lstProduct" as the criteria and the
afterupdate event in the list box requeries the subform.

It works quite well except where the primary key is a single digit for a
product selected, it returns all products with that digit contained somewhere
in the primary key. I understand that the asterisk is there so that if
nothing is selected, all records are displayed, and I would be OK with
removing that, but it is not so practical because I have two list boxes. I
can't seem to work out how to get it to only look for exactly the number of
digits in the primary key. It would be fiddly if I had to change those 2x9
primary keys (which are autonumber fields) to avoid the problem.

Julia

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

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

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

 

[John W. Vinson]

Hi Jeannette,

I tried to use your code but there was an error and the debugger came back
with this section highlighted:
Me.[SubformControlName].FilterOn = True

Just FYI that probably should have been

Me![SubformControlName].Form.FilterOn = True

A Form has a FilterOn property, a Subform Control doesn't.

 

[Julia]

Thanks, I thought I would have to modify the criteria in the subform record
source, and your expression did the trick. My subform is filtered
"dynamically" via the listbox without picking up the extra records.

I also wanted a way of removing the filter if necessary, so I have one
button under each of my listboxes with this expression in the onclick event:
Me.LstProduct.Value = Null
Me.MySubformControl.Requery

Julia