Creating Recordsets from a Recordset 1

[Snaggs]

Does anyone know how to create a recordset from another recordset? I need it for a drill down application where the initial SQL statement would be too long and complex.

For example, create a RS with all the states, then let the user pick a few states to keep and then dump those into a new recordset. Then let the user pick a few cities and dump that into a new recordset then let the user pick a few zipcodes and dump that into a new recordset.

The ultimate goal here is to end up with a recordset that can be bound to a report at run-time. I got the run-time report binding to work, I just can't figure out how to build a recordset from another recordset.

I'm thinking something like this:

SELECT * FROM <complex join> WHERE STATE IN(<whatever user selected>)
and save the result in RS1. Then for RS2 do something like this:
SELECT * FROM RS1 WHERE Cities IN(<whatever user selected>)
Then for RS3:
SELECT * FROM RS2 WHERE Zipcode IN(<whatever user selected>)

Then finally bind RS3 to a report and blow away RS1 and RS2.

I'm using ADO 2.1.

Thanks in advance,

Steve
tribesaddict@swbell.net

 

[caf]

Hi

Did you get an answer to this one already or did you figure it out already?

Do you want me to send you some code?

caf

 

[KirkJewell]

I'm still waiting for my copy of vb6 enterprise to turn up but I have been using Jet3.5 in access for years.

if I wanted to make a new recordset off an exisiting one I would do something like the following:- ( please allow for poetic licence - its more of an idea than an explicit code example).


rs1 = blah blah blah (all the original states)
....The user selects the fields they want (perhaps by setting a checkbox field call chkState

Dim rs1 as recordset
Dim rs2 as recordset
Dim rs3 as recordset

rs1.filter = &quot;[chkState] = True&quot; (this sets the filter but wont change the selected records in rs1)
set rs2 = rs1 (now (in access) rs2 will equal all the records in rs1 but filtered)
if you need to do it again
rs2.filter = &quot;[State] <> '&quot; & strState & &quot;'&quot; (perhaps ignoring a particular state)
set rs3 = rs1 (or you could you rs1 again if you didnt need it for anthing else)

Then give your final recordset pointer to your report.

I hope this helps (or course I haven't received my copy of vb yet - so i might be talking out of my ass.)
Regards
Kirk


Its very quick and reliable and you can do it over and over again.

 

[Snaggs]

Yes I did figure it out, but this process seems to be needed more than I thought. Cal, if you could post your code to show us what you did, I'll go ahead and post the code that I used in the project.

KirkJewel, thanks for posting the information about how to do this as well. Snaggs
tribesaddict@swbell.net

 

[Snaggs]

I used an idea from Microsoft's site. Refer to document Q247868. It works great and it's pretty quick too. It uses basically the same concept that Kirk mentioned above. Snaggs
tribesaddict@swbell.net