Results 1 to 8 of 8
  1. #1
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13

    Question Generate Report with a SubReport for One Customer

    I apologize in advance, because this must be very straight forward, but I'm having trouble with it. I've got a database with two tables: People and Activities. They are related using a one-to-many relationship based on CaseNumber. Each person can have multiple activities which fit into one of three categories.



    I have a form where I can view each Person. That form contains a sub-form showing all of their activities. No issues there.

    I'd like to create a report showing the same info. I've created a report based on the People table and have the CaseNumber in the Report Header. I've created a SubReport for the Activities information and placed it in the details section.

    Back on the form, I've placed a button with the following code:

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport "ReportName", acViewPreview, , "[CaseNumber] = " & [CaseNumber]

    This opens the report with the correct person's CaseNumber in the Report Header, but the SubReport contains all of the activities in the Activities table. How do I limit the SubReport to the same CaseNumber?

    Next, how can I then narrow down the SubReport to only show category of activity per page for that person?

    Thank you very much.




  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Have you set the subreport container Master/Child Links properties?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    I had not. Thank you!

    How could I further restrict the subreport to show one category of activity for the given person?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Report pagination can only be controlled on main report. I am not sure you even need a subreport. Might be possible for you to include the activities in the main report RecordSource then use the report Sorting & Grouping features to force a page for each activity.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    Would it be possible to add another expression to the DoCmd.OpenReport command?

    If the activity type I want to see is “EDUC”, I thought something like this would work:

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport "ReportName", acViewPreview, , "[CaseNumber] = " & [CaseNumber] AND “[ActivityType] = ‘“ & EDUC & ‘“

    But that doesn’t work.

    I’m currently stuck with internet access on my phone (legacy unlimited data plan that doesn’t allow a hotspot) and no WiFi to connect my laptop to, so I can’t upload the file.

    Thank you.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Activities table would have to be included in the main report RecordSource.

    So maybe go to Starbucks?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    hammer32 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    13
    It may not be pretty, but I’ve included ActivityType as a short text field in both tables, with the idea that I could set the value in People to the one I want to see. Then added that field to the Link Master/Child Fields.

    This is the prairie, nearest Starbucks is a four hour round trip

    Thank you again for your help!!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Wow! Then anything else with internet must be even farther away! Can't upload at work?

    Not sure that extra linking is advisable. is it working? You can set a dynamic filter in the subreport RecordSource to filter to a single activity but I thought you wanted all activities, but force each to its own page?

    The only other thing could do is use the table builder in forum Advanced post editor to provide your db structure. Post the SQL statements of query used in each RecordSource.

    I am still thinking a subreport might not be necessary but really need to know more about the data and what you want to output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 04-16-2018, 07:43 PM
  2. Replies: 6
    Last Post: 02-25-2014, 07:44 PM
  3. Replies: 6
    Last Post: 12-11-2013, 09:27 PM
  4. Replies: 1
    Last Post: 04-15-2013, 10:02 AM
  5. Replies: 4
    Last Post: 06-08-2011, 04:30 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums