Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: printing form/subform to report/subreport

    Hi all, this is my first post.I need help on printing a form with a subform to a report with a subreport.Ok, to simplify...
    I have one main form named "Job card" with primary key "Customer ID" which has a subform named "Jobs".For each customer in main form there are few records in subform.they are linked and I can see one subform record at a time.This is what I want.
    Now the problem is I want to print only the current record from subform,not all subform records for a particular customer.
    I created a report which has a subreport exactly as my form/subform.
    When I use the command
    "[Customer ID] = " & Me.[Customer ID]
    it picks up the customer I am on the form(which is ok) but also print all subform records associated with it.I only want the current subform record I am on.
    Any suggestion would be appreciated.
    Regards

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you open the report, you must pass a parameter to it (filter or criteria) that uniquely identifies the job you want to see on the subreport (JobId?). Search in Access help for Docmd.OpenReport for explanations on how to use such parameters.
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Posts
    3
    Thanks for the reply,mate.
    Here is how I tried but no luck.it is asking for a parameter value especially for the part in bold. ,so the expression is wrong.what I am wondering is that can it be achieved this way?what is the proper way to call the expression?
    strWhere = "[Customer ID] = " & Me.[Customer ID] '& " AND " & "[Jobs subreport].Report![Job ID] = " & Me![Jobs]![Job ID]
    DoCmd.OpenReport "Job report", acViewPreview, , strWhere
    Regards

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question Typo

    strWhere = "[Customer ID] = " & Me.[Customer ID] '& " AND " & "[Jobs subreport].Report![Job ID] = " & Me![Jobs]![Job ID]

    There is a typo in the above - try removing the single quote mark before the first ampersand:-

    strWhere = "[Customer ID] = " & Me.[Customer ID] & " AND " & "[Jobs subreport].Report![Job ID] = " & Me![Jobs]![Job ID]

    See if that makes any difference
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Apr 2010
    Posts
    3
    Its still same.I think what I am missing is to refer to the subreport correctly.for example, when trying
    "[Customer ID] = " & Me.[Customer ID] '& " AND " & "Reports.Jobs subreport![Job ID] = " & Me.[Jobs].Form![Job ID]
    say if I am in customer id 10 and job id 4 out of 20, it gives an error.
    Run-time error 3075:
    Syntax error (missing operator) in query expression '[Customer ID] =10 AND Reports.Jobs subreport![Job ID] = 4'
    What am I doing wrong?
    Anyone plz help.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The syntax is similar to the one you use with a subform:
    Code:
    "[Customer ID] = " & Me.[Customer ID] '& " AND " & "Reports.Jobs subreport.Report![Job ID] = " & Me.[Jobs].Form![Job ID]
    Have a nice day!

Posting Permissions

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