Solved

Error generating a file for each group of records

Posted on 2011-09-28
8
287 Views
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)
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
0
Comment
Question by:Delores_C
  • 3
  • 3
  • 2
8 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
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
 

Author Comment

by:Delores_C
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
ITA ... A sample db will be needed.


ET
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Delores_C
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:Delores_C
Comment Utility
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
<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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now