Apply Filter to List Box

[psandel]

I have a form with data fields and a list box, data is coming from a query. When I add a toggle button to apply a filter to the data on the form, the data in the fields are filtered, but the list box still shows all the data items. How do I use a toggle button or something on the form that when activated it filters the data in the list box and the list box only shows the filtered content.

 

[pbaldy]

You can change its row source, like the second method here:

http://www.baldyweb.com/CascadingCombo.htm

 

[psandel]

Not sure how to make that work with a button to apply a filter and one list box. List 195 is what I need filtered when Toggle 133.

I have;

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Detail_Click()

End Sub
'------------------------------------------------------------
' Toggle92_Click
'
'------------------------------------------------------------
Private Sub Toggle92_Click()
On Error GoTo Toggle92_Click_Err

DoCmd.RunCommand acCmdRemoveFilterSort
DoCmd.RunCommand acCmdRefresh


Toggle92_Click_Exit:
Exit Sub

Toggle92_Click_Err:
MsgBox Error$
Resume Toggle92_Click_Exit

End Sub


'------------------------------------------------------------
' Toggle133_Click
'
'------------------------------------------------------------
Private Sub Toggle133_Click()
On Error GoTo Toggle133_Click_Err

DoCmd.ApplyFilter "", "[All Comp]![Part Number] Like ""*-05*""", ""

Toggle133_Click_Exit:
Exit Sub

Toggle133_Click_Err:
MsgBox Error$
Resume Toggle133_Click_Exit

End Sub


'------------------------------------------------------------
' List195_AfterUpdate
'
'------------------------------------------------------------
Private Sub List195_AfterUpdate()
On Error GoTo List195_AfterUpdate_Err

DoCmd.SearchForRecord , "", acFirst, "[Part Number] = " & "'" & Screen.ActiveControl & "'"

List195_AfterUpdate_Exit:
Exit Sub

List195_AfterUpdate_Err:
MsgBox Error$
Resume List195_AfterUpdate_Exit

End Sub

 

[pbaldy]

You'd set its row source, like in the link. Along the lines of

Dim strSource As String

strSource = "SELECT Whatever " & _
            "FROM TableName " & _
            "WHERE [All Comp]![Part Number] Like '*-05*'"
Me.List195.RowSource = strSource
Me.List195= vbNullString

 

[psandel]

The code worked, but when applied, it filters the list box but blanks out all the columns except the first column. This allows me to only see the first column. Is there a way to change the code so all columns of the list box are shown.

 

[psandel]

See code below;

Private Sub Toggle198_Click()
On Error GoTo Toggle198_Click_Err

DoCmd.ApplyFilter "", "[All Comp]![Part Number] Like ""*-05*""", ""

strSource = "SELECT [Part Number] " & _
"From [All Comp] " & _
"WHERE [All Comp]![Part Number] Like '*-05*'"
Me.List195.RowSource = strSource
Me.List195 = vbNullString


Toggle198_Click_Exit:
Exit Sub

Toggle198_Click_Err:
MsgBox Error$
Resume Toggle198_Click_Exit

End Sub

 

[pbaldy]

You'd have to list the fields you want displayed, in the appropriate order. What is the row source when the form is first opened?

 

[psandel]

The row source for the form and list box is a relation query combining two tables. The code for the row source for the list box is;

SELECT [99-05 Comp].[Part Number], [99-05 Comp].Description, [99-05 Comp].Supplier, [99-05 Comp].Cost, [99-05 Comp].By, Suppliers.Approved, Suppliers.[Rating Score], Suppliers.[Final Rating] FROM [99-05 Comp] INNER JOIN Suppliers ON [99-05 Comp].Supplier = Suppliers.Supplier;

 

[pbaldy]

So I would assume you'd want to duplicate that in the code, but add the WHERE clause.

 

[psandel]

One would assume logically that would work, seemed to obvious once stated. Revised the code to;

Private Sub Toggle9905_Click()

On Error GoTo Toggle9905_Click_Err

DoCmd.ApplyFilter "", "[All Comp]![Part Number] Like ""*-05*""", ""

strSource = "SELECT [All Comp].Part Number, [All Comp].Description, [All Comp].Supplier, [All Comp].Cost, [All Comp].By, Suppliers.Approved, Suppliers.[Rating Score], Suppliers.[Final Rating] FROM [All Comp] INNER JOIN Suppliers ON [All Comp].Supplier = Suppliers.Supplier" & _
"From [All Comp] " & _
"WHERE [All Comp]![Part Number] Like '*-05*'"
Me.List182.RowSource = strSource
Me.List182 = vbNullString
Toggle9905_Click_Err:
MsgBox Error$
Resume Toggle9905_Click_Exit

End Sub
Before Toggle
All data shows in the list, 2154 items

After Toggle
Data reduced to 103 items, but still blanks out all the fields in the list box

 

[pbaldy]

I'm surprised it works at all; you have 2 FROM clauses, and potentially a space problem between lines. Try

strSource = "SELECT [All Comp].Part Number, [All Comp].Description, [All Comp].Supplier, [All Comp].Cost, [All Comp].By, Suppliers.Approved, Suppliers.[Rating Score], Suppliers.[Final Rating] FROM [All Comp] INNER JOIN Suppliers ON [All Comp].Supplier = Suppliers.Supplier " & _
"WHERE [All Comp]![Part Number] Like '*-05*'"

 

[psandel]

The line space issue was more from cut and paste. Used the following code, but list box still blanks out.

Private Sub Toggle9905_Click()
On Error GoTo Toggle9905_Click_Err

strSource = "SELECT [All Comp].Part Number, [All Comp].Description, [All Comp].Supplier, [All Comp].Cost, [All Comp].By, Suppliers.Approved, Suppliers.[Rating Score], Suppliers.[Final Rating] FROM [All Comp] INNER JOIN Suppliers ON [All Comp].Supplier = Suppliers.Supplier " & _
"WHERE [All Comp]![Part Number] Like '*-05*'"
Me.List182.RowSource = strSource
Me.List182 = vbNullString


Toggle9905_Click_Exit:
Exit Sub

Toggle9905_Click_Err:
MsgBox Error$
Resume Toggle9905_Click_Exit
End Sub

 

[pbaldy]

Can you attach the db here?

 

[psandel]

Attached is an abbreviated version to keep the file size small.

 

[pbaldy]

I missed that you didn't bracket Part Number in the SELECT clause. That should fix it. You'll find in the long run that the spaces and symbols in your names aren't worth the bother.

 

[psandel]

That fixed it, works GREAT. Thanks for all your help.

 

[pbaldy]

Happy to help and welcome to the site by the way!