Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unanswered: Pass value from Main Report to Sub report

    I have a main report and a sub report that use the same query to receive their data. The sub report shows detail records based on the purchase order number selected via [Enter PO #] prompt from the main report.

    The report runs perfect and gives the right information, the problem is that it will ask for PO# for the main report and for EACH detail record!! I want to be able to enter in only once.

    So, how can I pass the entered in value to each and every detail record in the sub-report?? I know I can do this through SQL but don't know all the steps...

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Your Main Report should remain the same. The subreport needs to be based on a query that does not have a prompt for PO. Then in the subform control (on the Main Report) make sure the Link Child Fields and Link Master Fields should be the PO Number. If these two propeties are setup properly then the records displayed in the sub report will depend on the PO Number selected for the Main report.

    Those two properties appear in the Data tab of the propeties for the subform control.

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    If your sub-report is just that, and you want it to show the detail information of a PO#, then you should be able to use the Master/Child link properties of the sub report object. To get to the subreport object, bring up the main report in Design view, then click ONLY ONCE on the subreport. Then open the Properties window and it should say that it is for a subreport (Subform/Subreport, and the name of the subreport object. Under the Data tab, you will find the Master and Child Link properties. Here is where you would put the name of the PO# as found on the Main report (Master Link) and the name of the PO# as found on the subreport (Child Link).
    HTH,

  4. #4
    Join Date
    Nov 2003
    Posts
    300
    Great! This works. The report comes up and asks only once for a PO#!

    One question / problem, I noticed that the detail is now printing duplicate sets of records. It will print records 1-5 and then 1-5 again. ??

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    That means that the sql for the main report is producing two records with the same PO#.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by GolferGuy
    That means that the sql for the main report is producing two records with the same PO#.

    prime suspect for that is going to be a malformed join in the base query (either you where clause is ambiguous or you join is not excluding invalid records or you are mixing which purchase order column to use in the specification.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2003
    Posts
    300
    I eliminated the WHERE clause from the main qry and got the same repeating records. I changed each of the joins to the 4 tables [one at a time and then ran the report] and all runs produced either no records or the same results as repeating records. Some joins produced an error not allowing me to run the query.

    Now when I run just the main query, it gives me just the right amount of records, not so on the report..

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so assuming the query you are tinkering with and getting the 'right' results is the same as the query used in the report.....

    if you are using where clauses then its possible that you have got a duplication issue. It would be better to use the join syntax, not least because "where" is deprecated in SQL these days (it also quite easy to foul things up).

    failing that I haven't got a scooby what cold be causing your problem
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Any chance you can upload a dummy version of this report/any queries/tables that it uses?
    Right click the objects in the database window and export... Then chose the database you want to export them to
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2003
    Posts
    300
    I have no idea why it is doing it. I will start from scratch and recreate on just what you instructed. The queries / reports work fine seperately, together is a different story.

    Thanks for all the help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •