Error generating a file for each group of records

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)
rst.MoveFirst
'rst.MoveLast
'rst.MoveFirst
'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
       
        rst.MoveNext
Loop


ProcExit:
    If Not rst Is Nothing Then
        rst.Close
        Set rst = Nothing
    End If
    Exit Sub
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error exporting file"
    Debug.Print "Error exporting file", Err.Number, Err.Description
    Resume ProcExit
Delores_CAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<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
0
 
Eric ShermanAccountant/DeveloperCommented:
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



ET
0
 
Delores_CAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Jeffrey CoachmanMIS LiasonCommented:
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

2.
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.


JeffCoachman



0
 
Eric ShermanAccountant/DeveloperCommented:
ITA ... A sample db will be needed.


ET
0
 
Delores_CAuthor Commented:
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

Columns
      Name      
      ProviderName      
      AccountNumber      
      RoutingNumber      
      ProvID          (this a Key field)
      Reference      
      Address1      
      Address2      
      City      
      State      
      Zip

      
Table: tluFundsImport

Columns
      Name      
      TransactionNumber      
      DraftDate      
      DraftID      
      AccountNumber      
      SSN      
      PatientName      
      IncurredFrom      
      IncurredTo      
      ServiceCode      
      Modifier      
      Billed      
      Eligible      
      Excess      
      Copay      
      Deductible      
      Paid      
      Comment      
      Sequence      
      Provider      
      TransactionComment      
      DateOfBirth      
      AlternateID      
      ProvID      
      Previously Paid      
      Previous TRNS      
      Units      
      Other      
      COB      
      Prac name      
      20%      
      PlanType

Copy-for-Analyze-Test-EFT-08-24-.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
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] & """"


JeffCoachman


0
 
Delores_CAuthor Commented:
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
0
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.

All Courses

From novice to tech pro — start learning today.