Populate many reports with the same code

[Guest]

Hi,

I have 60 reports to which I need to add the same procedures. I got the
code below from an old forum post by Fred. It allows page updating to run in
sequence in a chain of reports. What I would like to do is write the same
procedures in the same events over and over in each report.

-----------
1. Now in each report, Dim a variable in the declarations section:

Option Compare Database
Option Explicit
Dim intLastPage as Integer

2. Code each report's Open event:
intLastPage = DLookUp("intPageNumber","tblPage")

3. Code each Report's Report Header Format event:
[Page] = [Page] + intLastPage

4. Code each Report's Report Footer Print event:

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";"
Docmd.SetWarnings True

 

[Guest]

What is your question?

 

[Marshall Barton]

I have 60 reports to which I need to add the same procedures. I got the
code below from an old forum post by Fred. It allows page updating to run in
sequence in a chain of reports. What I would like to do is write the same
procedures in the same events over and over in each report.

-----------
1. Now in each report, Dim a variable in the declarations section:

Option Compare Database
Option Explicit
Dim intLastPage as Integer

2. Code each report's Open event:
intLastPage = DLookUp("intPageNumber","tblPage")

3. Code each Report's Report Header Format event:
[Page] = [Page] + intLastPage

4. Code each Report's Report Footer Print event:

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";"
Docmd.SetWarnings True

That can be done by using a procedure that open each report
in design view and then uses the various methods of the
report's Module object (see VBA Help).

Before going into all the details, could you explain why you
need so many reports and then to join them like that. If
the data is related, most of the time Sorting and Grouping,
maybe with a subreport, takes care of it.

 

[Guest]

Hi, Marsh.

The large number of reports is the result of an analysis of survey responses
shown as pivot charts, tables and text information for each question in the
survey. I am compiling the appendix to hold these. I have the choice of
chaining the reports together as a paper document or placing them all in one
report using section grouping which gives me the option of a PDF print. My
ideal would be a PDF but the route to get there is not important, only the
most efficient and manageable.

My thinking up to now is that there are pros and cons to each method as I
understand it. I list these below for chained reports and then grouped in
one single report. I would value any guidance to help me through this.

Chained reports:-
Pros:
Individual reports are easier to control and identify within such a large
appendix.
I can have sequential page numbering if I insert the same coding into every
Access report (hence the original post on the forum).
Cons:
Don't know if there is a way to get every report placed in order in the same
single PDF file (don't have Adobe, only the Acrobat reader).
I am likely to end up with a few or several blank, half or three quarter
pages throughout the main report because each report may not finish neatly at
or near the bottom of a page.

Grouped in one report:-
Pros:
Minimises blank space in the document.
Can print to one PDF file.
Cons:
There may be an issue with how many sections I can create in one ACC2003
report - I don't know if there is a limit.
Equally, I have come up against a limit on the vertical size allowed inside
a section, so there may be a finite amount of space for any one report - I
just don't know.
Also, I don't know if there is a limit to the number of pages I can print to
a PDF file or if certain PDF print drivers are better than others. I have
downloaded S Lebans’ A2000SnapshotToPDFver751.zip which prints directly to
PDF, but need to check if this has any kind of page limit.

John
--
John Whyte

I have 60 reports to which I need to add the same procedures. I got the
code below from an old forum post by Fred. It allows page updating to run in
sequence in a chain of reports. What I would like to do is write the same
procedures in the same events over and over in each report.

-----------
1. Now in each report, Dim a variable in the declarations section:

Option Compare Database
Option Explicit
Dim intLastPage as Integer

2. Code each report's Open event:
intLastPage = DLookUp("intPageNumber","tblPage")

3. Code each Report's Report Header Format event:
[Page] = [Page] + intLastPage

4. Code each Report's Report Footer Print event:

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";"
Docmd.SetWarnings True

That can be done by using a procedure that open each report
in design view and then uses the various methods of the
report's Module object (see VBA Help).

Before going into all the details, could you explain why you
need so many reports and then to join them like that. If
the data is related, most of the time Sorting and Grouping,
maybe with a subreport, takes care of it.

 

[Marshall Barton]

The large number of reports is the result of an analysis of survey responses
shown as pivot charts, tables and text information for each question in the
survey. I am compiling the appendix to hold these. I have the choice of
chaining the reports together as a paper document or placing them all in one
report using section grouping which gives me the option of a PDF print. My
ideal would be a PDF but the route to get there is not important, only the
most efficient and manageable.

My thinking up to now is that there are pros and cons to each method as I
understand it. I list these below for chained reports and then grouped in
one single report. I would value any guidance to help me through this.

Chained reports:-
Pros:
Individual reports are easier to control and identify within such a large
appendix.
I can have sequential page numbering if I insert the same coding into every
Access report (hence the original post on the forum).
Cons:
Don't know if there is a way to get every report placed in order in the same
single PDF file (don't have Adobe, only the Acrobat reader).
I am likely to end up with a few or several blank, half or three quarter
pages throughout the main report because each report may not finish neatly at
or near the bottom of a page.

Grouped in one report:-
Pros:
Minimises blank space in the document.
Can print to one PDF file.
Cons:
There may be an issue with how many sections I can create in one ACC2003
report - I don't know if there is a limit.
Equally, I have come up against a limit on the vertical size allowed inside
a section, so there may be a finite amount of space for any one report - I
just don't know.
Also, I don't know if there is a limit to the number of pages I can print to
a PDF file or if certain PDF print drivers are better than others. I have
downloaded S Lebans’ A2000SnapshotToPDFver751.zip which prints directly to
PDF, but need to check if this has any kind of page limit.

I would think that the ability to generate a single output
would be a very important consideration. If the layout of
each of your separate reports is very similar, then I would
try really hard to avoid creating and maintaining 60
(nearly?) identical reports.

FYI, each section in a report's design can have a maximum
height of 22 inches with a limit of 200 inches for the total
of all sections. You can have a maximum of 10 groups in a
report, each with a header and footer section for a total of
21 repeating sections not counting the report/page
header/footer sections.

 

[Guest]

Thanks, Marsh.

I now have all the form based charts and tables and reports as sub-objects
in one report, "Appendix", using your suggestion. This takes one report
header and nine section headers, no footers used. That's great,
thanks...............

However, I noticed, as I built "Appendix", that a peculiar effect has
happened, which to my, ignorant eye appears to be random. The effect is that
some charts are repeated on the next page, and the following pages, for
several more pages. As they repeat they gradually change vertical height on
the Appendix report's pages. That is, the same chart (or table) repeats on
several following pages but changes height, often on the at first, next page
too tall and, then, getting progressively smaller on the following pages.

I've placed page beaks at strategic points but to no effect. But I don't
really know what's going on: Basically I'm just guessing!!

Do you know what causes this?

Thanks, again.

John

 

[Marshall Barton]

I now have all the form based charts and tables and reports as sub-objects
in one report, "Appendix", using your suggestion. This takes one report
header and nine section headers, no footers used. That's great,
thanks...............

However, I noticed, as I built "Appendix", that a peculiar effect has
happened, which to my, ignorant eye appears to be random. The effect is that
some charts are repeated on the next page, and the following pages, for
several more pages. As they repeat they gradually change vertical height on
the Appendix report's pages. That is, the same chart (or table) repeats on
several following pages but changes height, often on the at first, next page
too tall and, then, getting progressively smaller on the following pages.

I don't have enough experience with charts to be able to
help with their problems. I have a vague memory of someone
suggesting that the section's Format event be used to set
the chart's row source property, but the details elude me.
Try searching the Google archives for responses from folks
with more chart experience. If that doesn't turn up any
useful ideas, try posting a new question.

 

[Guest]

Thanks. Will do, March.

John
--
John Whyte

I don't have enough experience with charts to be able to
help with their problems. I have a vague memory of someone
suggesting that the section's Format event be used to set
the chart's row source property, but the details elude me.
Try searching the Google archives for responses from folks
with more chart experience. If that doesn't turn up any
useful ideas, try posting a new question.

 

[Guest]

Thanks, will do, Marsh.

John
--
John Whyte

I don't have enough experience with charts to be able to
help with their problems. I have a vague memory of someone
suggesting that the section's Format event be used to set
the chart's row source property, but the details elude me.
Try searching the Google archives for responses from folks
with more chart experience. If that doesn't turn up any
useful ideas, try posting a new question.