Problem with OutputTo command

[Dan]

In an Access 2k database I'm using the OutputTo command to save a report to a
Snapshot file. This works fine as long as I only save one report and then
exit the routine. However, as soon as I attempt to loop through a recordset
containing various reports to be saved I receive an error: "The command or
action 'OutputTo' isn't available now." The first report in the list is saved
appropriately but as soon as the code attempts to output the second report
the error occurs.

The same code works fine if I save as acFormatRTF. Example code is below:

strSQL = "SELECT * FROM CustRptSelectReports WHERE
CustRptSelectReports.Select = yes " _
& "AND CustRptSelectReports.reportID = " & ReportID & ";"
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
DoCmd.OutputTo acOutputReport, "rptCustomerCover", acFormatSNP
rst.MoveFirst
Do Until rst.EOF
Select Case rst!Survey_Type
Case "Warewash"
DoCmd.OutputTo acOutputReport,
"rptCustomerWarewash", acFormatSNP
Case "Laundry"
DoCmd.OutputTo acOutputReport, "rptCustomerLaundry",
acFormatSNP
Case "Ancillary"
DoCmd.OutputTo acOutputReport,
"rptCustomerAncillary", acFormatSNP
Case "Housekeeping"
DoCmd.OutputTo acOutputReport, "rptCustomerHK",
acFormatSNP
Case "Floor Care"
DoCmd.OutputTo acOutputReport, "rptCustomerFC",
acFormatSNP
End Select
rst.MoveNext
Loop
DoCmd.OutputTo acOutputReport, "rptCustomerSummaryMain",
acFormatSNP

 

[SA]

Dan:

Try adding a little timer loop between outputs to allow Access to finish up
outputting the first report before sending the next report. You'd do this
like so:

Dim Timer1 as Single, Timer2 as Single

Docmd.OutputTo .....
Timer1 = Timer()
Do until Timer2 >= Timer1 + .25 'quarter of a second adjust as necessary
DoEvents
Timer2 = Timer()
Loop

 

[Dan]

I had tried this with no success. However, I rethought the code and tried it
again and it worked. Thanks.

 

[Dan]

Actually, I lied. Adding a delay does not fix it. The code works fine if I
use the debugger and step through the code. It also works fine if I open the
form and run the code from the database window. However, as soon as I open
the program as the user would and use the Main Menu to navigate to the form
and run the code...I get the error...no matter how long of a delay I set.
Something is obviously getting "hung up". Do I have to try to turn something
off after each OutputTo?

 

[Dan]

The problem was caused by the database window being hidden. I wrote code to
unhide the window before outputting the reports and code to hide it again
after the process was finished. No more errors.

 

[Harry V]

Steve,

I saw this thread and applied your timer to a similar problem. I'm calling
a report several times based on keys I get from a recordset and outputting
the reports to text files named by the key info. My problem is that unless I
close each preview, I get the same data in each text file, rather than
different data sets. The timer gives me the oportunity to close each
preview, but I'd lke to automate it fully. If I use the Print view, I get
asked for the file name (before it gets to the OutpuTo command, I think):

DoCmd.OpenReport "myReport", acViewPreview, "myQuery", myFilterString
DoCmd.OutputTo acOutputReport, "myReport", acFormatTXT, FileName, False

Followed by a timer loop set to 6 seconds. If the user closes each preview,
the correct data gets written to each file. If not, the data in the current
preview gets written to each file. I'm testing with a dataset that would
benerate 6 files, butthere is the potential to write hundreds...