• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Print a report with a second page conditionally Access 2002

In the detail section of my report I have a subreport that lists names.  My report prints on a preprinted form.  There are 7 available lines in the detail section to fill on this preprinted form.  If the number of names in this section of the report exceeds 7, I need to force a second page to print ONLY the remaining names in the same detail section of the report on the second page.

Thank you for any help.  
0
jpaulfain
Asked:
jpaulfain
  • 13
  • 8
  • 6
  • +1
1 Solution
 
harfangCommented:
Hello,

There is no easy way to do this. Basically, you will need the detail section and the subreport to grow (these are the default properties), but not find any more room on the page after seven lines. In other words, you must increase the page footer until you leave room for the right number of lines.

The subreport must be at the very top of the detail section, because the first line of the second page will be. Also, turn off "Keep Together" for the detail section, or you might create an infinite loop to find the right page...

If you look into VB, there are events to handle that case: Retreat (oops, didn't fit!) and Format's FormatCount parameter (second go to finish printing that section!).

Hope this helps,
(°v°)
0
 
klilleyCommented:
You could change the subform to only print the first 7 lines, then have a second report with only the subform on it (excluding the first 7 lines).

Count the number of records in code and optionally print the second print.

ie.

docmd.openreport "myreportname"
if dcount("[Field]","[table/query]","[criteria]") > 7 then
     docmd.openreport "myreportnamepage2"
end if


0
 
jpaulfainAuthor Commented:
harfang:  

Thank you for the suggestion, but since the subform/subreport is located near the bottom of the page, the printed lines on the second page must also print at the same location on the page.  This is a preprinted form the report is filling out.  Since this approach will continue printing at the top of page 2 it will not work for me.  

Thank you,
Paul Fain
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
klilleyCommented:
not if you position the subform on the second page in the correct location
0
 
jpaulfainAuthor Commented:
klilley:

Thank you for your suggestion.  I am going to try this approach.  I like the idea of generating a second report with the subform/subreport located in the correct position for the second printed page.  And these additional lines in the subreport (above 7) are all I want to pront on page two, so this would work.  It also allows for a pause in the coding so that another page could be inserted into the printer before printing page two.

Please help me with this..... After the code "counts" the number of names that need to be printed, how do I restrict the subform on page one to print only the first 7 names, and restrict the subform on page 2 to print the remaining X number of names?  Any help is appreciated here.

Thank you,
Paul Fain

0
 
klilleyCommented:
To make  life easier ill use report 1 with subform A and report 2 with subform B and ai assume the table the queries are based on has a primary key

Subform A is based on a query (A) that returns the top 7 records based on the relevant criteria -  ie SELECT * from Mytable where criteriafield = "X"

Subform B is based on an unmatched query (B) that is linked to query A and returns all those record not displayed in query - ie SELECT * FROM MyTable LEFT JOIN queryA ON queryA.IDField= mytable.IDfield WHERE mytable.idfield Is Null and mytable.criteriafield="X";
0
 
klilleyCommented:
oops first query should be SELECT TOP 7 mytable.* from mytable where criteriafield="X"

Sorry

Kev
0
 
jpaulfainAuthor Commented:
Kev:

Thank you.  Give me some time to try this.  I will likely be back for more help.  If you think of any other clarifying information for me, please post it.

paul fain
0
 
jpaulfainAuthor Commented:
Kev:

Please walk me through the design of an unmatched query (B) that is joined to query A.  Can I just duplicate query A to create query B and somehow join it with query A?  Please explain further.

paul
0
 
jpaulfainAuthor Commented:
Kev:

As I am working with this, I have taken an initial step of modifying subform query A and setting a SELECT TOP value of 7.  However, when I run the report, nothing prints or previews in subform A in report 1.  Yet, when I run the query with a SELECT TOP of ALL, it always works as before.  Question.... should subform query A be joined with the parent report's form query?  Right now they are not, I have been using a query for the parent report form, and a different query for the imbedded subform in the report.  This has been working fine for years but I just cannot make it work when I adjust the TOP values on the subform A query.

paul
0
 
klilleyCommented:
Paul

You have to save query A.  When you use TOP 7 does the query run on its own (ie ignoring the report)?

You can use the unmatched query wizard to help you.

Can you post up the SQL of query A and ill see if it will shed some light on the prob?

Kev
0
 
jpaulfainAuthor Commented:
When you use TOP 7 does the query run on its own ?   "YES"  it runs fine from the design view.  

paul



0
 
harfangCommented:
Paul,

I understand that you need the lines to print at the correct distance from the top on page two. But the same was true for page  one. There is no difference there. Simply make sure that whateve you used to place the subreport on the right place is used again on page two.

But I did explain that already.

The need for a pause in the printing however makes the point moot. A report will always be sent to the printer as a single job, so introducing pauses during the print process would not work.

You are now trying to figure out a way to print only 7 records. 1-7, 8-14, etc. Kev is right in pointing to the "TOP 7" SQL predicate, but it's not easy to use, mostly because it relies on a unique ID. Also, you would have to write the "LIMIT(8,7)", "LIMIT(15,7)", etc. queries. That is a pain, because LIMIT is not recognized by Access SQL. You would need -- for LIMIT(8,7):

    SELECT TOP 7 * FROM tblYourTable
    WHERE YourID Not In (
        Select Top 7 YourID From tblYourTable
        Order By <sort fields>, ID
        )
    ORDER BY <sort fields>, ID;

The next problem will be how to pass thet query to the subreport...


Consider this VB solution:

Any normal module:

    Global glngRowOffset  As Long
    Global gfRowsContinue As Boolean

When calling your report:

    glngRowOffset = 0
    Do
        gfRowsContinue = False
        MsgBox "need paper, please!"
        DoCmd.OpenReport .....
        glngRowOffset = glngRowOffset + 7
    Until Not gfRowsContinue

As you see, this will keep on calling the same report with increasing values in glngRowOffset, provided that the report sets gfRowContinue to True.

The detail section of your subreport can then use this:


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Static slngRowCount As Long

    ' counting printed records
    slngRowCount = slngRowCount + 1

    If slngRowCount <= glngRowOffset Then
        ' printed last time, skip:
        Cancel = True
    ElseIf slngRowCount <= glngRowOffset + 7 Then
        ' that's our range (proceed)
    Else
        ' keep for next time
        Cancel = True
        gfRowsContinue = True
    End If

End Sub


This might look complicated if you have little experience with VB, but it's really quite simple.

Good luck!
(°v°)
0
 
harfangCommented:
Missing  a transition above, at "consider this VB solution" ... INSTEAD.
The "Top 7" SQL and the VB solution should not be mixed, as I hope you understood.
(°v°)
0
 
jpaulfainAuthor Commented:
Thank you both for your input.  I will give these suggestions some thought and try them as soon as possible.  
Thank you very much.

paul
0
 
jpaulfainAuthor Commented:
harfang:

You are right, thank you, your suggested VB code nicely controls how many lines will print in the subform/subreport.  Please explain how the remaining lines (above our preset 7) will be determined and then be printed on a second page in the same location.  Do I need to add a page break in the report?  Should I insert another copy of the same subform/subreport into the parent report after the page break?  Will the second subform use the same query as the first page subform as it’s source?  Please explain further… thank you.

paul
0
 
harfangCommented:
Hello,

In the above {http:#16592076} you have two code fragments in the "VB solution".

1) A loop calling the same report over and over, each time warning the user first using a message box. This loop will continue as long  as the report detects additional unprinted records.

2) The code for the report itself. It will
    a) skip over already printed records,
    b) print seven records, and
    c) raise a flag if there are more to print.

So:

> Do I need to add a page break in the report?
No. In fact, your subreport could have a fixed height. Each time it is printed, it will print only seven lines. That's all.

> Should I insert another copy of the same subform/subreport [...]
No again. The control loop will print your  report over and over.

> [...]  use the same query [... ]
The query is not the issue. You do not need to add any "top 7" or other tricks. The subreport will always loop through all records, but the VB code will allow the actual printing of only seven of those, determined by the current value of the global variable glngRowOffset...

I hope this clarifies. If not, feel free to ask for more details.

Good luck!
(°v°)
0
 
jpaulfainAuthor Commented:
harfang:

Thank you for walking me through this thing.  Below are the VB events that I have so far.  I also declared the two global variables
in a module.  All seems to be working except that this is associated with a PREVIEW of the report, not actual print of report.  
While the first time through runs perfectly, I see no indication that the report is attempting to print again for the remaining
lines in the subform/report.  Please tell me what you think and suggest whatever I need to do from here.



THE FORMAT EVENT IN THE DETAIL SECION OF THE SUBFORM/SUBREPORT....

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


    Static slngRowCount As Long

    ' counting printed records
    slngRowCount = slngRowCount + 1

    If slngRowCount <= glngRowOffset Then
        ' printed last time, skip:
        Cancel = True
    ElseIf slngRowCount <= glngRowOffset + 13 Then
        ' that's our range (proceed)
    Else
        ' keep for next time
        Cancel = True
        gfRowsContinue = True
    End If

End Sub



..................................

HERE IS THE ONCLICK EVENT ASSOCIATED WITH THE COMMAND BUTTON CALLING THE REPORT........
Private Sub PreviewReport_Click()

On Error GoTo Err_Preview_Click
       
    Dim stDocName As String
    Dim acObjState_Open As AccessObject
   
   
'Prepare to preview report with additional code to count records printed, flag
'the number of lines not printed and run S-2 Front report repeatedly until all
'counted lines in record set have printed in the S-2 subform.

    glngRowOffset = 0
   
    Do  'start DO While loop
        gfRowsContinue = False
        MsgBox "Place form(s) in printer, please!"

    Select Case Me!WhichReportOption
        Case 1  'S-303 Front Option
            stDocName = IIf(SelectPrinterOption = 1, "Print S-303 Front Printer 1", "Print S-303 Front Printer 2")
            DoCmd.OpenReport stDocName, acViewPreview
           
            'the following will hide the "Print Menu" until exiting the preview
           
            Me.Visible = False
            DoEvents
            While SysCmd(acSysCmdGetObjectState, acReport, stDocName) = acObjState_Open
                DoEvents
            Wend
            Me.Visible = True
            DoEvents

           
        Case 2  'S-303 Back Option
            stDocName = IIf(SelectPrinterOption = 1, "Print S-303 Back Printer 1", "Print S-303 Back Printer 2")
            DoCmd.OpenReport stDocName, acViewPreview
           
            'the following will hide the "Print Menu" until exiting the preview
           
            Me.Visible = False
            DoEvents
            While SysCmd(acSysCmdGetObjectState, acReport, stDocName) = acObjState_Open
                DoEvents
            Wend
            Me.Visible = True
            DoEvents
           
        Case 3  'S-2 Front Option
            stDocName = IIf(SelectPrinterOption = 1, "Print S-2 Front Printer 1", "Print S-2 Front Printer 2")
            DoCmd.OpenReport stDocName, acViewPreview
           
            'the following will hide the "Print Menu" until exiting the preview
           
            Me.Visible = False
            DoEvents
            While SysCmd(acSysCmdGetObjectState, acReport, stDocName) = acObjState_Open
                DoEvents
            Wend
            Me.Visible = True
            DoEvents
           
        Case 4  'S-2 Back Option
            stDocName = IIf(SelectPrinterOption = 1, "Print S-2 Back Printer 1", "Print S-2 Back Printer 2")
            DoCmd.OpenReport stDocName, acViewPreview
           
            'the following will hide the "Print Menu" until exiting the preview
           
            Me.Visible = False
            DoEvents
            While SysCmd(acSysCmdGetObjectState, acReport, stDocName) = acObjState_Open
                DoEvents
            Wend
            Me.Visible = True
            DoEvents
           
        Case Else
            MsgBox "Select a report", vbExclamation, "Reports"
   
    End Select
   
    glngRowOffset = glngRowOffset + 13
   
    Loop Until Not gfRowsContinue   'finish Do While loop



Exit_Preview_Click:
    Exit Sub

Err_Preview_Click:
    Resume Exit_Preview_Click

End Sub
0
 
harfangCommented:
Hello,

The first problem is that you do not look at the error code. At the very least, replace the last lines of your PreviewReport_Click() handler:


Exit_Preview_Click:
    Exit Sub

Err_Preview_Click:
    MsgBox Err.Description, vbCritical, "Error " & Err.Num   ' <----- this line added!
    Resume Exit_Preview_Click

End Sub


This should provoke the  error "Object variable or With block variable not set".

If you want to know where that happens, you can try to comment out the very first line:

    ' On Error GoTo Err_Preview_Click

This would remove error handling, and you would be able to debug the code. As it stands, you have a strange declaration in your sub:

    Dim acObjState_Open As AccessObject

This object is never set to anything, instead it is used in place of the constant acObjStateOpen (with is =1). So finally, it boild down to:
* Remove the line "Dim acObjState_Open As AccessObject"
* Replace acObjState_Open with acObjStateOpen

With these changes you should make it work.

Good luck, keep me posted!
(°v°)
0
 
jpaulfainAuthor Commented:
harfang:

All is working good so far.  I have another question.  When the report prints the first time, if the slngRowCount exceeds the maximum of 13 lines, the remaining number of lines is flagged and the report continues printing until all lines in the subform have been printed.  But, there is other information printed on the first page that I do not want printed on the following pages.  How would you recommend I use VB code (the flagged variable) to omit certain fields or sections of the report on the following pages.  Thank you.

paul
0
 
harfangCommented:
I see.

glngRowOffset is 0 only the first time, i.e. the first page. For an entire section, use:

Private Sub SomeSectionForPageOne(FormatCount As Integer, Cancel As Integer)
    Cancel = glngRowOffset   ' don't print unless offset = 0
End Sub

For a single control on a section

<format event>
    Me.txtPageOneOnly.Visible = ( glngRowOffset = 0 )
    Me.txtPageTwoAndMore.Visible = glngRowOffset

Cheers!
(°v°)
0
 
Jeffrey CoachmanCommented:
jpaulfain,

You might also be interested in this:

http://support.microsoft.com/?kbid=209006

(ACC2000: How to Control the Number of Records Printed per Page)

Good luck!
0
 
jpaulfainAuthor Commented:
harfang:

I have several controls in the detail section of the report that I would like to hide on second page.

Would this work in the "source property" of a control in the detail section of the report?

IIF((glngRowOffset = 0),[ControlNameWillPrint],"  ")

paul
0
 
harfangCommented:
Paul,

This doesn't work. The control source is evaluated in another VB project (Access') and only functions are available. So, create this public function in your report:

Public Function IsFirstPage() As Boolean
    IsFirstPage = (glngRowOffset = 0)
End Function

This will be available, for example through:

    = IIf( IsFirstPage(), [FieldName], Null )
    = IIf( IsFirstPage(), "Some text for page 1", "...con'd" )

Cheers!
(°v°)
0
 
klilleyCommented:
To hide multiple controls on a report or form i use the TAG property ie set the TAG to "visible" for all controls you wish to appear on the second page

when the report runs use


if Not IsFirstPage then
    for each ctl in object.controls
         ctl.visible=(ctl.tag="visible")
    next
end if

0
 
jpaulfainAuthor Commented:
harfang:

Thank you for the best solution to my question.  Could you tell me the best way to add word processing ability to a memo field?  That would allow font choices, bold, underline, italic, etc on the fly?  Also, is spell checking possible when using a richtext memo field?

Thank you,
paul fain

0
 
harfangCommented:
Hello paul fain

I'm glad you found my comments useful.

You are now asking a new question. The best thing would be to:
 • search this site for similar questions, and/or
 • create a new question with an explicit title.

I would not have answered, because I don't have any real experience with the RichTextBox control. If I remember correctly, you need to program each feature with either a custom toolbar or buttons in a subform. I'm sure there are several examples out there, but I don't know them.

With a new question, you will attract the attention of experienced experts.

Good luck in any case!
(°v°)
0
 
jpaulfainAuthor Commented:
Thank you.....
0

Featured Post

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.

  • 13
  • 8
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now