Limiting results per page

[Michael]

I have a report that has a subreport in the footer. I need the report
capped at 28 rows per page because the subreport (rotated 90 degrees with
the rotated lable control) is going to get pushed out at 29 rows. Also 28
rows is what we want for the deposit slip. Regardless if we want it or not
though sometimes we are going to have more than 28 fields which will wreck
the way we want the report to look. How can I either limit the report to 28
rows or better yet make the 29th row start row 1 of next page (keeping in
mind though that I need the subreport to not move to the 2nd page)?

 

[krissco]

Oh. I should have warned you. You will need to reset that recordCount
if your report has more than one page. I suppose that would be
possible by placing the recordCount higher in the module and resetting
it via another section's events?


Good Luck,

-Kris

 

[krissco]

I have a report that has a subreport in the footer. I need the report
capped at 28 rows per page because the subreport (rotated 90 degrees with
the rotated lable control) is going to get pushed out at 29 rows. Also 28
rows is what we want for the deposit slip. Regardless if we want it or not
though sometimes we are going to have more than 28 fields which will wreck
the way we want the report to look. How can I either limit the report to 28
rows or better yet make the 29th row start row 1 of next page (keeping in
mind though that I need the subreport to not move to the 2nd page)?

Here is some code you can use to limit the number of records in a
report section. I don't know how to wrap the rows onto another page -
only truncate them. Place the sub in your main report module:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static recordCount As Integer
recordCount = recordCount + 1
If recordCount >= 28 Then Me.Detail.Visible = False
End Sub

-Kris

 

[Marshall Barton]

Here is some code you can use to limit the number of records in a
report section. I don't know how to wrap the rows onto another page -
only truncate them. Place the sub in your main report module:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static recordCount As Integer
recordCount = recordCount + 1
If recordCount >= 28 Then Me.Detail.Visible = False
End Sub

That is not a reliable technique. The detail section may be
processed multiple times, which will throw you count off by
an arbitrary amount. Use a running sum text box instead.

 

[Michael]

Is there a way to make:

Section(0).ForceNewPage Every 28 records?

 

[Marshall Barton]

Is there a way to make:

Section(0).ForceNewPage Every 28 records?

Add a text box named txtLineNum to the detail section. Set
its control source to =1 and RunningSum property to Over
Group.

Then add a line of code to the detail section's Format
event:
Me.Section(0).ForceNewPage=IIf(txtLineNum Mod 28=0,1,0)

If you are using grouping, add a text box named
txtDetailCount to the group header. Set its control source
to =Count(*) and change the line of code to:
Me.Section(0).ForceNewPage = IIf(txtLineNum Mod 28 = 0 _
And txtLineNum < Me.txtDetailCount, 1, 0)

 

[krissco]

I was not putting it as code. It worked now, sort of.

Now my issue is the footer is jumping to the 2nd page as well.

Is there any way to make it that the detail goes to 28 records but keeps the
report footer on the first page (which shows the same 28 records but
different fields) and then puts the next 28 records on the 2nd page, etc...."Marshall Barton" <[email protected]> wrote in message

The report footer will always go to the last page.

Do the controls in your report work if you move them to the page
footer? If so, you can place it there and hide the page footer (I
assume) on pages > 1 (since you only want your subreport on the first
page).

-Kris

 

[Michael]

This will probably seem stupid to you but am I actually typing Me. or does
that stand for something because other code that I have tried starting with
Me. keeps causing errors.

 

[Michael]

I was not putting it as code. It worked now, sort of.

Now my issue is the footer is jumping to the 2nd page as well.

Is there any way to make it that the detail goes to 28 records but keeps the
report footer on the first page (which shows the same 28 records but
different fields) and then puts the next 28 records on the 2nd page, etc....

 

[Michael]

I put the subreport in the page footer and now I'm getting better results.
Only problem is I have to show a Posting Amount total per report page
because each page if 28 records are filled will be a deposit record on our
side and a deposit slip for the bank.

In the page footer the =Sum([PostingAmt]) fails.

I really need a way to leave that subreport and totaling fields in the
report footer and have the first 28 records for the regular report and the
first 28 records in the subreport (report footer) display on one page and
then have the 29th, 30th, etc... (up to another 28 and so on) print on the
2nd page, 3rd page, 4th pg, etc...

What I have is a list of deposits from our company on top (as a regular
report). In the report footer

 

[Michael]

Another thing I am going to run into is that the total fields have to total
per 28 records so I think I am going to have to code that with a running
sum.

 

[Michael]

Is there a way to force the 28 fields borders if there are less than 28
fields?

 

[Marshall Barton]

Nothing like jumping in the deep end with such a complex
report.

for page total calculation see:
http://support.microsoft.com/kb/296249/en-us

 

[Marshall Barton]

You can use the Page event to draw lines anywhere on the
page. This is fairly easy if you are not using
CanGrow/CanShrink.

t = x * 1440 + Me.Section(3).Height _
+ Me.Section(5).Height _
+ . . . 'repeat for each group header
' x is the top margin in inches
d = Me.Section(0).Height

For k = 0 To 27
Line (0,t + k * d) - Step(Me.Width, d), vbBlack, B
Next K

 

[Michael]

Tell me about it.

Not like when I was in school in 2001 and we made VB do "Hello World" (ha
ha).

Nothing like jumping in the deep end with such a complex
report.

for page total calculation see:
http://support.microsoft.com/kb/296249/en-us
--
Marsh
MVP [MS Access]

Another thing I am going to run into is that the total fields have to
total
per 28 records so I think I am going to have to code that with a running
sum.