filtering down data....How low can you GO!

[paulmcdonnell]

Hi Guys,

What I need to do is filter a form by as many criteria as is required by the user. Ie I have 10 separate fields and I want to beable to set up a filtering process which filters by one field, two fields, three fields, upto 10 fields if required. Slowly filtering the data down adding condition after condition until in theory there are no records left.

I know how to do this with a query in basic terms but what is the best way to allow this when viewing forms. Can you lay sucessive filters ontop of each other if so, how do you set the code to say "filter using recordsource as result of previous filter"

Getting a bit confused with this one, but I'm sure that many people have done this before, say for example : filter by all those who live in london, then filtering those in london who work at a company, then filtering those in london who work at a company by which building they work in and so on.......

obvious by setting conditions in saved queries but how by user selection??

Bit long winded I know but any ideas..


THANKS GUYS

PAUL

 

[llkhoutx]

dynamically buuild you record/row source based up selected criteria.\

Here is an example of dynamically building a listbox rowsource:

Me!lstMMCustomerAccounts.RowSource = "SELECT tbOmniAccountInfo.UniqueAcctID AS AcctID, " & _
"tbOmniAccountInfo.AccountID, tbOmniAccountInfo.bytVersion AS Version, " & _
"tbOmniAccountInfo.AccountName, tbOmniAccountInfo.ynValidated AS Valid, " & _
"tbOmniAccountInfo.AcctRecNum, tbOmniAccountInfo.CustomerID " & _
"FROM tbOmniAccountInfo " & _
"WHERE (((tbOmniAccountInfo.CustomerID)= " & _
"[Forms]![hfrmMMParameters]![cboCustomerID]) " & _
"AND ((tbOmniAccountInfo.bytAcctType)=" & 1 & ")) " & _
"ORDER BY tbOmniAccountInfo.UniqueAcctID, " & _
"tbOmniAccountInfo.bytVersion DESC;"

What you want to do is to programmatically create a WWERE clause based upon form criteria.