Control detail section height size in Ac2007

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.

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 procedu
Me.pgEject.Visible = (txtDtlNum = txtGrpCount - 2)

--
Marsh
MVP [MS Access]

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 procedu
Me.pgEject.Visible = (txtDtlNum = txtGrpCount - 2)

--
Marsh
MVP [MS Access]
.

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 procedu
Me.pgEject.Visible = (txtDtlNum = txtGrpCount - 2)

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 procedu
Me.pgEject.Visible = (txtDtlNum = txtGrpCount - 2)
.