Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Report printing problem

Discussion in 'Information Technology' started by YFH, Jul 28, 2006.

  1. YFH

    YFH
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    UK British Sikh Report-2016 Breaking News Mar 24, 2016
    1984 The Story I Did Not Report In ’84 History of Sikhism Jun 28, 2015
    Opinion Top Green Beret Officer Forced to Resign Over Affair With WaPo Reporter Breaking News Jun 26, 2014
    United Sikhs New Report Shows Increasing Hate Attacks on Sikhs Sikh Organisations May 21, 2014
    Legal ACLU reports: Judge orders Sikh man, "Remove that rag, or Go to Jail! Breaking News Sep 27, 2013

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no private e-mails, please)


    "YFH" <yingfanh@yahoo.ca> wrote in message
    news:1150360635.618776.225250@i40g2000cwc.googlegroups.com...
    >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
    >
     
  4. YFH

    YFH
    Expand Collapse
    Guest

    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
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "YFH" <yingfanh@yahoo.ca> wrote in message
    > news:1150360635.618776.225250@i40g2000cwc.googlegroups.com...
    > >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
    > >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no private e-mails, please)


    "YFH" <yingfanh@yahoo.ca> wrote in message
    news:1150422670.765434.277850@i40g2000cwc.googlegroups.com...
    > 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
    >> http://I.Am/DougSteele
    >> (no private e-mails, please)
    >>
    >>
    >> "YFH" <yingfanh@yahoo.ca> wrote in message
    >> news:1150360635.618776.225250@i40g2000cwc.googlegroups.com...
    >> >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
    >> >

    >
     
  6. YFH

    YFH
    Expand Collapse
    Guest

    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....

    Douglas J. Steele wrote:
    > 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
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "YFH" <yingfanh@yahoo.ca> wrote in message
    > news:1150422670.765434.277850@i40g2000cwc.googlegroups.com...
    > > 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
    > >> http://I.Am/DougSteele
    > >> (no private e-mails, please)
    > >>
    > >>
    > >> "YFH" <yingfanh@yahoo.ca> wrote in message
    > >> news:1150360635.618776.225250@i40g2000cwc.googlegroups.com...
    > >> >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
    > >> >

    > >
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no e-mails, please!)


    "YFH" <yingfanh@yahoo.ca> wrote in message
    news:1150520713.253598.323460@r2g2000cwb.googlegroups.com...
    > 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....
    >
    > Douglas J. Steele wrote:
    > > 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
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "YFH" <yingfanh@yahoo.ca> wrote in message
    > > news:1150422670.765434.277850@i40g2000cwc.googlegroups.com...
    > > > 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
    > > >> http://I.Am/DougSteele
    > > >> (no private e-mails, please)
    > > >>
    > > >>
    > > >> "YFH" <yingfanh@yahoo.ca> wrote in message
    > > >> news:1150360635.618776.225250@i40g2000cwc.googlegroups.com...
    > > >> >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
    > > >> >
    > > >

    >
     

Share This Page