Results 1 to 11 of 11
  1. #1
    Mark Harris is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    5

    Using multiple reports from one query in a specific order

    I have a difficult one to explain, but I will try:

    I have data in a table as below: Picture 1


    Click image for larger version. 

Name:	Picture 1.png 
Views:	31 
Size:	49.6 KB 
ID:	46326


    What I want to be able to do is run a single operation to output the data in columns 2-9 in order of column 1 to a report defined in column 10.

    e.g. line 6 would print to report "RTest0700" with data from line 6 in columns 2-9, then followed by line 7 printing to report "RTest1100" with data from line 7 in columns 2-9... etc etc. Where column 10 is blank, no report is selected.

    The reports look different as shown below:

    RTest0700: see picture 2
    Click image for larger version. 

Name:	Picture 2.png 
Views:	30 
Size:	8.3 KB 
ID:	46325

    RTest1100: see picture 3
    Click image for larger version. 

Name:	Picture 3.png 
Views:	29 
Size:	7.3 KB 
ID:	46324

    Is what I am trying to do possible.

    I am in no way an Access expert.... please treat me kindly...

    Thanks
    Mark

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make an empty report (no data query)
    add 3 subreport boxes
    set each subrpt to a report you listed.
    now you can print this 1 report to get all 3.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I suggest you prepare a small database sample with just a few "dummy" records with no sensitive\personal information and upload it here. To do what you need would involve writing some VBA code to loop through a recordset based on the table (or better through a query that contains only the records that have a report: "SELECT * FROM TSquadding WHERE [Report Name] Is Not Null;") and open the corresponding report in normal view (=print it) similar to this:
    Code:
    Dim rs as DAO.Recordset
    Dim sReportName as String
    
    
    Set rs=CurrentDN.OpenRecordset("SELECT * FROM TSquadding WHERE [Report Name] Is Not Null;",dbOpenSnapshot)
    
    
    If rs.RecordCount=0 then Exit Sub
    
    
    Do until rs.EOF
    
    
       sReportName =rs("[Report Name]")
       Docmd.OpenReport sReportName, acViewNormal,,"ID=" & rs("ID")
           '.OpenReport (ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
    
    
    rs.move Next
    Loop
    
    
    rs.close
    Set rs=Nothing
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Mark Harris is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    5
    Thank you Vlad; I will try that this evening and if I fail, will upload the DB, it small with test data and test reports only for now, as once it works, I can make it look how it needs to look.

    Cheers....

  5. #5
    Mark Harris is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    5

    Sample DB as suggested

    Please find attached a zip file, with dummy data. 1 query that sorts the data into the required printing order for the reports and for each line the required report is in the final column. I also have the 3 dummy reports that are needed.

    I really appreciate this help, it is may beyond my capability but I am learning :-)

    Thanks
    Mark
    FILE:
    Squadding.zip

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think your goal is clear. Based on the data provided, is that to group by report (i.e. every record with RTest0700 should be in one instance of that report)? Or is it 1 record in 1 report based on the report name for that record?

    If the latter, what should happen when a report opens with 1 record? I don't know if you can open another version of it with a different record while the 1st one is still open. Would have to look that one up. It can be done with forms so I imagine it's possible with reports.

    EDIT2 - doable by adapting this code
    http://allenbrowne.com/ser-35.html
    Last edited by Micron; 10-06-2021 at 11:00 AM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Mark,

    Is this what you are trying to achieve?

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Mark Harris is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    5
    Vlad; that my friend is 100% of what I was hoping for, I thank you so much. I could not have done that. Can I be cheeky. On the form you have added in the DB to print them. Could you add 2 more buttons for me?

    1 that allows me to print all the pages but ONLY for a specific GRID#
    2 that allows me to print a range of the pages starting at ID# "nn" and ending at ID# "yy"

    Then if I have issues when printing, I can restart from a specific place or person.

    Then I would be very happy to buy you a beer :-)

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Mark,

    Please review the update file. It should allow you to do what you suggested and more; you can leave all or any of the three combos empty and it will print\output to PDF the individual reports as requested (uses >= for the start ID and <= for the end ID) - please review the code as I added comments to explain how it works.

    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Mark Harris is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    5
    Hello Vlad, yes that is working exactly as needed on the GRID filters. The PDF option did not work, It just creates lots of files but I'm not sure I will use that option.

    Thank you again for the support and doing that code for me.

    Regards
    Mark

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Mark,
    You're very welcome! Regarding the PDF option not sure what you mean, instead of printing it saves each individual report as a PDF file in case you need to email it You can change the naming convention or file location to something else if you wish.
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 6
    Last Post: 04-26-2017, 02:27 PM
  2. Replies: 13
    Last Post: 02-08-2017, 04:11 PM
  3. Union Query Order By Specific Row Values
    By Dormie in forum Queries
    Replies: 3
    Last Post: 02-18-2015, 10:42 AM
  4. Replies: 26
    Last Post: 09-18-2014, 10:18 AM
  5. Replies: 11
    Last Post: 07-08-2011, 02:12 PM

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