Querying from recordsets

[BAleiHi]

I want to create a recordset using one query, then do a subsequent query on the result to do some more manipulation. However, I can find no syntax for selecting from a recordset variable (what are its parent database and table names?). Sometimes I can use a workaround by having the first query be a SELECT...INTO, yielding a new table in a known database from which I can do the subsequent query. But when I want the first query to be a crosstab, it seems not to support SELECT...INTO. My current workaround is a routine I wrote to copy a recordset to a table but it is a *CLUNKY KLUDGE*! Surely there is a more elegant way. Any help?

TIA

Bob Alei

[jim]

Create a temporary querydef and run your second query against the querydef.

[JHausmann]

I use the following (assume you've already opened a database object):

dim rstmp as recordset
dim rstmp1 as recordset


sSQL= "select * from table1 where field=" & varaible
set rstmp=database.OpenRecordset(sSQL, dbOpenSnapshot)

If rsTmp.EOF = False Then
    Do While Not rsTmp.EOF
        key1 = rsTmp.Fields(0)
'       [...]
        lastfield = rstmp.fields(x)
        sSql2 = "select something from table2 where item='" & key1 & "' 
        Set rsTmp2 = database.OpenRecordset(sSql2, dbOpenSnapshot)
        If rsTmp2.EOF = False Then 
'          do something like read the fields
        endif
        rsTmp2.Close
        rsTmp.MoveNext
    loop
    rsTmp.Close
End If

(hit return typing stuff in, not a good idea)


[This message has been edited by JHausmann (edited 08-23-1999).]

[bashfirst]

If you are using DAO you could use the Filter method on the existing recordset to get a subset....

Bash