Subreport?

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

Bruce

I have previously described and received a lot of help by
folds here with a training records database. To sum it
up, I have an Employees table and a Training Sessions
table. Each employee may attend many training sessions,
and each training sessions may be attended by many
employees. To address this there is a junction table
between the Employees and Sessions tables, containing
EmployeeID and SessionID (PKs from their respective
tables, FKs in the junction table) and the date.
A form is based on the Sessions table, with a subform
based on the junction table. After entering the Session
information (about 20 possible fields, including
description, instructor, etc.) in the main form, employee
names are selected from a combo box in the subform. The
row source for the combo box is a sql statement that
concatenates Last, First from the Employees table. That
is the visible column; the bound column is EmployeeID.
That works fine for the form, but then I want a report
with the same information, with a line for each attendee
to sign. I can make a report for the session based on the
Sessions table, and a subreport based on the junction
table. The information is there, but I see the
EmployeeID, not the employee name. I know this is not
difficult, but I can't get my brain around it.
The other thing I mentioned is a line for each attendee to
sign, next to his or her name:
Jones, Johnny 1/1/2001 _______________________
etc. I suppose I could do something like format the date
field with a bunch of underscores after the date, but that
bothers me for reasons I can't quite explain.
Finally, I found this code:

Dim ctl As Control

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

and placed it in the report detail's Format event so that
empty fields don't take up room. Apparently setting the
Can Shrink property of the text boxes works only when
there are no attached labels. This code seems to take
care of that problem, as long as the labels came with the
text boxes. Independent labels are still a problem, but I
think I can solve it. Is this the best way? It seems
clumsy.
 
I'm not sure which fields you would want to hide and not take up space. To
me this red-flags normalization issues.

If you want to display employee names in a report, you should include the
table with the employee names in the report's record source. To draw lines,
you can use the line control from the tool box.

I am a bit concerned about your unknown table structures...
 
An address may contain Address 1 and Address 2 fields.
Address 2 is not always used. When it is not used, it is
hidden and shrunk so that City, State appears directly
below Address 1, without a gap. My wish to hide fields is
similarly motivated. This is a single report for various
types of training. Sometimes training is related to one
part number, sometimes to two or more; sometimes there is
one instructor, sometimes more; sometimes the training
applies to other situations, sometimes it does not; etc.
When I enter the training information in a form, the
session information is entered in the main form. For
instance:
Subject: Preparation of parts for finish coat
Document: Technical Plan Z-99
Part number: 123456
Instructor: Jim Jones
and so forth. This information is stored in the Session
table.
Next I need to enter the names of those who attended the
training. The employee names are in the Employees table,
but most of the time only a few employees attend a
training session. As I said, each employee attends many
training sessions, and each training session is attended
by many employees. By "many" I mean one or more. This
would involve a many to many relationship between the
Employees table and the Session table. To enable this, I
have created a junction table that contains an autonumber
PK, the EmployeeID (which is the PK from the Employees
table), the SessionID (which is the PK from the Session
table), and the date (since not every employee will attend
on the same day).
After I have entered the Session information as described
above, I enter the attendees in a subform that is tied to
the junction table. For example:
John Doe 3/25/04
Blanche White 3/26/04
etc.
The Employees table contains fields for first name and
last name. In the subform, the names are selected from a
combo box. The row source for the combo box is the
following expression:
SELECT [tblEmployees].[EmployeeID], [First] & " " & [Last]
AS [Full Name] FROM tblEmployees.
The bound column is 1. This is the Employee ID. The
visible column is 2, which contains the name. The form
shows the name, but the Employee ID, not the name, is
stored in the junction table.
Now I want a report to be printed. The report should
contain the session information and a listing of the
attendees. After each attendee's name I would like a line
on which then can sign their names (to verify attendance).
The report is based on a record in the Session table. The
list of attendees is in a subreport. The listing of names
comes from the records in the junction table that have the
same SessionID as in the main form. In other words, it
should look like the form/subform described above, but in
printable format. If you combine the Session I described
above with the listing of names I provided, that is what
the report should contain. It works, EXCEPT that I see a
listing of EmployeeID numbers (not names) in the
subreport. This is because only the EmployeeID is stored
in the junction table. I need to see their names, just as
I did in the subform described above.
The other issue is that I would like for there to be a
line after the date as shown in the listing of names. The
line is for the attendee to sign. I know about the line
tool, but rather than filling the page with lines that are
aligned with the text boxes containing the employee names,
I would like for the line to appear only next to the
names. As I said in the original posting, I could
probably format the date with "______________" included at
the end, but I wonder if there is a better way.
 
In my previous reply, I suggested that you should include the employee table
(tblEmployees) in the subreport's record source so the employee names can be
printed on the subreport.

If you add a horizontal line in the detail section of the subreport, it will
be displayed/drawn once per attendee. I'm not sure what you mean by "rather
than filling the page with lines". There will be only one line per attendee,
not a page full of lines.

--
Duane Hookom
MS Access MVP
--


Bruce said:
An address may contain Address 1 and Address 2 fields.
Address 2 is not always used. When it is not used, it is
hidden and shrunk so that City, State appears directly
below Address 1, without a gap. My wish to hide fields is
similarly motivated. This is a single report for various
types of training. Sometimes training is related to one
part number, sometimes to two or more; sometimes there is
one instructor, sometimes more; sometimes the training
applies to other situations, sometimes it does not; etc.
When I enter the training information in a form, the
session information is entered in the main form. For
instance:
Subject: Preparation of parts for finish coat
Document: Technical Plan Z-99
Part number: 123456
Instructor: Jim Jones
and so forth. This information is stored in the Session
table.
Next I need to enter the names of those who attended the
training. The employee names are in the Employees table,
but most of the time only a few employees attend a
training session. As I said, each employee attends many
training sessions, and each training session is attended
by many employees. By "many" I mean one or more. This
would involve a many to many relationship between the
Employees table and the Session table. To enable this, I
have created a junction table that contains an autonumber
PK, the EmployeeID (which is the PK from the Employees
table), the SessionID (which is the PK from the Session
table), and the date (since not every employee will attend
on the same day).
After I have entered the Session information as described
above, I enter the attendees in a subform that is tied to
the junction table. For example:
John Doe 3/25/04
Blanche White 3/26/04
etc.
The Employees table contains fields for first name and
last name. In the subform, the names are selected from a
combo box. The row source for the combo box is the
following expression:
SELECT [tblEmployees].[EmployeeID], [First] & " " & [Last]
AS [Full Name] FROM tblEmployees.
The bound column is 1. This is the Employee ID. The
visible column is 2, which contains the name. The form
shows the name, but the Employee ID, not the name, is
stored in the junction table.
Now I want a report to be printed. The report should
contain the session information and a listing of the
attendees. After each attendee's name I would like a line
on which then can sign their names (to verify attendance).
The report is based on a record in the Session table. The
list of attendees is in a subreport. The listing of names
comes from the records in the junction table that have the
same SessionID as in the main form. In other words, it
should look like the form/subform described above, but in
printable format. If you combine the Session I described
above with the listing of names I provided, that is what
the report should contain. It works, EXCEPT that I see a
listing of EmployeeID numbers (not names) in the
subreport. This is because only the EmployeeID is stored
in the junction table. I need to see their names, just as
I did in the subform described above.
The other issue is that I would like for there to be a
line after the date as shown in the listing of names. The
line is for the attendee to sign. I know about the line
tool, but rather than filling the page with lines that are
aligned with the text boxes containing the employee names,
I would like for the line to appear only next to the
names. As I said in the original posting, I could
probably format the date with "______________" included at
the end, but I wonder if there is a better way.
-----Original Message-----
I'm not sure which fields you would want to hide and not take up space. To
me this red-flags normalization issues.

If you want to display employee names in a report, you should include the
table with the employee names in the report's record source. To draw lines,
you can use the line control from the tool box.

I am a bit concerned about your unknown table structures...

--
Duane Hookom
MS Access MVP
--




.
Click to expand...
Click to expand...
 
I think I figured out most of what I needed. I got the
names on the subreport by basing the subreport on a query
rather than on the table. In the query I could
concatenate the names as a separate field. For the
signature lines, I just put a line next to the date
field. It appears only when there is a name and a date.
Obvious now that I see how it is done. Remaining question
has to do with headers. The subreport needs column
headings for Name, Date, and Signature. However, I cannot
figure out how to get the subreport headers (either report
or page headers) to print.
-----Original Message-----
An address may contain Address 1 and Address 2 fields.
Address 2 is not always used. When it is not used, it is
hidden and shrunk so that City, State appears directly
below Address 1, without a gap. My wish to hide fields is
similarly motivated. This is a single report for various
types of training. Sometimes training is related to one
part number, sometimes to two or more; sometimes there is
one instructor, sometimes more; sometimes the training
applies to other situations, sometimes it does not; etc.
When I enter the training information in a form, the
session information is entered in the main form. For
instance:
Subject: Preparation of parts for finish coat
Document: Technical Plan Z-99
Part number: 123456
Instructor: Jim Jones
and so forth. This information is stored in the Session
table.
Next I need to enter the names of those who attended the
training. The employee names are in the Employees table,
but most of the time only a few employees attend a
training session. As I said, each employee attends many
training sessions, and each training session is attended
by many employees. By "many" I mean one or more. This
would involve a many to many relationship between the
Employees table and the Session table. To enable this, I
have created a junction table that contains an autonumber
PK, the EmployeeID (which is the PK from the Employees
table), the SessionID (which is the PK from the Session
table), and the date (since not every employee will attend
on the same day).
After I have entered the Session information as described
above, I enter the attendees in a subform that is tied to
the junction table. For example:
John Doe 3/25/04
Blanche White 3/26/04
etc.
The Employees table contains fields for first name and
last name. In the subform, the names are selected from a
combo box. The row source for the combo box is the
following expression:
SELECT [tblEmployees].[EmployeeID], [First] & " " & [Last]
AS [Full Name] FROM tblEmployees.
The bound column is 1. This is the Employee ID. The
visible column is 2, which contains the name. The form
shows the name, but the Employee ID, not the name, is
stored in the junction table.
Now I want a report to be printed. The report should
contain the session information and a listing of the
attendees. After each attendee's name I would like a line
on which then can sign their names (to verify attendance).
The report is based on a record in the Session table. The
list of attendees is in a subreport. The listing of names
comes from the records in the junction table that have the
same SessionID as in the main form. In other words, it
should look like the form/subform described above, but in
printable format. If you combine the Session I described
above with the listing of names I provided, that is what
the report should contain. It works, EXCEPT that I see a
listing of EmployeeID numbers (not names) in the
subreport. This is because only the EmployeeID is stored
in the junction table. I need to see their names, just as
I did in the subform described above.
The other issue is that I would like for there to be a
line after the date as shown in the listing of names. The
line is for the attendee to sign. I know about the line
tool, but rather than filling the page with lines that are
aligned with the text boxes containing the employee names,
I would like for the line to appear only next to the
names. As I said in the original posting, I could
probably format the date with "______________" included at
the end, but I wonder if there is a better way.
-----Original Message-----
I'm not sure which fields you would want to hide and not take up space. To
me this red-flags normalization issues.

If you want to display employee names in a report, you should include the
table with the employee names in the report's record source. To draw lines,
you can use the line control from the tool box.

I am a bit concerned about your unknown table structures...
Click to expand...
attendee
to


.
Click to expand...
.
Click to expand...
 
Subreports have REPORT and GROUP headers that you can use. PAGE headers are
not displayed in subreports.

--
Duane Hookom
MS Access MVP
--

Bruce said:
I think I figured out most of what I needed. I got the
names on the subreport by basing the subreport on a query
rather than on the table. In the query I could
concatenate the names as a separate field. For the
signature lines, I just put a line next to the date
field. It appears only when there is a name and a date.
Obvious now that I see how it is done. Remaining question
has to do with headers. The subreport needs column
headings for Name, Date, and Signature. However, I cannot
figure out how to get the subreport headers (either report
or page headers) to print.
-----Original Message-----
An address may contain Address 1 and Address 2 fields.
Address 2 is not always used. When it is not used, it is
hidden and shrunk so that City, State appears directly
below Address 1, without a gap. My wish to hide fields is
similarly motivated. This is a single report for various
types of training. Sometimes training is related to one
part number, sometimes to two or more; sometimes there is
one instructor, sometimes more; sometimes the training
applies to other situations, sometimes it does not; etc.
When I enter the training information in a form, the
session information is entered in the main form. For
instance:
Subject: Preparation of parts for finish coat
Document: Technical Plan Z-99
Part number: 123456
Instructor: Jim Jones
and so forth. This information is stored in the Session
table.
Next I need to enter the names of those who attended the
training. The employee names are in the Employees table,
but most of the time only a few employees attend a
training session. As I said, each employee attends many
training sessions, and each training session is attended
by many employees. By "many" I mean one or more. This
would involve a many to many relationship between the
Employees table and the Session table. To enable this, I
have created a junction table that contains an autonumber
PK, the EmployeeID (which is the PK from the Employees
table), the SessionID (which is the PK from the Session
table), and the date (since not every employee will attend
on the same day).
After I have entered the Session information as described
above, I enter the attendees in a subform that is tied to
the junction table. For example:
John Doe 3/25/04
Blanche White 3/26/04
etc.
The Employees table contains fields for first name and
last name. In the subform, the names are selected from a
combo box. The row source for the combo box is the
following expression:
SELECT [tblEmployees].[EmployeeID], [First] & " " & [Last]
AS [Full Name] FROM tblEmployees.
The bound column is 1. This is the Employee ID. The
visible column is 2, which contains the name. The form
shows the name, but the Employee ID, not the name, is
stored in the junction table.
Now I want a report to be printed. The report should
contain the session information and a listing of the
attendees. After each attendee's name I would like a line
on which then can sign their names (to verify attendance).
The report is based on a record in the Session table. The
list of attendees is in a subreport. The listing of names
comes from the records in the junction table that have the
same SessionID as in the main form. In other words, it
should look like the form/subform described above, but in
printable format. If you combine the Session I described
above with the listing of names I provided, that is what
the report should contain. It works, EXCEPT that I see a
listing of EmployeeID numbers (not names) in the
subreport. This is because only the EmployeeID is stored
in the junction table. I need to see their names, just as
I did in the subform described above.
The other issue is that I would like for there to be a
line after the date as shown in the listing of names. The
line is for the attendee to sign. I know about the line
tool, but rather than filling the page with lines that are
aligned with the text boxes containing the employee names,
I would like for the line to appear only next to the
names. As I said in the original posting, I could
probably format the date with "______________" included at
the end, but I wonder if there is a better way.
-----Original Message-----
I'm not sure which fields you would want to hide and not take up space. To
me this red-flags normalization issues.

If you want to display employee names in a report, you should include the
table with the employee names in the report's record source. To draw lines,
you can use the line control from the tool box.

I am a bit concerned about your unknown table structures...

--
Duane Hookom
MS Access MVP
--

I have previously described and received a lot of help by
folds here with a training records database. To sum it
up, I have an Employees table and a Training Sessions
table. Each employee may attend many training sessions,
and each training sessions may be attended by many
employees. To address this there is a junction table
between the Employees and Sessions tables, containing
EmployeeID and SessionID (PKs from their respective
tables, FKs in the junction table) and the date.
A form is based on the Sessions table, with a subform
based on the junction table. After entering the Session
information (about 20 possible fields, including
description, instructor, etc.) in the main form, employee
names are selected from a combo box in the subform. The
row source for the combo box is a sql statement that
concatenates Last, First from the Employees table. That
is the visible column; the bound column is EmployeeID.
That works fine for the form, but then I want a report
with the same information, with a line for each attendee
to sign. I can make a report for the session based on the
Sessions table, and a subreport based on the junction
table. The information is there, but I see the
EmployeeID, not the employee name. I know this is not
difficult, but I can't get my brain around it.
The other thing I mentioned is a line for each
Click to expand...
attendee
to
sign, next to his or her name:
Jones, Johnny 1/1/2001 _______________________
etc. I suppose I could do something like format the date
field with a bunch of underscores after the date, but that
bothers me for reasons I can't quite explain.
Finally, I found this code:

Dim ctl As Control

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

and placed it in the report detail's Format event so that
empty fields don't take up room. Apparently setting the
Can Shrink property of the text boxes works only when
there are no attached labels. This code seems to take
care of that problem, as long as the labels came with the
text boxes. Independent labels are still a problem, but I
think I can solve it. Is this the best way? It seems
clumsy.



.
Click to expand...
.
Click to expand...
Click to expand...
 
I ended up including the Employee table in a query, and
basing the subreport on the query rather than on the
table. For session attendance I store only the
EmployeeID. The query lets me concatenate the first and
last names, which is how it needs to appear on the
report. So I did include the Employee table in the record
source, just not directly.
For the point about the lines, I was guilty of muddled
thinking. I have from time to time needed to create the
appearance of information being entered on a line. If
such fields are set to shrink if they are empty, the lines
still need to be dealt with. This is in a situation where
all fields on the report are from the same record.
However, in the case of the subreport, each line (Name,
Date) is a different record in the Attendance table. The
blank line for the signature appears only when there is a
record. That should have been obvious to me, but I got
stuck in my thinking.
Thanks for your help, and for clearing up (in another
posting) which headers appear when a report is printed.
I had not considered using the group header, but I think
it will help with what I need to do.
-----Original Message-----
In my previous reply, I suggested that you should include the employee table
(tblEmployees) in the subreport's record source so the employee names can be
printed on the subreport.

If you add a horizontal line in the detail section of the subreport, it will
be displayed/drawn once per attendee. I'm not sure what you mean by "rather
than filling the page with lines". There will be only one line per attendee,
not a page full of lines.

--
Duane Hookom
MS Access MVP
--


An address may contain Address 1 and Address 2 fields.
Address 2 is not always used. When it is not used, it is
hidden and shrunk so that City, State appears directly
below Address 1, without a gap. My wish to hide fields is
similarly motivated. This is a single report for various
types of training. Sometimes training is related to one
part number, sometimes to two or more; sometimes there is
one instructor, sometimes more; sometimes the training
applies to other situations, sometimes it does not; etc.
When I enter the training information in a form, the
session information is entered in the main form. For
instance:
Subject: Preparation of parts for finish coat
Document: Technical Plan Z-99
Part number: 123456
Instructor: Jim Jones
and so forth. This information is stored in the Session
table.
Next I need to enter the names of those who attended the
training. The employee names are in the Employees table,
but most of the time only a few employees attend a
training session. As I said, each employee attends many
training sessions, and each training session is attended
by many employees. By "many" I mean one or more. This
would involve a many to many relationship between the
Employees table and the Session table. To enable this, I
have created a junction table that contains an autonumber
PK, the EmployeeID (which is the PK from the Employees
table), the SessionID (which is the PK from the Session
table), and the date (since not every employee will attend
on the same day).
After I have entered the Session information as described
above, I enter the attendees in a subform that is tied to
the junction table. For example:
John Doe 3/25/04
Blanche White 3/26/04
etc.
The Employees table contains fields for first name and
last name. In the subform, the names are selected from a
combo box. The row source for the combo box is the
following expression:
SELECT [tblEmployees].[EmployeeID], [First] & " " & [Last]
AS [Full Name] FROM tblEmployees.
The bound column is 1. This is the Employee ID. The
visible column is 2, which contains the name. The form
shows the name, but the Employee ID, not the name, is
stored in the junction table.
Now I want a report to be printed. The report should
contain the session information and a listing of the
attendees. After each attendee's name I would like a line
on which then can sign their names (to verify attendance).
The report is based on a record in the Session table. The
list of attendees is in a subreport. The listing of names
comes from the records in the junction table that have the
same SessionID as in the main form. In other words, it
should look like the form/subform described above, but in
printable format. If you combine the Session I described
above with the listing of names I provided, that is what
the report should contain. It works, EXCEPT that I see a
listing of EmployeeID numbers (not names) in the
subreport. This is because only the EmployeeID is stored
in the junction table. I need to see their names, just as
I did in the subform described above.
The other issue is that I would like for there to be a
line after the date as shown in the listing of names. The
line is for the attendee to sign. I know about the line
tool, but rather than filling the page with lines that are
aligned with the text boxes containing the employee names,
I would like for the line to appear only next to the
names. As I said in the original posting, I could
probably format the date with "______________" included at
the end, but I wonder if there is a better way.
-----Original Message-----
I'm not sure which fields you would want to hide and
Click to expand...
not
take up space. To
me this red-flags normalization issues.

If you want to display employee names in a report, you should include the
table with the employee names in the report's record source. To draw lines,
you can use the line control from the tool box.

I am a bit concerned about your unknown table structures...
Click to expand...
help
by
folds here with a training records database. To sum it
up, I have an Employees table and a Training Sessions
table. Each employee may attend many training sessions,
and each training sessions may be attended by many
employees. To address this there is a junction table
between the Employees and Sessions tables, containing
EmployeeID and SessionID (PKs from their respective
tables, FKs in the junction table) and the date.
A form is based on the Sessions table, with a subform
based on the junction table. After entering the Session
information (about 20 possible fields, including
description, instructor, etc.) in the main form, employee
names are selected from a combo box in the subform. The
row source for the combo box is a sql statement that
concatenates Last, First from the Employees table. That
is the visible column; the bound column is EmployeeID.
That works fine for the form, but then I want a report
with the same information, with a line for each attendee
to sign. I can make a report for the session based
Click to expand...
on
the
Sessions table, and a subreport based on the junction
table. The information is there, but I see the
EmployeeID, not the employee name. I know this is not
difficult, but I can't get my brain around it.
The other thing I mentioned is a line for each
Click to expand...
attendee
to
sign, next to his or her name:
Jones, Johnny 1/1/2001 _______________________
etc. I suppose I could do something like format the date
field with a bunch of underscores after the date, but that
bothers me for reasons I can't quite explain.
Finally, I found this code:

Dim ctl As Control

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

and placed it in the report detail's Format event so that
empty fields don't take up room. Apparently setting the
Can Shrink property of the text boxes works only when
there are no attached labels. This code seems to take
care of that problem, as long as the labels came with the
text boxes. Independent labels are still a problem, but I
think I can solve it. Is this the best way? It seems
clumsy.



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


.
Click to expand...
 
Back
Top