[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

When printing Access Reports, printing stops after 8 pages.

Posted on 2011-03-08
21
Medium Priority
?
478 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
[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
  • 13
  • 8
21 Comments
 
LVL 58
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58
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 58
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 58
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
 

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

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

649 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