Solved

Error generating a file for each group of records

Posted on 2011-09-28
8
292 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
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



ET
0
 

Author Comment

by:Delores_C
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.
0
 
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

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 19

Expert Comment

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


ET
0
 

Author Comment

by:Delores_C
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

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
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] & """"


JeffCoachman


0
 

Author Comment

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

Accepted Solution

by:
Jeffrey Coachman earned 500 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
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

825 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