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.

 

[Steve]

Hello Julia,

You should have a products table that looks something like:
TblProduct
ProductID
ProductDesc
etc

and a table of names that looks something like:
TblPerson
PersonID
PersonFirstName
PersonLastName
etc

Your product listbox should have the value of ProductID and display
ProductDesc. Your person listbox should have the value of PersonID and
display PersonLastName, PersonFirstName. I will assume your product listbox
is named Product and your person listbox is named Person.

I will assum your main form is named MyMainForm and the subform control on
the main form is named MySubformControl.

Base the subform on a query that includes a ProductID field and a PersonID
field. Put the following expression in the criteria of the ProductID field:
Forms!MyMainForm!Product Or (Forms!MyMainForm!Product Is Null)
Put the following expression in the criteria of the PersonID field:
Forms!MyMainForm!Person Or (Forms!MyMainForm!Person Is Null)

Put the following code in the AfterUpdate event of the Product listbox:
Me!MyMainForm!MySubformControl

Put the following code in the AfterUpdate event of the Person listbox:
Me!MyMainForm!MySubformControl


To filter the subform, select a product or person in one of the listboxes
and the subform will display the records associated with the selections in
BOTH listboxes. You may need to make a selection in one listbox, let the
subform requery, make a selection in the other and let the subform requery
to get the pair of product and person you want. Also, note that if there is
no selection in either listbox, the subform will display all records for
that field. In other words, if you leave say the person listbox blank, you
will get the products you selected in the product listbox for ALL persons.

Steve
(e-mail address removed)

 

[Steve]

OOps .........

Put the following code in the AfterUpdate event of the Product listbox:
Me!MyMainForm!MySubformControl.Requery

Put the following code in the AfterUpdate event of the Person listbox:
Me!MyMainForm!MySubformControl.Requery

Steve

 

[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

 

[Steve]

Yes-ssss! Way to go.

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