VB code to create and export several queries

[Slayboy]

Hi,

I am having trouble finishing off some code that should step through one query and fiter another query by each record, exporting them each time to excel.

This is the code below, basically it open a query containing just Owner names, takes the first one and filters another query on it, sets up the SQL code and then creates the query and exports it.
For some reason access is having trouble with the 'set rst1 =' bit, What am I doing wrong?

CODE:

Private Sub Command255_Click()

Dim dbs As Database, rst As Recordset, rst1 As Recordset
Dim SQLString As String
Dim Owner As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ELTDistributionRecipients", dbOpenSnapshot)

rst.MoveFirst
Do
Owner = rst.Fields![Owner]

SQLString = "SELECT ELTDistributionReport.*" _
& " FROM ELTDistributionReport" _
& " WHERE (((ELTDistributionReport.Owner)='" & Owner & "'));"

Set rst1 = dbs.OpenRecordset(SQLString, dbOpenTable)

DoCmd.OutputTo acOutputQuery, rst1, acFormatXLS, DLookup("[Path]", "[ReportPrintTableLocation]", "[PID]=1") & Owner & ".xls", False

If Not rst.EOF Then rst.MoveNext

Loop Until rst.EOF


End Sub

 

[DCrake]

Simple Software Solutions

I think it is down to naming conventions, I don't think Access will like you using the word owner. This could be reserved word.

Also you can not export a recordset. You will have to change the sql in the actual query and export that. The query must contains in the querydefs collection for this to work. Alternaively is to use Excel Automation and use the CopyFromRecordset option.

CodeMaster: