Solved

When printing Access Reports, printing stops after 8 pages.

Posted on 2011-03-08
21
469 Views
Last Modified: 2012-05-11
This code works perfectly except for the fact that it will not finish printing all the pages.
It stops on 8 1/2 pages on every report that is passed to it.
ie... If the report should be 200 pages, I am only getting the first 81/2 pages and then it moves on to the next report.
 
This is the Report Module's code:

Private Sub Report_Open(Cancel As Integer)
   Dim strLine As String
    varText = Null
    Open OpenArgs For Input As #1
   Do Until EOF(1)
        Line Input #1, strLine
        varText = varText + vbCrLf & strLine
    Loop
    Close #1
End Sub

And this is how I call the above Sub:

If AutoPrint = True Then
      If outputType = "Print" Then
          DoCmd.OpenReport "rptTextFile", acViewNormal, , , , oFile
      End If
End If
0
Comment
Question by:djMcCauley
  • 13
  • 8
21 Comments
 
LVL 57
ID: 35073065
<<varText >>

  A string can only hold 64k characters; what is it your trying to do with this?

JimD.
0
 

Author Comment

by:djMcCauley
ID: 35073304
In the code, I am looping thru the lines of the source text file using: Line Input , strLine
Each strline is output perfectly until it gets 8 pages in.
Each string line is 120 char, I think.
0
 

Author Comment

by:djMcCauley
ID: 35073444
Here's a little more of the code that is used to call the Sub:

Do Until rs.EOF
        Client = rs.Fields(0).Value
        invGrp = rs.Fields(1).Value
        inv = rs.Fields(2).Value
        invName = rs.Fields(3).Value
        If IsAdhoc Then
            outputPath = "C:\Data\P4DL\InvReports\InvRpts_ADHOC\"
        Else
            outputPath = rs.Fields(5).Value
        End If
        outputType = rs.Fields(6).Value
 
  'Read the Main SAR File and extract only the data for the appliable Inv
        oFile = OutputFile(fileName, Client, invGrp, inv, invName, outputPath, rptType, outputType, RptDate)
           
  'Print the Investor's Report if applicable
            If AutoPrint = True Then
                    If outputType = "Print" Then
                        DoCmd.OpenReport "rptTextFile", acViewNormal, , , , oFile
                    End If
            End If
         rs.MoveNext
Loop
0
 
LVL 57
ID: 35073611
 Your not going to be able to do it that way.  Since a string is limited to 64K:
 
  60 lines per page * 120 char = 7,200 char per page x 8 = 57,600

  So some where on the 8th page worth of stuff, your running out of string space.

You have a couple of choices:

1. Use VBA to read each record and save to a table.  Then run the report off that.
2. Use the TranferText method to fill a table, then run the report off that.

 But first, you can try this.  Not sure how well it's going to work.

1.  Leave the open statement for the file in the report's OnOpen event.
2. Move the Line Input #1, strLine statement to the details OnFormat event.  Make sure you do it like this:

   If FormatCount = 1 then
      On Error Resume Next
      Line Input #1, strLine
      If EOF(1) = True then
         DoCmd.Close acReport, Me.Name
      Else
        Me.<some control> = strLine
      End If
   End if

3. And in the reports OnClose event:

   Close #1

  That should allow it to report on any size file.  However I'm not sure what your doing in the report besides this, so that may not work.

JimD.


 

0
 

Author Comment

by:djMcCauley
ID: 35073901
The only thing I need to accomplish with this, is to just copy verbatum what is in the source file that pertains to the selected Investor.
What is <some control> in the else statement?
0
 
LVL 57
ID: 35073976
<<What is <some control> in the else statement? >>

  An unbound control in the reports detail section, which will display the data.

JimD.
0
 

Author Comment

by:djMcCauley
ID: 35074027
Like this?

Option Compare Database
Option Explicit
Dim varText As Variant
----------------------------------------------------------------------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Text = varText
   
    If FormatCount = 1 Then
      On Error Resume Next
      Line Input #1, strLine
      If EOF(1) = True Then
         DoCmd.Close acReport, Me.Name
      Else
        Me.<some control> = strLine
      End If
   End If
End Sub

Private Sub Report_Close()
Close #1
End Sub

Private Sub Report_Open(Cancel As Integer)
     
    Dim strLine As String
    varText = Null
   
    Open OpenArgs For Input As #1
   Do Until EOF(1)
        Line Input #1, strLine
        varText = varText + vbCrLf & strLine
    Loop
    Close #1
 End Sub

But I'm not sure what is meant by this line?        Me.<some control> = strLine
0
 

Author Comment

by:djMcCauley
ID: 35074218
Sorry, I mean like this?

Private Sub Report_Open(Cancel As Integer)
     Open OpenArgs For Input As #1
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strLine As String
If FormatCount = 1 Then
    On Error Resume Next
    Line Input #1, strLine
      If EOF(1) = True Then
         DoCmd.Close acReport, Me.Name
      Else
        Me.<some control> = strLine
      End If
End If
End Sub

Private Sub Report_Close()
  Close #1
End Sub
0
 

Author Comment

by:djMcCauley
ID: 35074309
I must be having brain freeze here! I just don't seem to understand when you say:
--An unbound control in the reports detail section, which will display the data--

I really don't use Access Reports ever and so I am not that familular with this.
I will need for you to spell this out for me if you don't mind.
0
 
LVL 57
ID: 35074449

 Create a control in the reports detail section.  Call it txtLineData.  Leave the controlsource property blank.  Then change the code to:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strLine As String
   
    If FormatCount = 1 Then
      On Error Resume Next
      Line Input #1, strLine
      If EOF(1) = True Then
         DoCmd.Close acReport, Me.Name
      Else
        Me.txtLineData = strLine
      End If
   End If

End Sub
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:djMcCauley
ID: 35074522
So, when you say create a control in the reports detail section, are you talking about putting a text box or lable directly on the report's detail section?

I guess the "create control" is throwing me.
0
 
LVL 57
ID: 35083055
<<So, when you say create a control in the reports detail section, are you talking about putting a text box or lable directly on the report's detail section?>>

  yes.  You would want a text control so the data could be printed.

JimD.
0
 

Author Comment

by:djMcCauley
ID: 35083507
Ok, thanks.
I'm not going to be able to try your solution out until late in the day due to other processes going on, so
please don't think that I have abandoned this work.
0
 

Author Comment

by:djMcCauley
ID: 35087078
Just ran the above code. It will only print the first pageline of the Source file.
Which actually means we are successful in starting to split it up into pagelines instead of the entire report. Just need the rest of the pageLines.
0
 
LVL 57
ID: 35087155
Sorry.  Been a while since I tried an un-bound report.  Give this a go in the OnFormat:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strLine As String
    If FormatCount = 1 Then
      On Error Resume Next
      Line Input #1, strLine
      If EOF(1) = True Then
         DoCmd.Close acReport, Me.Name
      Else
        Me.<some control> = strLine
        Me.Movelayout = True
        Me.PrintSection = True
        Me.NextRecord = False
     End If
     
End If
End Sub

0
 

Author Comment

by:djMcCauley
ID: 35087511
That printed the same first line twice, spaced down about an inch.
Here is the code so far:

Option Compare Database
Option Explicit
_______________________________________________________________
Private Sub Report_Open(Cancel As Integer)
     Open OpenArgs For Input As #1
End Sub
______________________________________________________________________
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 
    Dim strLine As String
    If FormatCount = 1 Then
      On Error Resume Next
      Line Input #1, strLine
        If EOF(1) = True Then
            DoCmd.Close acReport, Me.Name
        Else
            Me.txtLineData = strLine
            Me.MoveLayout = True
            Me.PrintSection = True
            Me.NextRecord = False
        End If
     End If
End Sub
______________________________________________________________
Private Sub Report_Close()
  Close #1
End Sub
0
 

Author Comment

by:djMcCauley
ID: 35087560
How about code that can just open up a directory (folder) on the local drive and print each file that it finds in the folder?
0
 
LVL 57
ID: 35087806
Here's the correct code:

    Dim strLine As String
   
      On Error Resume Next
      Line Input #1, strLine
        If EOF(1) = True Then
            Cancel = True
            DoCmd.Close acReport, Me.Name
        Else
            Me.txtLineData = strLine
            Me.MoveLayout = True
            Me.PrintSection = True
            Me.NextRecord = False
        End If

<<That printed the same first line twice, spaced down about an inch.>>

  Make sure you have the bottom of the detail section snugged up to the bottom of the txtLineData control.

JimD.

0
 

Author Comment

by:djMcCauley
ID: 35087955
Ok, this appears to work, except for the formatting. The lines are to far apart and the spacing on each line is compressed. So the the columns don't line up to the proper header.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 35088137
<<Ok, this appears to work, except for the formatting. The lines are to far apart and the spacing on each line is compressed. So the the columns don't line up to the proper header. >>

  You need to adjust the size of the font, the font used, and the size of the control and/or detail section.

JimD.
0
 

Author Closing Comment

by:djMcCauley
ID: 35088518
Thank You for a job well done. Also for being very patient with me.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 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

12 Experts available now in Live!

Get 1:1 Help Now