?
Solved

Create a page 2 of a report if sub report query count >3

Posted on 2006-04-28
9
Medium Priority
?
376 Views
Last Modified: 2008-02-01
Good day.

I have a report built of sub-reports.  There is a sub report that will list items from a query.  
If the count of records in the query is more than three then I want a page two created with records 4-6 and so on.

So, If I have 11 items I should end up with four copies of the report.. each with three entries and the last with 2.

so, my question is this.  What is an easy and clean method of implementing this?

thank you for your time.
K
0
Comment
Question by:chad
  • 4
  • 4
8 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 16566537
Hello,

The following module will force a page break after each third detail section:

-----------------------------------------------------------------------------------------------
Option Explicit

Dim gintCounter As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    gintCounter = gintCounter + 1
    Me.Detail.ForceNewPage = IIf(gintCounter Mod 3 = 0, 2, 0)
End Sub
-----------------------------------------------------------------------------------------------

You can use this on a subform.

Hope this helps,
(°v°)
0
 
LVL 11

Author Comment

by:chad
ID: 16566655
thanks for the quick reply.  I will have to test it out tomorrow.  
0
 
LVL 11

Author Comment

by:chad
ID: 16608500
okay.  It works.. sorta.
I have the vba in the event procedure for the details section of the subreport.

When I do that it causes a page break in the entire main report.
i.e. it does not include the remaining data.  

Is there a way to make the main report do a page break based on the three item count?
thanks,
K
0
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!

 
LVL 58

Expert Comment

by:harfang
ID: 16609868
Hello,

Well, does it need to be a subreport? Can't you use the main report to do that? E.g.

+----------------------
 | page header
 | to fill the top
 | portion of the
 | page
+----------------------
 | 1. Aaa aaa
 | 2. Bbb bbb
 | 3. Ccc ccc
+----------------------
 | page footer
 | for the rest
+----------------------

This method create a page break in the subreport. That page break is propagated to the main report. After that, the main report will only print the page footer, nothing else, quite naturally.

You seem to expect something else. Can you be more precise? What sort of "page break" are you imagining for the main report?

You might want to look at a similar thread: http:/Q_21834220.html

Cheers!
(°v°)
0
 
LVL 11

Author Comment

by:chad
ID: 16610216
thanks,

I came to the same conclusion...sorta.  I only have the subreport in the detail section.  every thing else is in a footer/header.
Now I want to ensure that each page has three... no more no less.

this is the general idea

itemlist  1) item 1                name  1) blah
            2) item 2                          2) blah again  
            3)  itme 3                         3) blah blah

I have all of this on a subreport but when I have only one item, I only get one line.  I still want three lines to show up and have the last two blank.
I could use TOP 3 as you suggested in the other thread but I think that will give me the same results I have now.

thanks.
K
0
 
LVL 58

Expert Comment

by:harfang
ID: 16610523
It would.

If you use the page footer for the remaining information, it's aligned to the bottom marging, so that you would end up with blank space where the two additional lines could have been.

If you need to actually print something there, it's a little more comples, and requires VB again.

I created a small demo module for a report that fills each page with 40 lines. Change the constant and you can use that to print three lines: http:/Q_21617721.html

See if that works, or we can adapt it for you.

Good luck!
(°v°)
0
 
LVL 11

Author Comment

by:chad
ID: 16611547
Thanks, I will play and hopefuly learn tomorrow
0
 
LVL 58

Expert Comment

by:harfang
ID: 16614690
Tell me how it goes. It might take some tweaking.
(°v°)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

609 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