2 recordsets

[Guest]

Hi All,
I had the code to do this once, but can't locate it.
I want to open a recordset #1, then open a 2nd recordset that is dependent
on the first recordset.
Like the first one would be cust id, name, etc
and the 2nd would be opening a recordset like payroll, where it would get
data for the cust id in recordset 1.
The reason for this is to export certain lines to EXCEL to a template.

Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("MyTable")
Do While Not rs.EOF

'open a 2nd recordset here
'if it's something, then export the line to EXCEL

rs.MoveNext
Loop
rs.Close

 

[Pat Hartman\(MVP\)]

This can easily be done without code by using a query that joins the two
tables and exporting to Excel with the TransferSpreadsheet Method/Action.
You can use expressions in the query if you need to reformat fields and
criteria in the where clause if you need to restrict the set of records.

 

[Guest]

perhaps I was not clear enough in what I want to do.
The end result is to be able to output to a template where the 1st row would
show primary information, and the 2nd row would be indented and would be
showing more information, like a drill down and would go into the 2nd column.

Since reports cannot be exported properly into EXCEL, many of the users want
the same nice format as ACCESS reports, but they want it in EXCEL.

Also, I will be sending out an email to business unit directors, that
contains only their relative data, so I need to include that in the 2nd
recordset.

 

[Pat Hartman\(MVP\)]

Wouldn't you be able to just export the flattened recordset and then add
grouping to the spreadsheet?
A real recordset would be much more flexible than a report.

 

[Guest]

No, that would defeat the purpose of having the EXCEL template formatted
correctly with the data placed as needed.

Thank you anyway.