Error generating a file for each group of records

Posted on 2011-09-28
Medium Priority
Last Modified: 2012-05-12
The problem is 1).  It is only creating a few file, not all of them.  2).
the error message after it generate a couple of file is “Run-time error ‘2501’ The OutputTo action was canceled”.  The code I am using is listed below.  Any help is appreciated.

Private Sub Command3_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strReportName, strFileName As String
Dim strSQL As String
Dim rpt As Report

'On Error GoTo ProcError

strReportName = "rptMasterReport921"
strSQL = "SELECT tblProviderAccount.[Reference], tluFundsImport.DraftDate " _
    & "FROM tblProviderAccount INNER JOIN tluFundsImport ON tblProviderAccount.ProvID = tluFundsImport.ProvID " _
    & "GROUP BY tblProviderAccount.[Reference], tluFundsImport.DraftDate "

strSQL = strSQL & "ORDER BY tblProviderAccount.[Reference];"

'strSQL = "SELECT tluFundsImport.ProvID, tblProviderAccount.ProviderName, " _
'                        & "tluFundsImport.DraftDate " _
'            & "FROM tblProviderAccount INNER JOIN tluFundsImport " _
'            & "ON tblProviderAccount.ProvID = tluFundsImport.ProvID " _
'            & "GROUP BY tluFundsImport.ProvID, tblProviderAccount.ProviderName, " _
'                            & "tluFundsImport.DraftDate;"
Debug.Print strSQL
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
'MsgBox rst.RecordCount
Do While Not rst.EOF
        strFileName = "C:\Users\Eipc\Desktop\" _
                    & rst![Reference] & " " & "(Plan 92) " & Format(rst!DraftDate, "mm_dd_yyyy") & ".pdf"
        Debug.Print strFileName
        StrFilter = "[Reference] = '" & rst![Reference] & "'"
        If DCount("*", "qryRPT1", StrFilter) > 0 Then
        DoCmd.OpenReport strReportName, acViewPreview, "qryRPT1", StrFilter, acHidden
        DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileName
        DoCmd.Close acReport, strReportName
        End If

    If Not rst Is Nothing Then
        Set rst = Nothing
    End If
    Exit Sub
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error exporting file"
    Debug.Print "Error exporting file", Err.Number, Err.Description
    Resume ProcExit
Question by:Delores_C
  • 3
  • 3
  • 2
LVL 19

Expert Comment

by:Eric Sherman
ID: 36790769
Sounds like the recordsource is empty therefore the OutputTo action is probably cancelled.

Try counting a field instead of the "*" in the qryRPT1 recordsource ....

 If DCount("[YourFieldNameHere]", "qryRPT1", StrFilter) > 0 Then


Author Comment

ID: 36814168
It didn't work, I am still getting the first couple of records in alphabetical order.

When I run the query, which is the record source, it returns at least 40 records.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36816043
A few things (Not sure if they are relevant)

1. Dim strReportName, strFileName As String
...In this line only strFileName  is actually a string.
"strReportName" will be a *Variant", because no datatype is being explicitly assigned
Do this instead:
Dim strReportName as string
Dim strFileName As String

I'm a little confused at your error handler...

The recordsets should be closed and set to nothing in the code normally and in the Error handler.
You seem to be only doing this if there is an error...

3. Still confused why two querys are needed.
The SQL and the qryRPT1

4. Also when you do this:
If DCount("*", "qryRPT1", StrFilter) > 0 Then
...you have an "If", ...but no "Else"...
So you will only generate a file: If DCount("*", "qryRPT1", StrFilter) > 0

5. If you are only getting one file, then look at this record and see what the difference is between this record and the next record.

But at some point (without a sample) we are all guessing here...
This is why it is always best to post a sample file.


Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 19

Expert Comment

by:Eric Sherman
ID: 36817474
ITA ... A sample db will be needed.


Author Comment

ID: 36892937
sort by PlanType,
Group by Reference,
Keep ProvID in the Group Reference

Reference is linked to the ProvID,
The Reference has many ProvID, I need to keep all the ProvID in one report per Reference.  
This is necessary because at the top of the report I need to provide the total Paid for a Reference. I need to also keep each PlanType together by Reference.
 I created a independent Report for each PlanType, and linked the report to the query.

There are two tables

Table: tblProviderAccount

      ProvID          (this a Key field)

Table: tluFundsImport

      Previously Paid      
      Previous TRNS      
      Prac name      

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36894155
You have lots of "issues" with this system.
(Perhaps these would be better served by you posting separate questions)

But the main issue here (Files not being generated) seems to be the Apostrophe in the Reference ("Mary's Hospital")

So do this in your code for the Filter string:
        StrFilter = "[Reference] =" & """" & rst![Reference] & """"



Author Comment

ID: 36894530
I've removed the Apostrophe so it eliminated the start of generating the file.  But the main problem is that it cancel the output after generating a few reports
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 36894822
<But the main problem is that it cancel the output after generating a few reports>
Not in the file you posted here...

After I modded the code, it generated the (3) files just fine

Again, this is why it is confusing when you post what appears to be duplicate questions...

So see you "other" question on this, and see if that resolves the issue.

Either that, or post a copy of the db that generates the error

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question