Selectively print or bypass a subform.

phmurphy
phmurphy used Ask the Experts™
on
I am using a subform to create a 2 page report.  The subform is at the end of the first form.  The forms print out pictures that can vary from 4 to 8.  There are 4 photos on the first page and 4 on the second.  I would like the second page to be skipped when printing if there aren't more than 4 photos.  What code and where do I put it to bypass printing the second page (subform) if number of photos <= 4.  I don't need code to figure out how many photos there are, just need code for the bypass.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you are really printing a form, then you have no control over what gets printed.

To have such control you must use a proper report.

Author

Commented:
HI peter57r,

Sorry, I am printing reports.  I just copied the form controls to a report and I sometimes confuse the terminology.  I have a 2 page  "report'  that includes a subreport as the second page.  The subreport is a full page of photos just like the first page of the report.  

How do I selectively not print the second page of the report?

Pat
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Can you please state your Access version please?
Since we do not have a sample to go by, I will give you a generic solution.

<I don't need code to figure out how many photos there are>
1. ...if you say so, ...but I will presume that somewhere you have logic to decide when the number of pics is <= 4. , and storing this result somewhere...
Because I am not clear on how any code we post will be able determine this on it's own...?
So let's presume that you have a public Boolean variable named "blnpubWillBypass"
When it is TRUE, then number of pics is <= 4, and only the first page will be printed.
(if the Variable is False, then both pages will print)
2. Are you quite sure the number of pages will *always* be 2?
Suppose that the first page (or the second page for that matter) contains more data than can fit on one page?

In any event...

Print the Report from a button of a form.
The code will look something like this:

Dim strRptName As String
strRptName = "YourReportName"
    'Open the report (briefly) in Print Preview
    DoCmd.OpenReport strRptName, acViewPreview
        'If the the variable is True, then only print the first page
        If blnpubWillBypass = True Then
            DoCmd.PrintOut acPages, 1, 1
        'If the Variable is false Print all (both) pages
        ElseIf blnpubWillBypass = False Then
            DoCmd.PrintOut acPrintAll
        End If
    'Close the report
    DoCmd.Close acReport, strRptName


So as you can see the basic code to print only 1 page (in this specific case) is:

Dim strRptName As String
strRptName = "YourReportName"
        DoCmd.OpenReport strRptName, acViewPreview
        DoCmd.PrintOut acPages, 1, 1
        DoCmd.Close acReport, strRptName


;-)

JeffCoachman

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks for the response.  I won't be able to test this until tomorrow, but I don't think it will work because I have many record to print, and each record will have either one or two pages, so printall wouldn't work.  I was thinking that some code could be placed in the OnPrint event (or some other event) of the subreport detail, that would look at the variable and decide to print or move on.

I have a field that is a simple number of the photos that is the variable used in the logic.

So I have ReportPage1, and a subreport called ReportPage2.  I send ReportPage1 to the Adobe pdf printer and something triggers the logic in the ReportPage2 to print or bypass, then move on to the next record.

Pat

Author

Commented:
I have probably confused this a bit so let me try to clarify.  I have the subreport embedded at the end of the first page of the report so when I send page 1 to the printer, page 1, then page 2 for all of the records gets automatically sent to the printer.  If I have 10 records, 20 pages get sent automatically.  I don't really send the records one at a time.
MIS Liason
Most Valuable Expert 2012
Commented:
<but I don't think it will work because I have many record to print, and each record will have either one or two pages, so printall wouldn't work.>
Well, ... you did not make this inherently clear in your original post.....
You stated only:
  <I am using a subform to create a 2 page report.>

So then let's be clear on my concern (which you also did not address)
Are you quite sure the number of pages will *always* be 2?
Suppose that the first page (or the second page for that matter) contains more data than can fit on one page?

...In any even, here is a sample.

Open rptCustomers main.

As you scroll through the records, notice that CustomerID's ANATR, BOLID, et al, ...have no second page.
This is because I set the code to hide the manual page break and the subreport if the count of records in the subreport was lass than 5.

(Note: If your Key field is Numeric change my code to:
   If DCount("OrderID", "tblOrders", "CustomerID=" & Me.CustomerID) < 5 Then

Study this sample carefully, I am sure that you will be able to adapt this to work in your database.

;-)

JeffCoachman


Access-EEQ26525924OnlyPrintSubRe.mdb

Author

Commented:
Thanks, I will take a look at your code.   My first look indicates that you want me to look at the code associated with the on format event of the detail in rptCustomersMain.

The ultimate report can be 2 to infinity pages long since there will be 1 or 2 pages per record, and I can have many many records.  There will never be an issue about the data extending beyond either page since there are just 4 photos on each page and no variable number of items per page.  Each record has the file names of 4 to 8 photo files stored so only 1 to 2 pages would be required for each record.  It isn't like there could be a list of varying length.

Author

Commented:
Here is the code I used.  It almost works.  What I get is a blank page for the second page instead of no page getting printed.
I will strip down my database and attach something to show you in the next comment.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Photo < 5 Then
        Me.PhotoMonitoringPage2.Visible = False
       Me.PageBreak4.Visible = False
    Else
        Me.PhotoMonitoringPage2.Visible = True
       Me.PageBreak4.Visible = True
    End If
End Sub

Author

Commented:
Here is the stripped down database and a small image file (None.bmp) that you will need to copy to C:\.  Open PhotoMonitoring and then page down.  Note that there are blank pages instead of no page for records where there are only 4 or fewer photos.  
rox-pla-vegetation-2010---Copy.accdb
None.bmp

Author

Commented:
Never mind,  I just had to adjust the position of the subform and page break.  NOw it all works perfectly.  Thanks.
Pat

Author

Commented:
Thanks,
This saves a lot of electrons and paper.
Pat
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)

Great, I'm glad I could help.

This was actually an interesting little question.

;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial