Report printing problem

[YFH]

I am trying to print a single record from a form into a report.

The form is called formContract (based on a table called Contract) and
includes 2 subforms: formContract_Color and formContract_Price. The two
subforms basically summarize the items on the contract by color and by
price, respectively, and they are based on two tables Contract_Color
and Contract_Price. It's a one-to-many relationship between Contract
and Contract_Color/Contract_Price, and on the form they're linked by
master/child key Contract_ID.

The related report is called rptContract. I put a command button in the
form that opens the related record in report preview mode
(DoCmd.OpenReport stDocName, acViewPreview, ,
"[Order_ID]=Forms!formContract!cmbSelectOrder"
). I used the wizard to create 2 subreports in rptContract, based on
formContract_Color and formContract_Price.

I've designed the report so that each record can be printed on a single
page. I've also made sure that the report length and width plus margins
don't exceed the page.

The problem happens when I try to print the report. Instead of printing
one page for the selected record, it prints X pages, where X
corresponds to the number of corresponding records in Contract_Color.
Only the first page is what I want, containing all the information from
both subforms. All the other pages are identical to the first one,
except the subform information is missing (blank). For example, if the
contract I want to print has 20 color groups, 20 pages are printed.

Does anyone know the cause of this problem? Do I need to put in code
that specifies the subreports to be printed (even though I have defined
the master/child keys in both the form and the report)?

Thanks,
YH

 

[Douglas J. Steele]

It sounds as though the underlying query that makes up the recordsource for
the report is incorrect.

What's the SQL for that query? (In case you're not familiar with getting the
SQL for a query, when the query's open, select View | SQL View from the
menu).

 

[YFH]

Hi Doug,

The main report is based on a query called qryContract:

SELECT ORDER.Order_ID, ORDER.Factory_ID, ORDER.Style_ID, ORDER.DES11,
ORDER.DES22, Factory.Company_Name, ORDER.MK1, ORDER.MK2, ORDER.MK3,
ORDER.MK4, ORDER.MK5, ORDER.MK6, ORDER.MK7
FROM Factory INNER JOIN [ORDER] ON Factory.Factory_No=ORDER.Factory_ID
ORDER BY ORDER.Order_ID, ORDER.Factory_ID, ORDER.Style_ID;

The 2 subreports are then created with the wizard based on tables
Contract_Color and Contract_Price.

Thanks,
YFH

It sounds as though the underlying query that makes up the recordsource for
the report is incorrect.

What's the SQL for that query? (In case you're not familiar with getting the
SQL for a query, when the query's open, select View | SQL View from the
menu).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to print a single record from a form into a report.

The form is called formContract (based on a table called Contract) and
includes 2 subforms: formContract_Color and formContract_Price. The two
subforms basically summarize the items on the contract by color and by
price, respectively, and they are based on two tables Contract_Color
and Contract_Price. It's a one-to-many relationship between Contract
and Contract_Color/Contract_Price, and on the form they're linked by
master/child key Contract_ID.

The related report is called rptContract. I put a command button in the
form that opens the related record in report preview mode
(DoCmd.OpenReport stDocName, acViewPreview, ,
"[Order_ID]=Forms!formContract!cmbSelectOrder"
). I used the wizard to create 2 subreports in rptContract, based on
formContract_Color and formContract_Price.

I've designed the report so that each record can be printed on a single
page. I've also made sure that the report length and width plus margins
don't exceed the page.

The problem happens when I try to print the report. Instead of printing
one page for the selected record, it prints X pages, where X
corresponds to the number of corresponding records in Contract_Color.
Only the first page is what I want, containing all the information from
both subforms. All the other pages are identical to the first one,
except the subform information is missing (blank). For example, if the
contract I want to print has 20 color groups, 20 pages are printed.

Does anyone know the cause of this problem? Do I need to put in code
that specifies the subreports to be printed (even though I have defined
the master/child keys in both the form and the report)?

Thanks,
YH

 

[Douglas J. Steele]

What have you got set up as the Link Child and Link Master fields between
the main report and the color subreport? I don't see anything in your main
query that sems to be related to color...

BTW, since Order is a reserved word, you might try putting it in square
brackets everywhere, not just in the From clause as you current have.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

The main report is based on a query called qryContract:

SELECT ORDER.Order_ID, ORDER.Factory_ID, ORDER.Style_ID, ORDER.DES11,
ORDER.DES22, Factory.Company_Name, ORDER.MK1, ORDER.MK2, ORDER.MK3,
ORDER.MK4, ORDER.MK5, ORDER.MK6, ORDER.MK7
FROM Factory INNER JOIN [ORDER] ON Factory.Factory_No=ORDER.Factory_ID
ORDER BY ORDER.Order_ID, ORDER.Factory_ID, ORDER.Style_ID;

The 2 subreports are then created with the wizard based on tables
Contract_Color and Contract_Price.

Thanks,
YFH

It sounds as though the underlying query that makes up the recordsource
for
the report is incorrect.

What's the SQL for that query? (In case you're not familiar with getting
the
SQL for a query, when the query's open, select View | SQL View from the
menu).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to print a single record from a form into a report.

The form is called formContract (based on a table called Contract) and
includes 2 subforms: formContract_Color and formContract_Price. The two
subforms basically summarize the items on the contract by color and by
price, respectively, and they are based on two tables Contract_Color
and Contract_Price. It's a one-to-many relationship between Contract
and Contract_Color/Contract_Price, and on the form they're linked by
master/child key Contract_ID.

The related report is called rptContract. I put a command button in the
form that opens the related record in report preview mode
(DoCmd.OpenReport stDocName, acViewPreview, ,
"[Order_ID]=Forms!formContract!cmbSelectOrder"
). I used the wizard to create 2 subreports in rptContract, based on
formContract_Color and formContract_Price.

I've designed the report so that each record can be printed on a single
page. I've also made sure that the report length and width plus margins
don't exceed the page.

The problem happens when I try to print the report. Instead of printing
one page for the selected record, it prints X pages, where X
corresponds to the number of corresponding records in Contract_Color.
Only the first page is what I want, containing all the information from
both subforms. All the other pages are identical to the first one,
except the subform information is missing (blank). For example, if the
contract I want to print has 20 color groups, 20 pages are printed.

Does anyone know the cause of this problem? Do I need to put in code
that specifies the subreports to be printed (even though I have defined
the master/child keys in both the form and the report)?

Thanks,
YH

 

[YFH]

Oops, it was the wrong query I put in the last message. The underlying
query, qryContractReport, actually looks like this,

SELECT qryContract.Order_ID, qryContract.Factory_ID,
qryContract.Style_ID, qryContract.DES11, qryContract.DES22,
qryContract.Company_Name, ContractDetails.Destination,
ContractDetails.Payment, ContractDetails.Shipment,
ContractDetails.Payment_Criteria, Contract_Color.ORD_SEQ,
Contract_Color.COL, Contract_Color.QTY,
FROM (qryContract INNER JOIN ContractDetails ON qryContract.Order_ID =
ContractDetails.Order_ID) INNER JOIN Contract_Color ON
qryContract.Order_ID = Contract_Color.Order_ID;

qryContract selects information from multiple tables (e.g., Order,
Factory). ContractDetails stores information entered by the user at the
time the contract is to be printed.

Contract_Color has a composit primary key {Order_ID, ORD_SEQ} since one
order may have multiple color groups.

I tried removing from the SQL all lines related to Contract_Color,
assuming that a master/child link to the subreports would do the trick
(link child: Contract_Color.Order_ID, link master:
qryContractReport.Order_ID). After I removed those lines, only one page
is printed for each record, but I started getting a pop-up box that
asks me to enter the ORD_SEQ for the selected record....

What have you got set up as the Link Child and Link Master fields between
the main report and the color subreport? I don't see anything in your main
query that sems to be related to color...

BTW, since Order is a reserved word, you might try putting it in square
brackets everywhere, not just in the From clause as you current have.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

The main report is based on a query called qryContract:

SELECT ORDER.Order_ID, ORDER.Factory_ID, ORDER.Style_ID, ORDER.DES11,
ORDER.DES22, Factory.Company_Name, ORDER.MK1, ORDER.MK2, ORDER.MK3,
ORDER.MK4, ORDER.MK5, ORDER.MK6, ORDER.MK7
FROM Factory INNER JOIN [ORDER] ON Factory.Factory_No=ORDER.Factory_ID
ORDER BY ORDER.Order_ID, ORDER.Factory_ID, ORDER.Style_ID;

The 2 subreports are then created with the wizard based on tables
Contract_Color and Contract_Price.

Thanks,
YFH

It sounds as though the underlying query that makes up the recordsource
for
the report is incorrect.

What's the SQL for that query? (In case you're not familiar with getting
the
SQL for a query, when the query's open, select View | SQL View from the
menu).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am trying to print a single record from a form into a report.

The form is called formContract (based on a table called Contract) and
includes 2 subforms: formContract_Color and formContract_Price. The two
subforms basically summarize the items on the contract by color and by
price, respectively, and they are based on two tables Contract_Color
and Contract_Price. It's a one-to-many relationship between Contract
and Contract_Color/Contract_Price, and on the form they're linked by
master/child key Contract_ID.

The related report is called rptContract. I put a command button in the
form that opens the related record in report preview mode
(DoCmd.OpenReport stDocName, acViewPreview, ,
"[Order_ID]=Forms!formContract!cmbSelectOrder"
). I used the wizard to create 2 subreports in rptContract, based on
formContract_Color and formContract_Price.

I've designed the report so that each record can be printed on a single
page. I've also made sure that the report length and width plus margins
don't exceed the page.

The problem happens when I try to print the report. Instead of printing
one page for the selected record, it prints X pages, where X
corresponds to the number of corresponding records in Contract_Color.
Only the first page is what I want, containing all the information from
both subforms. All the other pages are identical to the first one,
except the subform information is missing (blank). For example, if the
contract I want to print has 20 color groups, 20 pages are printed.

Does anyone know the cause of this problem? Do I need to put in code
that specifies the subreports to be printed (even though I have defined
the master/child keys in both the form and the report)?

Thanks,
YH

 

[Douglas J Steele]

If you're getting a pop-up, make sure that you didn't mistype the name of
the field. Check in the underlying query, the Control Source for all bound
fields on the report, in the Sorting and Grouping dialog and in any VBA code
that might be setting properties of the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Oops, it was the wrong query I put in the last message. The underlying
query, qryContractReport, actually looks like this,

SELECT qryContract.Order_ID, qryContract.Factory_ID,
qryContract.Style_ID, qryContract.DES11, qryContract.DES22,
qryContract.Company_Name, ContractDetails.Destination,
ContractDetails.Payment, ContractDetails.Shipment,
ContractDetails.Payment_Criteria, Contract_Color.ORD_SEQ,
Contract_Color.COL, Contract_Color.QTY,
FROM (qryContract INNER JOIN ContractDetails ON qryContract.Order_ID =
ContractDetails.Order_ID) INNER JOIN Contract_Color ON
qryContract.Order_ID = Contract_Color.Order_ID;

qryContract selects information from multiple tables (e.g., Order,
Factory). ContractDetails stores information entered by the user at the
time the contract is to be printed.

Contract_Color has a composit primary key {Order_ID, ORD_SEQ} since one
order may have multiple color groups.

I tried removing from the SQL all lines related to Contract_Color,
assuming that a master/child link to the subreports would do the trick
(link child: Contract_Color.Order_ID, link master:
qryContractReport.Order_ID). After I removed those lines, only one page
is printed for each record, but I started getting a pop-up box that
asks me to enter the ORD_SEQ for the selected record....

What have you got set up as the Link Child and Link Master fields between
the main report and the color subreport? I don't see anything in your main
query that sems to be related to color...

BTW, since Order is a reserved word, you might try putting it in square
brackets everywhere, not just in the From clause as you current have.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

The main report is based on a query called qryContract:

SELECT ORDER.Order_ID, ORDER.Factory_ID, ORDER.Style_ID, ORDER.DES11,
ORDER.DES22, Factory.Company_Name, ORDER.MK1, ORDER.MK2, ORDER.MK3,
ORDER.MK4, ORDER.MK5, ORDER.MK6, ORDER.MK7
FROM Factory INNER JOIN [ORDER] ON Factory.Factory_No=ORDER.Factory_ID
ORDER BY ORDER.Order_ID, ORDER.Factory_ID, ORDER.Style_ID;

The 2 subreports are then created with the wizard based on tables
Contract_Color and Contract_Price.

Thanks,
YFH


Douglas J. Steele wrote:
It sounds as though the underlying query that makes up the recordsource
for
the report is incorrect.

What's the SQL for that query? (In case you're not familiar with getting
the
SQL for a query, when the query's open, select View | SQL View from the
menu).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am trying to print a single record from a form into a report.

The form is called formContract (based on a table called Contract) and
includes 2 subforms: formContract_Color and formContract_Price. The two
subforms basically summarize the items on the contract by color and by
price, respectively, and they are based on two tables Contract_Color
and Contract_Price. It's a one-to-many relationship between Contract
and Contract_Color/Contract_Price, and on the form they're linked by
master/child key Contract_ID.

The related report is called rptContract. I put a command button in the
form that opens the related record in report preview mode
(DoCmd.OpenReport stDocName, acViewPreview, ,
"[Order_ID]=Forms!formContract!cmbSelectOrder"
). I used the wizard to create 2 subreports in rptContract, based on
formContract_Color and formContract_Price.

I've designed the report so that each record can be printed on a single
page. I've also made sure that the report length and width plus margins
don't exceed the page.

The problem happens when I try to print the report. Instead of printing
one page for the selected record, it prints X pages, where X
corresponds to the number of corresponding records in Contract_Color.
Only the first page is what I want, containing all the information from
both subforms. All the other pages are identical to the first one,
except the subform information is missing (blank). For example, if the
contract I want to print has 20 color groups, 20 pages are printed.

Does anyone know the cause of this problem? Do I need to put in code
that specifies the subreports to be printed (even though I have defined
the master/child keys in both the form and the report)?

Thanks,
YH