Hi,

I am new to Access and have created most of my Report/Database reading these
forums. I am using Access 2007 and I am having problems with my report
format, specifically the details section. Currently, my report is linked to
a table and modeled after a Word document. I have a page header, a group
header 'repeated in every section', a detail section, a group footer, a page
footer, and a report footer with Force New Page set to 'Before Section'.

My problem is in the detail section. The detail section is grouped by Field
'Doc#' set at 'keep whole group together on one page' then sorted by Field
'Item#'. Each Item# is associated with rows autofilled by the database.

I would like th number of rows on the same page as the group footer to
always equal to 2. For example, if I have 7 Item#s, then I would like 5 rows
autofilled on previous pages and the last 2 on a separate page with the group
footer. The number of Item#s vary with each associated Doc#.

Thank you in advance.

Hi Marshall,

Thank you for your help. The code did not produce the response I wanted.
The page break is separating each row onto its own page. The last page also
only has the 7th row.

Please let me know what information I can provide you to remedy the problem.

Thank you.

"Marshall Barton" wrote:

> VBNovice10 wrote:
> >I am new to Access and have created most of my Report/Database reading these
> >forums. I am using Access 2007 and I am having problems with my report
> >format, specifically the details section. Currently, my report is linked to
> >a table and modeled after a Word document. I have a page header, a group
> >header 'repeated in every section', a detail section, a group footer, a page
> >footer, and a report footer with Force New Page set to 'Before Section'.
> >
> >My problem is in the detail section. The detail section is grouped by Field
> >'Doc#' set at 'keep whole group together on one page' then sorted by Field
> >'Item#'. Each Item# is associated with rows autofilled by the database.
> >
> >I would like th number of rows on the same page as the group footer to
> >always equal to 2. For example, if I have 7 Item#s, then I would like 5 rows
> >autofilled on previous pages and the last 2 on a separate page with the group
> >footer. The number of Item#s vary with each associated Doc#.
>
>
> Add a text box (named txtGrpCount) to the group header
> section and set its expression to =Count(*)
>
> Then add a text box (named txtDtlNum) to the detail section.
> Set its expression to =1 and its RunningSum property to
> Over Group.
>
> Now, you can tell which detail is being processed and how
> close you are to the last one in the detail section's Format
> event procedure.
>
> Add a page break control (named pgEject) at the very top of
> the detail section.
>
> Finally, add a line of VBA code to the detail section's
> Format event procedure:
> Me.pgEject.Visible = (txtDtlNum = txtGrpCount - 2)
>
> --
> Marsh
> MVP [MS Access]
> .
>

Thank you,

Everything worked out as planned. The security settings had disabled
macros. I reset the settings to enable macros and your VBA code worked
perfectly.

Thank you again.

"Marshall Barton" wrote:

> You must have done something unusual. Maybe you added two
> page break controls? Maybe the header and or detail text
> boxes are not set as I said? Maybe you have ForceNewPage
> set for the detail section? Maybe the line of VBA is wrong?
> Or maybe ???
>
> Without more specific information, all I can say is to
> double check everything.
> --
> Marsh
> MVP [MS Access]
>
>
> VBNovice10 wrote:
> >Thank you for your help. The code did not produce the response I wanted.
> >The page break is separating each row onto its own page. The last page also
> >only has the 7th row.
> >
> >Please let me know what information I can provide you to remedy the problem.
> >
> >"Marshall Barton" wrote:
> >> VBNovice10 wrote:
> >> >I am new to Access and have created most of my Report/Database reading these
> >> >forums. I am using Access 2007 and I am having problems with my report
> >> >format, specifically the details section. Currently, my report is linked to
> >> >a table and modeled after a Word document. I have a page header, a group
> >> >header 'repeated in every section', a detail section, a group footer, a page
> >> >footer, and a report footer with Force New Page set to 'Before Section'.
> >> >
> >> >My problem is in the detail section. The detail section is grouped by Field
> >> >'Doc#' set at 'keep whole group together on one page' then sorted by Field
> >> >'Item#'. Each Item# is associated with rows autofilled by the database.
> >> >
> >> >I would like th number of rows on the same page as the group footer to
> >> >always equal to 2. For example, if I have 7 Item#s, then I would like 5 rows
> >> >autofilled on previous pages and the last 2 on a separate page with the group
> >> >footer. The number of Item#s vary with each associated Doc#.
> >>
> >>
> >> Add a text box (named txtGrpCount) to the group header
> >> section and set its expression to =Count(*)
> >>
> >> Then add a text box (named txtDtlNum) to the detail section.
> >> Set its expression to =1 and its RunningSum property to
> >> Over Group.
> >>
> >> Now, you can tell which detail is being processed and how
> >> close you are to the last one in the detail section's Format
> >> event procedure.
> >>
> >> Add a page break control (named pgEject) at the very top of
> >> the detail section.
> >>
> >> Finally, add a line of VBA code to the detail section's
> >> Format event procedure:
> >> Me.pgEject.Visible = (txtDtlNum = txtGrpCount - 2)
> .
>