Can I use a recordset as input to an SQL query? (1 Viewer)

[DanR]

Hi

I have a function which returns a recordset as its output.

I want to use this recordset to restrict the records displayed by a form, by either adding a filter in runtime, or editing the RecordSource property to add a WHERE clause.

I know how to do this if I was working with a table. For example this recordsource for a form:

SELECT tblTaxonList.TaxonID, tblTaxonList.TaxonName
FROM tblTaxonList

Can be restricted based on the the unique TaxonID in table tblChildren, to look like this:

SELECT tblTaxonList.TaxonID, tblTaxonList.TaxonName
FROM tblTaxonList
RIGHT JOIN tblChildren
ON tblTaxonList.TaxonID = tblChildren.TaxonID

So my question is, can I do this if tblChildren is actually a recordset rstChildren? Or do I need to turn it into a table to use it in this way?

Unfortunately, I can't just use the SQL code that defines rstChildren, because it uses several steps of VBA code to choose which records to add.

Please help

cheers
Dan

 

[Pat Hartman]

Look up disconnected recordsets. I have not used any but they might be what you need.