Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error generating a file for each group of records

Posted on 2011-09-28
8
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 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
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 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