How to apply a new query to an open recordset

[flash1]

I have created a search function that allows the user to select up to 4 different criteria to search the database. I have developed the sql statements. The statements use multiple joins between tables. The various search options make it too complex to apply the criteria using a single sql statement. Is it possible to use a recordset on the based on the first criteria and then apply the next criteria to the first recordset and so on until the initial recordset is filtered a max of 3 times.

 

[cmmrfrds]

Do you want to manipulate the recordset after bringing it back to a Form. In this case, you can put the recordset into an ADO recordset that has a client side cursor and use the ADO filtering, sorting, etc.. properties. Then, equate the ADO recordset back to the form recordset. See thread 705-211727 which deals with this issue.

 

[flash1]

No, I want to manipulate the recordset based one or many criteria selected by the user before bringing it back to the form. The criteria are applied one at a time drilling down to the resulting records.

I am new to this forum, how do I link/search to the thread that you referenced? I tried the search but without results.

Thanks,
Lynn

 

[cmmrfrds]

The thread is on this form last dated the 18th and has ADO in the title. I don't know how to find by thread number.

Are you using Access as the database or SQL Server? Which version of Access? In SQL Server you can use a stored procedure to do what you want. In Access it will be more difficult and require writing VBA code.

 

[flash1]

Thanks for the ADO info.

I had considered using a temporary table since I need to have the option to run a report on all or part of the resultant but wasn't sure if it was the best way to go.

I am using Access 97.

 

[KyleS]

Try this link:

thread705-211727

 

[cmmrfrds]

Since the database is Access, probably building an ADO recordset with a client side cursor is one way to go. If the initial recordset can be generalized enough, then you can do the filtering in the returned recordset and equate the filtered recordset to the Forms recordset.