Filter Recordset

[DMerchen]

I have found several posts concerning filtering a recordset, but I am still a little befuddled. I have a text box where a person can enter a keyword to narrow the items on a subform. This is done using a recordsource. My question is, can I then set a filter on that recordsource, and continue to filter based on keywords in a textbox narrowing the list each time I enter a keyword and hit a button to apply the filter.

So I want to take a list of 200+ items and continue to filter by entering a keyword and hitting a button, take the filtered list and filter again and again until I narrow it down to only a couple of items.

Private Sub Command91_Click()
Dim strsearch As String
Dim strText As String

If (Len(Me.TxtSearch) > 0) Then
strText = Me.TxtSearch.Value
End If

strsearch = "SELECT * from FABSubForm where ((PartNumber like ""*" & strText & "*"") or(Description like ""*" & strText & "*"") or(TYP like ""*" & strText & "*""))"
Me.RecordSource = strsearch
End Sub

I hope this makes sense, and thanks for the assistance!!!

 

[pbaldy]

The sample db here demonstrates an extension of your method:


http://baldyweb.com/BuildSQL.htm

 

[arnelgp]

of course you can, just test first if there is already a Where
clause on the recordsource:

Dim strSQL as String
strSQL = Me.Recordsource
If Instr(strSQL, "Where") Then
	'there is existing filter
	strSQL = StrSQL & " And ...." 'put additional filter
Else
	'there is no filter yet
	strSQL = Replace(strSQL, ";", "") & " Where ...." 'put filter here
End If
Me.Recourdsource=strSQL

 

[DMerchen]

Thanks for your responses. I am almost there in understanding this. arnelgp, is there a filter you set up to run in your code? Do you do a DoCmd.setfilter?
It looks like you can simply use this code to continually whittle down the list.
Can you run multiple where statements? Would it just be a modification to my code I posted?

Thanks! Trying to get there!

 

[arnelgp]

You are right, continually add filter to your recordsource. But you will need another button to reinstate the recordsource to clear the filters.

 

[DMerchen]

So with this method, I can use one textbox and one button and continue to filter down the list in a recordset?

Then I have another button to show all records again, correct?

Thanks!

 

[arnelgp]

Yes that is correct