Subreport format

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I asked a similar question earlier, but perhaps there was
too much detail. Is it possible, when a subreport extends
to a second page, for the second page to have any heading
other than the main report's page header? It seems that a
subreport page header will not appear on a second page
except when the subreport is opened alone, which of course
defeats its purpose.
I should mention that the main report describes a training
session, and the subreport is the attendance at the
session. Each session record has a PK, by which I have
grouped the records. By forcing a new page between
sections, I can get a long subreport to extend to a second
page, but the only header I can get to appear on that page
is the main report's page header. The subreport's group
header (a set of column labels for the records in the
detail section) appears only at the start of the subreport
(in the main report's detail section).
I have tried every combination I could think of for group
headers, forcing new pages, grouping options, and so
forth, but I cannot find a way for the column headings to
appear on the second page. If what I am attempting is
impossible, please let me know so that I can stop
imagining that Microsoft considered this situation and has
a way of making it work. I have Access 2000.
 
Bruce said:
I asked a similar question earlier, but perhaps there was
too much detail. Is it possible, when a subreport extends
to a second page, for the second page to have any heading
other than the main report's page header? It seems that a
subreport page header will not appear on a second page
except when the subreport is opened alone, which of course
defeats its purpose.
I should mention that the main report describes a training
session, and the subreport is the attendance at the
session. Each session record has a PK, by which I have
grouped the records. By forcing a new page between
sections, I can get a long subreport to extend to a second
page, but the only header I can get to appear on that page
is the main report's page header. The subreport's group
header (a set of column labels for the records in the
detail section) appears only at the start of the subreport
(in the main report's detail section).
I have tried every combination I could think of for group
headers, forcing new pages, grouping options, and so
forth, but I cannot find a way for the column headings to
appear on the second page. If what I am attempting is
impossible, please let me know so that I can stop
imagining that Microsoft considered this situation and has
a way of making it work. I have Access 2000.
Click to expand...


If I understand right, you already have the column headings
in a group header section. If so, just set it's
RepeatSection property to Yes.
 
Thanks, that was the missing link. What I had before was
the training session information (subject, instructor,
department, etc.) in the main report's detail section.
Grouping was by the Session PK. The subreport, listing
attendance, date, etc. was also in the detail section.
The column labels were in the subreport's report header.
(I could not get the subreport's page header to show up at
all). After reading your reply, I moved the session
information to the group header, along with the column
labels, and changed the Repeat Section property to Yes.
The subreport remained in the main report's detail
section. That did what I needed. I would have preferred
that only selected information about the session appear on
the second page, similar to how a page header can be made
not to appear with the report header, but that level of
refinement does not seem to be readily available for group
headers. However, it is not really a problem. Your reply
allows me to move past formatting problems so that I can
get back to work on information management, which is after
all the whole point of the project.
 
Bruce said:
Thanks, that was the missing link. What I had before was
the training session information (subject, instructor,
department, etc.) in the main report's detail section.
Grouping was by the Session PK. The subreport, listing
attendance, date, etc. was also in the detail section.
The column labels were in the subreport's report header.
(I could not get the subreport's page header to show up at
all). After reading your reply, I moved the session
information to the group header, along with the column
labels, and changed the Repeat Section property to Yes.
The subreport remained in the main report's detail
section. That did what I needed. I would have preferred
that only selected information about the session appear on
the second page, similar to how a page header can be made
not to appear with the report header, but that level of
refinement does not seem to be readily available for group
headers. However, it is not really a problem. Your reply
allows me to move past formatting problems so that I can
get back to work on information management, which is after
all the whole point of the project.
Click to expand...


If you want the group header to look different on the first
occurance and subsequent occurances, then you can use VBA
code in the group header's Format event to make some of the
header controls invisible and use CanShrink to reduce the
space the header occupies.

First you need a way to determine when the header is
starting the group and when it is just repeating on
additional pages of the group. Then is easily done by
adding an invisible text box named txtCounter to the detail
section. Set it's control source expression to =1 and
RunningSum property to Over Group.

Then you can make the session text boxes visible or not as
needed:

Dim bolFirst As Boolean
bolFirst = (Me.txtCounter = 1)
Me.sessionA.Visible = bolFirst
. . .
Me.sessionZ.Visible = bolFirst

Make sure the session text box's and the header section's
CanShrink properties are all set to Yes.
 
Thanks again for taking the time to help. I have to say
that something about the suggestion is not making sense to
me. I tested it a bit, and could not get the number in
txtCounter to increment (I made it visible to test it).
Perhaps if I put the subreport into the group header
rather than the detail section, so that txtCounter is the
only thing in the detail section? As it is now, with the
subreport in the detail section, txtCounter only shows up
once, no matter the length of the subreport. In other
words, it shows up at the end of the subreport with the
number 1 in it, no matter how many pages the subreport
occupies. For now other matters are demanding my
attention, so I will not have a chance to experiment
further for at least a few days, but I have saved the
suggestion to my personal Help files for reference when I
can get back to it.
 
Bruce said:
Thanks again for taking the time to help. I have to say
that something about the suggestion is not making sense to
me. I tested it a bit, and could not get the number in
txtCounter to increment (I made it visible to test it).
Perhaps if I put the subreport into the group header
rather than the detail section, so that txtCounter is the
only thing in the detail section? As it is now, with the
subreport in the detail section, txtCounter only shows up
once, no matter the length of the subreport. In other
words, it shows up at the end of the subreport with the
number 1 in it, no matter how many pages the subreport
occupies. For now other matters are demanding my
attention, so I will not have a chance to experiment
further for at least a few days, but I have saved the
suggestion to my personal Help files for reference when I
can get back to it.
Click to expand...


Time out. You seem to have placed the running sum text box
in the main report, when I had intended it to be in the
subreport. From the way I read your question, there was no
changes to make to the main report. I thought the group
header that you're trying to manipulate is in the subreport.
Was that correct or is it a main report header of some kind?
--
Marsh
MVP [MS Access]


 
The main report group header is the training session
information (subject, instructor, etc.). Each session is
a record in the sessions table. The main report detail
section is a subreport that lists attendance at the
training session. The subreport lists name and date of
everbody who attended the training session. Each
name/date is a separate record in a junction table that is
between the sessions table and an employees table. The
main report is grouped by the PK (SessionID) from the
sessions table. At first I did not group at all, but
instead inserted a page break after the subreport. It
looks something like this:

Main Report Page Header
___________________________

Main Report Group Header
Subject
Instructor
Department
etc.

NAME DATE (column labels for the subreport)
____________________________

Main Report Detail Section
Subreport
Name Date
Name Date
______________________

Subreport Report Footer
______________________________

Main Report Page Footer

Repeat Section is set to Yes in the group header. When I
print I get the Main Report's page header and Group Header
on each page. The subreport's report footer appears at
the end of the subreport, as intended. The Main Report's
page footer appears on each page, as intended. When the
list of attendees runs onto a second page, the second page
looks just like the first.

I have a few other settings such as forcing a page break
to make the formatting work properly.

This might be a complete hash from your point of view, but
it seems to work. One thing I might change is to have
less information at the top of the second page when two
pages are necessary to list all of the attendees at a
training session. Maybe the second page would just
contain "[Subject], continued" or something like that,
then the column headings for Name and Date, and a
continuation of the attendance listing.
I sometimes wonder how much detail to put into my
questions. In this case it seems that a bit more would
have helped.
-----Original Message-----
Bruce said:
Thanks again for taking the time to help. I have to say
that something about the suggestion is not making sense to
me. I tested it a bit, and could not get the number in
txtCounter to increment (I made it visible to test it).
Perhaps if I put the subreport into the group header
rather than the detail section, so that txtCounter is the
only thing in the detail section? As it is now, with the
subreport in the detail section, txtCounter only shows up
once, no matter the length of the subreport. In other
words, it shows up at the end of the subreport with the
number 1 in it, no matter how many pages the subreport
occupies. For now other matters are demanding my
attention, so I will not have a chance to experiment
further for at least a few days, but I have saved the
suggestion to my personal Help files for reference when I
can get back to it.
Click to expand...


Time out. You seem to have placed the running sum text box
in the main report, when I had intended it to be in the
subreport. From the way I read your question, there was no
changes to make to the main report. I thought the group
header that you're trying to manipulate is in the subreport.
Was that correct or is it a main report header of some kind?
--
Marsh
MVP [MS Access]


up
at appear
on
Click to expand...
.
Click to expand...
 
Now I know why the sorting/grouping dialog has a place for
field/expression. It had not occurred to me to sort by a
constant, but I think I see how that works. That could
come in very handy. It makes much more sense this way.
Thanks again. I have left the Session information
(Instructor, etc.) in the main report's group header,
grouped by SessionID (the PK). One thing I still wonder
about: The main report group header's On Format event is

Dim ctl As Control

For Each ctl In Controls
ctl.Visible = Not IsNull(ctl)
Next ctl

Me.txtDocRevision.Visible = Not ([DocID1] = "N/A")

I get an error "method or data member not found" for field
[DocID1]. The field name is correct. When I rem out the
line of code, it works fine. Any ideas?
By the way, if I try to put the session information into
the report detail with the same On Format event as
described above it bogs down, and I need to press Ctrl +
Break.
-----Original Message-----
This stuff should be handled in the subreport. Create a
group header section in the subreport based on a constant
expression such as =1. Set the header section's
RepeatSection to Yes.

Move the labels to this section and the apply the technique
I outlined before.
--
Marsh
MVP [MS Access]


The main report group header is the training session
information (subject, instructor, etc.). Each session is
a record in the sessions table. The main report detail
section is a subreport that lists attendance at the
training session. The subreport lists name and date of
everbody who attended the training session. Each
name/date is a separate record in a junction table that is
between the sessions table and an employees table. The
main report is grouped by the PK (SessionID) from the
sessions table. At first I did not group at all, but
instead inserted a page break after the subreport. It
looks something like this:

Main Report Page Header
___________________________

Main Report Group Header
Subject
Instructor
Department
etc.

NAME DATE (column labels for the subreport)
____________________________

Main Report Detail Section
Subreport
Name Date
Name Date
______________________

Subreport Report Footer
______________________________

Main Report Page Footer

Repeat Section is set to Yes in the group header. When I
print I get the Main Report's page header and Group Header
on each page. The subreport's report footer appears at
the end of the subreport, as intended. The Main Report's
page footer appears on each page, as intended. When the
list of attendees runs onto a second page, the second page
looks just like the first.

I have a few other settings such as forcing a page break
to make the formatting work properly.

This might be a complete hash from your point of view, but
it seems to work. One thing I might change is to have
less information at the top of the second page when two
pages are necessary to list all of the attendees at a
training session. Maybe the second page would just
contain "[Subject], continued" or something like that,
then the column headings for Name and Date, and a
continuation of the attendance listing.
I sometimes wonder how much detail to put into my
questions. In this case it seems that a bit more would
have helped.
-----Original Message-----
Bruce wrote:

Thanks again for taking the time to help. I have to say
that something about the suggestion is not making
Click to expand...
sense
to
me. I tested it a bit, and could not get the number in
txtCounter to increment (I made it visible to test it).
Perhaps if I put the subreport into the group header
rather than the detail section, so that txtCounter is the
only thing in the detail section? As it is now, with the
subreport in the detail section, txtCounter only shows up
once, no matter the length of the subreport. In other
words, it shows up at the end of the subreport with the
number 1 in it, no matter how many pages the subreport
occupies. For now other matters are demanding my
attention, so I will not have a chance to experiment
further for at least a few days, but I have saved the
suggestion to my personal Help files for reference
Click to expand...
when
I
can get back to it.


Time out. You seem to have placed the running sum text box
in the main report, when I had intended it to be in the
subreport. From the way I read your question, there
Click to expand...
was
no
changes to make to the main report. I thought the group
header that you're trying to manipulate is in the subreport.
Was that correct or is it a main report header of some kind?



-----Original Message-----
Bruce wrote:

Thanks, that was the missing link. What I had before
was
the training session information (subject, instructor,
department, etc.) in the main report's detail section.
Grouping was by the Session PK. The subreport, listing
attendance, date, etc. was also in the detail section.
The column labels were in the subreport's report
header.
(I could not get the subreport's page header to show up
at
all). After reading your reply, I moved the session
information to the group header, along with the column
labels, and changed the Repeat Section property to Yes.
The subreport remained in the main report's detail
section. That did what I needed. I would have
preferred
that only selected information about the session appear
on
the second page, similar to how a page header can be
made
not to appear with the report header, but that level of
refinement does not seem to be readily available for
group
headers. However, it is not really a problem. Your
reply
allows me to move past formatting problems so that I can
get back to work on information management, which is
after
all the whole point of the project.


If you want the group header to look different on the
first
occurance and subsequent occurances, then you can use VBA
code in the group header's Format event to make some of
the
header controls invisible and use CanShrink to reduce the
space the header occupies.

First you need a way to determine when the header is
starting the group and when it is just repeating on
additional pages of the group. Then is easily done by
adding an invisible text box named txtCounter to the
detail
section. Set it's control source expression to =1 and
RunningSum property to Over Group.

Then you can make the session text boxes visible or
Click to expand...
not
as
needed:

Dim bolFirst As Boolean
bolFirst = (Me.txtCounter = 1)
Me.sessionA.Visible = bolFirst
. . .
Me.sessionZ.Visible = bolFirst

Make sure the session text box's and the header section's
CanShrink properties are all set to Yes.
.
Click to expand...
Click to expand...

.
Click to expand...
 
Bruce said:
Now I know why the sorting/grouping dialog has a place for
field/expression. It had not occurred to me to sort by a
constant, but I think I see how that works. That could
come in very handy. It makes much more sense this way.
Thanks again. I have left the Session information
(Instructor, etc.) in the main report's group header,
grouped by SessionID (the PK). One thing I still wonder
about: The main report group header's On Format event is

Dim ctl As Control

For Each ctl In Controls
ctl.Visible = Not IsNull(ctl)
Next ctl

Me.txtDocRevision.Visible = Not ([DocID1] = "N/A")

I get an error "method or data member not found" for field
[DocID1]. The field name is correct. When I rem out the
line of code, it works fine. Any ideas?
Click to expand...

Not all controls have a value to test for Null (e.g.
labels). You can test for the type of control:

For Each ctl In Me.Controls
If ctl.ControlType = acTextbox Then
ctl.Visible = Not IsNull(ctl)
End If
Next ctl


By the way, if I try to put the session information into
the report detail with the same On Format event as
described above it bogs down, and I need to press Ctrl +
Break.
Click to expand...

I don't know why that would happen. Maybe the revised
procedure will work better (certainly, you did not want to
make the subreport invisible).
--
Marsh
MVP [MS Access]


-----Original Message-----
This stuff should be handled in the subreport. Create a
group header section in the subreport based on a constant
expression such as =1. Set the header section's
RepeatSection to Yes.

Move the labels to this section and the apply the technique
I outlined before.
--
Marsh
MVP [MS Access]


The main report group header is the training session
information (subject, instructor, etc.). Each session is
a record in the sessions table. The main report detail
section is a subreport that lists attendance at the
training session. The subreport lists name and date of
everbody who attended the training session. Each
name/date is a separate record in a junction table that is
between the sessions table and an employees table. The
main report is grouped by the PK (SessionID) from the
sessions table. At first I did not group at all, but
instead inserted a page break after the subreport. It
looks something like this:

Main Report Page Header
___________________________

Main Report Group Header
Subject
Instructor
Department
etc.

NAME DATE (column labels for the subreport)
____________________________

Main Report Detail Section
Subreport
Name Date
Name Date
______________________

Subreport Report Footer
______________________________

Main Report Page Footer

Repeat Section is set to Yes in the group header. When I
print I get the Main Report's page header and Group Header
on each page. The subreport's report footer appears at
the end of the subreport, as intended. The Main Report's
page footer appears on each page, as intended. When the
list of attendees runs onto a second page, the second page
looks just like the first.

I have a few other settings such as forcing a page break
to make the formatting work properly.

This might be a complete hash from your point of view, but
it seems to work. One thing I might change is to have
less information at the top of the second page when two
pages are necessary to list all of the attendees at a
training session. Maybe the second page would just
contain "[Subject], continued" or something like that,
then the column headings for Name and Date, and a
continuation of the attendance listing.
I sometimes wonder how much detail to put into my
questions. In this case it seems that a bit more would
have helped.
-----Original Message-----
Bruce wrote:

Thanks again for taking the time to help. I have to say
that something about the suggestion is not making sense
to
me. I tested it a bit, and could not get the number in
txtCounter to increment (I made it visible to test it).
Perhaps if I put the subreport into the group header
rather than the detail section, so that txtCounter is
the
only thing in the detail section? As it is now, with
the
subreport in the detail section, txtCounter only shows
up
once, no matter the length of the subreport. In other
words, it shows up at the end of the subreport with the
number 1 in it, no matter how many pages the subreport
occupies. For now other matters are demanding my
attention, so I will not have a chance to experiment
further for at least a few days, but I have saved the
suggestion to my personal Help files for reference when
I
can get back to it.


Time out. You seem to have placed the running sum text
box
in the main report, when I had intended it to be in the
subreport. From the way I read your question, there was
no
changes to make to the main report. I thought the group
header that you're trying to manipulate is in the
subreport.
Was that correct or is it a main report header of some
kind?



-----Original Message-----
Bruce wrote:

Thanks, that was the missing link. What I had before
was
the training session information (subject, instructor,
department, etc.) in the main report's detail
section.
Grouping was by the Session PK. The subreport,
listing
attendance, date, etc. was also in the detail
section.
The column labels were in the subreport's report
header.
(I could not get the subreport's page header to show
up
at
all). After reading your reply, I moved the session
information to the group header, along with the column
labels, and changed the Repeat Section property to
Yes.
The subreport remained in the main report's detail
section. That did what I needed. I would have
preferred
that only selected information about the session
appear
on
the second page, similar to how a page header can be
made
not to appear with the report header, but that level
of
refinement does not seem to be readily available for
group
headers. However, it is not really a problem. Your
reply
allows me to move past formatting problems so that I
can
get back to work on information management, which is
after
all the whole point of the project.


If you want the group header to look different on the
first
occurance and subsequent occurances, then you can use
VBA
code in the group header's Format event to make some of
the
header controls invisible and use CanShrink to reduce
the
space the header occupies.

First you need a way to determine when the header is
starting the group and when it is just repeating on
additional pages of the group. Then is easily done by
adding an invisible text box named txtCounter to the
detail
section. Set it's control source expression to =1 and
RunningSum property to Over Group.

Then you can make the session text boxes visible or not
as
needed:

Dim bolFirst As Boolean
bolFirst = (Me.txtCounter = 1)
Me.sessionA.Visible = bolFirst
. . .
Me.sessionZ.Visible = bolFirst

Make sure the session text box's and the header
section's
CanShrink properties are all set to Yes.
.
Click to expand...

.
Click to expand...
Click to expand...
 
Thanks for all of your help. The problem was not with the
IsNull part of the code, but the part where I tried to
make a control (text box) invisible when the value of
another control is "N/A". The problem seems to have been
that I was trying to refer to the value of a control in
the underlying record source (a query) rather than to the
value of a control on the form. When I changed the code
to txtDocRevision.Visible = Not (Me.txtDocID = "N/A") it
worked. The control source for txtDocID is [DocID1].
There is much I still need to learn. Folks like you who
are so generous with your time and expertise are why I
have made as much progress as I have (it certainly is not
because of the Access Help files).
-----Original Message-----
Bruce said:
Now I know why the sorting/grouping dialog has a place for
field/expression. It had not occurred to me to sort by a
constant, but I think I see how that works. That could
come in very handy. It makes much more sense this way.
Thanks again. I have left the Session information
(Instructor, etc.) in the main report's group header,
grouped by SessionID (the PK). One thing I still wonder
about: The main report group header's On Format event is

Dim ctl As Control

For Each ctl In Controls
ctl.Visible = Not IsNull(ctl)
Next ctl

Me.txtDocRevision.Visible = Not ([DocID1] = "N/A")

I get an error "method or data member not found" for field
[DocID1]. The field name is correct. When I rem out the
line of code, it works fine. Any ideas?
Click to expand...

Not all controls have a value to test for Null (e.g.
labels). You can test for the type of control:

For Each ctl In Me.Controls
If ctl.ControlType = acTextbox Then
ctl.Visible = Not IsNull(ctl)
End If
Next ctl


By the way, if I try to put the session information into
the report detail with the same On Format event as
described above it bogs down, and I need to press Ctrl +
Break.
Click to expand...

I don't know why that would happen. Maybe the revised
procedure will work better (certainly, you did not want to
make the subreport invisible).
--
Marsh
MVP [MS Access]


-----Original Message-----
This stuff should be handled in the subreport. Create a
group header section in the subreport based on a constant
expression such as =1. Set the header section's
RepeatSection to Yes.

Move the labels to this section and the apply the technique
I outlined before.
--
Marsh
MVP [MS Access]



Bruce wrote:

The main report group header is the training session
information (subject, instructor, etc.). Each session is
a record in the sessions table. The main report detail
section is a subreport that lists attendance at the
training session. The subreport lists name and date of
everbody who attended the training session. Each
name/date is a separate record in a junction table
Click to expand...
that
is
between the sessions table and an employees table. The
main report is grouped by the PK (SessionID) from the
sessions table. At first I did not group at all, but
instead inserted a page break after the subreport. It
looks something like this:

Main Report Page Header
___________________________

Main Report Group Header
Subject
Instructor
Department
etc.

NAME DATE (column labels for the subreport)
____________________________

Main Report Detail Section
Subreport
Name Date
Name Date
______________________

Subreport Report Footer
______________________________

Main Report Page Footer

Repeat Section is set to Yes in the group header.
Click to expand...
When
I
print I get the Main Report's page header and Group Header
on each page. The subreport's report footer appears at
the end of the subreport, as intended. The Main Report's
page footer appears on each page, as intended. When the
list of attendees runs onto a second page, the second page
looks just like the first.

I have a few other settings such as forcing a page break
to make the formatting work properly.

This might be a complete hash from your point of view, but
it seems to work. One thing I might change is to have
less information at the top of the second page when two
pages are necessary to list all of the attendees at a
training session. Maybe the second page would just
contain "[Subject], continued" or something like that,
then the column headings for Name and Date, and a
continuation of the attendance listing.
I sometimes wonder how much detail to put into my
questions. In this case it seems that a bit more would
have helped.
-----Original Message-----
Bruce wrote:

Thanks again for taking the time to help. I have to say
that something about the suggestion is not making sense
to
me. I tested it a bit, and could not get the number in
txtCounter to increment (I made it visible to test it).
Perhaps if I put the subreport into the group header
rather than the detail section, so that txtCounter is
the
only thing in the detail section? As it is now, with
the
subreport in the detail section, txtCounter only shows
up
once, no matter the length of the subreport. In other
words, it shows up at the end of the subreport with the
number 1 in it, no matter how many pages the subreport
occupies. For now other matters are demanding my
attention, so I will not have a chance to experiment
further for at least a few days, but I have saved the
suggestion to my personal Help files for reference when
I
can get back to it.


Time out. You seem to have placed the running sum text
box
in the main report, when I had intended it to be in the
subreport. From the way I read your question, there was
no
changes to make to the main report. I thought the group
header that you're trying to manipulate is in the
subreport.
Was that correct or is it a main report header of some
kind?



-----Original Message-----
Bruce wrote:

Thanks, that was the missing link. What I had before
was
the training session information (subject, instructor,
department, etc.) in the main report's detail
section.
Grouping was by the Session PK. The subreport,
listing
attendance, date, etc. was also in the detail
section.
The column labels were in the subreport's report
header.
(I could not get the subreport's page header to show
up
at
all). After reading your reply, I moved the session
information to the group header, along with the column
labels, and changed the Repeat Section property to
Yes.
The subreport remained in the main report's detail
section. That did what I needed. I would have
preferred
that only selected information about the session
appear
on
the second page, similar to how a page header can be
made
not to appear with the report header, but that level
of
refinement does not seem to be readily available for
group
headers. However, it is not really a problem. Your
reply
allows me to move past formatting problems so that I
can
get back to work on information management, which is
after
all the whole point of the project.


If you want the group header to look different on the
first
occurance and subsequent occurances, then you can use
VBA
code in the group header's Format event to make
Click to expand...
some
of
the
header controls invisible and use CanShrink to reduce
the
space the header occupies.

First you need a way to determine when the header is
starting the group and when it is just repeating on
additional pages of the group. Then is easily done by
adding an invisible text box named txtCounter to the
detail
section. Set it's control source expression to =1 and
RunningSum property to Over Group.

Then you can make the session text boxes visible or not
as
needed:

Dim bolFirst As Boolean
bolFirst = (Me.txtCounter = 1)
Me.sessionA.Visible = bolFirst
. . .
Me.sessionZ.Visible = bolFirst

Make sure the session text box's and the header
section's
CanShrink properties are all set to Yes.
.


.
Click to expand...
Click to expand...

.
Click to expand...
 
Glad to hear you're getting it sorted out.

Keep on truck'n, it gets easier after awhile ;-)
--
Marsh
MVP [MS Access]


Thanks for all of your help. The problem was not with the
IsNull part of the code, but the part where I tried to
make a control (text box) invisible when the value of
another control is "N/A". The problem seems to have been
that I was trying to refer to the value of a control in
the underlying record source (a query) rather than to the
value of a control on the form. When I changed the code
to txtDocRevision.Visible = Not (Me.txtDocID = "N/A") it
worked. The control source for txtDocID is [DocID1].
There is much I still need to learn. Folks like you who
are so generous with your time and expertise are why I
have made as much progress as I have (it certainly is not
because of the Access Help files).
-----Original Message-----
Bruce said:
Now I know why the sorting/grouping dialog has a place for
field/expression. It had not occurred to me to sort by a
constant, but I think I see how that works. That could
come in very handy. It makes much more sense this way.
Thanks again. I have left the Session information
(Instructor, etc.) in the main report's group header,
grouped by SessionID (the PK). One thing I still wonder
about: The main report group header's On Format event is

Dim ctl As Control

For Each ctl In Controls
ctl.Visible = Not IsNull(ctl)
Next ctl

Me.txtDocRevision.Visible = Not ([DocID1] = "N/A")

I get an error "method or data member not found" for field
[DocID1]. The field name is correct. When I rem out the
line of code, it works fine. Any ideas?
Click to expand...

Not all controls have a value to test for Null (e.g.
labels). You can test for the type of control:

For Each ctl In Me.Controls
If ctl.ControlType = acTextbox Then
ctl.Visible = Not IsNull(ctl)
End If
Next ctl


By the way, if I try to put the session information into
the report detail with the same On Format event as
described above it bogs down, and I need to press Ctrl +
Break.
Click to expand...

I don't know why that would happen. Maybe the revised
procedure will work better (certainly, you did not want to
make the subreport invisible).
--
Marsh
MVP [MS Access]


-----Original Message-----
This stuff should be handled in the subreport. Create a
group header section in the subreport based on a constant
expression such as =1. Set the header section's
RepeatSection to Yes.

Move the labels to this section and the apply the
technique
I outlined before.
--
Marsh
MVP [MS Access]



Bruce wrote:

The main report group header is the training session
information (subject, instructor, etc.). Each session
is
a record in the sessions table. The main report detail
section is a subreport that lists attendance at the
training session. The subreport lists name and date of
everbody who attended the training session. Each
name/date is a separate record in a junction table that
is
between the sessions table and an employees table. The
main report is grouped by the PK (SessionID) from the
sessions table. At first I did not group at all, but
instead inserted a page break after the subreport. It
looks something like this:

Main Report Page Header
___________________________

Main Report Group Header
Subject
Instructor
Department
etc.

NAME DATE (column labels for the subreport)
____________________________

Main Report Detail Section
Subreport
Name Date
Name Date
______________________

Subreport Report Footer
______________________________

Main Report Page Footer

Repeat Section is set to Yes in the group header. When
I
print I get the Main Report's page header and Group
Header
on each page. The subreport's report footer appears at
the end of the subreport, as intended. The Main
Report's
page footer appears on each page, as intended. When the
list of attendees runs onto a second page, the second
page
looks just like the first.

I have a few other settings such as forcing a page break
to make the formatting work properly.

This might be a complete hash from your point of view,
but
it seems to work. One thing I might change is to have
less information at the top of the second page when two
pages are necessary to list all of the attendees at a
training session. Maybe the second page would just
contain "[Subject], continued" or something like that,
then the column headings for Name and Date, and a
continuation of the attendance listing.
I sometimes wonder how much detail to put into my
questions. In this case it seems that a bit more would
have helped.
-----Original Message-----
Bruce wrote:

Thanks again for taking the time to help. I have to
say
that something about the suggestion is not making
sense
to
me. I tested it a bit, and could not get the number
in
txtCounter to increment (I made it visible to test
it).
Perhaps if I put the subreport into the group header
rather than the detail section, so that txtCounter is
the
only thing in the detail section? As it is now, with
the
subreport in the detail section, txtCounter only shows
up
once, no matter the length of the subreport. In other
words, it shows up at the end of the subreport with
the
number 1 in it, no matter how many pages the subreport
occupies. For now other matters are demanding my
attention, so I will not have a chance to experiment
further for at least a few days, but I have saved the
suggestion to my personal Help files for reference
when
I
can get back to it.


Time out. You seem to have placed the running sum text
box
in the main report, when I had intended it to be in the
subreport. From the way I read your question, there
was
no
changes to make to the main report. I thought the group
header that you're trying to manipulate is in the
subreport.
Was that correct or is it a main report header of some
kind?



-----Original Message-----
Bruce wrote:

Thanks, that was the missing link. What I had
before
was
the training session information (subject,
instructor,
department, etc.) in the main report's detail
section.
Grouping was by the Session PK. The subreport,
listing
attendance, date, etc. was also in the detail
section.
The column labels were in the subreport's report
header.
(I could not get the subreport's page header to show
up
at
all). After reading your reply, I moved the session
information to the group header, along with the
column
labels, and changed the Repeat Section property to
Yes.
The subreport remained in the main report's detail
section. That did what I needed. I would have
preferred
that only selected information about the session
appear
on
the second page, similar to how a page header can be
made
not to appear with the report header, but that level
of
refinement does not seem to be readily available for
group
headers. However, it is not really a problem. Your
reply
allows me to move past formatting problems so that I
can
get back to work on information management, which is
after
all the whole point of the project.


If you want the group header to look different on the
first
occurance and subsequent occurances, then you can use
VBA
code in the group header's Format event to make some
of
the
header controls invisible and use CanShrink to reduce
the
space the header occupies.

First you need a way to determine when the header is
starting the group and when it is just repeating on
additional pages of the group. Then is easily done by
adding an invisible text box named txtCounter to the
detail
section. Set it's control source expression to =1 and
RunningSum property to Over Group.

Then you can make the session text boxes visible or
not
as
needed:

Dim bolFirst As Boolean
bolFirst = (Me.txtCounter = 1)
Me.sessionA.Visible = bolFirst
. . .
Me.sessionZ.Visible = bolFirst

Make sure the session text box's and the header
section's
CanShrink properties are all set to Yes.
.


.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top