Results 1 to 12 of 12
  1. #1
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142

    quick way to print multiple reports


    I have a report that I can run from my database that gives me an individuals weekly report. The report is populated by first typing in the persons name and then the date range to be included in the report. This is not bad when I have to do one or two reports for individuals but when I have to do everyone (over 150 people) it can get quite bothersome having to type "name-date range - print, name, date range - print, name date range- print" again and again and again.

    Is there a quicker way to print everyone at once and still get individual reports printed?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is there a quicker way to print everyone at once
    Taken verbatim, no. Presumably you mean 'in one operation'. Then yes.
    You'd build a recordset of report recipients and loop through it, opening the report and either a) apply the person id and dates as filter values or b) open the report using a sql recordsource that you build in code and open it using that sql as its recordsource. In either case, you'd have to close the report after the print otherwise those current values are likely to persist. I don't see an advantage with either method beyond (perhaps) filtering would take less code. AFAIK, the whole report isn't loaded then filtered; rather the recordset filter is applied first. Thus there should be no speed advantage as potentially thousands of records are loaded then filtered out. It's what I used to think happened, but have learned it's not so.

    Are you sure you want to print 150 reports rather than email them?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Philippines
    Posts
    142
    unfortunately yes. Management requires associate sign off for the report. I print it, you sign it, they keep it. repeat 150 times a week. It can take me up to 2 hours to run and print them all depending on if i'm interrupted a lot.

  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,816
    Many examples out there, roughly following:
    Code:
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT ID FROM table")
    Do While Not rs.EOF
        DoCmd.OpenReport "reportname", , , "ID=" & rs!ID
        rs.MoveNext
    Loop
    Include what other criteria you need along with the ID.

    Probably take about 20 minutes to print 150 reports, depending on speed of network and printer, but however long it takes, you can be doing something else, although not within the db.
    Last edited by June7; 03-03-2019 at 09:41 PM.
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Had most of this done but company showed up. Might as well post it anyway. Assumes dates will come from a form and the names are based on employee numbers, not names, and come from a query or sql statement. acNormal should send the report directly to the printer. The window mode could open the report hidden, but it's not used here. I would also put in about a 2 second pause between print calls, otherwise it could be too fast for the printer spooler.

    Code:
    Sub PrintReports(dteStart As Date, dteEnd As Date)
    Dim rs As DAO.Recordset
    Dim strWhere As String
    
    strWhere1 = "[fieldNameForDate] BETWEEN " & dteStart & " AND " & dteEnd
    Set rs = CurrentDb.OpenRecordset "sql here to get a list of recipients by employee number"
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do While Not rs.EOF
            DoCmd.OpenReport "reportName", acNormal,, "[RecipientFieldName] = " & rs.EmplNo & " And " & strWhere
           Pause(2)
           DoCmd.Close acReport "reportName"
           rs.MoveNext
        Loop
    End If
    
    End Sub
    Next part in a standard module so that it can be used anywhere in the db
    Code:
    Sub Pause(intSec As Integer)
    Dim varTime As Variant
    
    varTime = Timer
    Do Until Timer = varTimer + intSec
    Loop
    
    End Sub
    Do While Not (rs.BOF And rs.EOF)
    Never have tried that syntax. Would have thought that rs would never be both BOF and EOF after commencing a loop that actually contains records. It will be when it gets to EOF, but I don't see it being an AND situation when it gets there. I've been wrong before...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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,816
    Per Allen http://allenbrowne.com/ser-29.html#Move_With_No_Records

    Revised previous post. I was mixing print and PDF output code.
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What you quoted
    If Not (rs.BOF And rs.EOF) Then
    If rs.RecordCount <> 0 Then
    is not what you wrote,
    Do While Not (rs.BOF And rs.EOF)
    but it is what I wrote. I use his method all the time. He then goes on
    Do while Not rst.EOF
    If rst![MyField]
    My bad - I forgot to close my recordset.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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,816
    Actually, what you say is what I have always done. Then I scanned Allen's article again and realized wasn't checking for BOF with an If statement. Will the code enter Do While and error with no records because not checking for BOF? Can't the Do While loop consider both? Will have to do some trials. Okay, answered my question. Testing for EOF is enough.
    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.

  9. #9
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    What abount Adding a table with Employee Names, numbers then have a couple checkbox fields added. Then run a form with a date from and date to controls, and you can check as many individial as you need.


    List of Names, List of reports with check boxes, 2 X Date fields. and a submit button. End Date can be default to =Date().

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Re your testing: Would have to be tested when there ARE records, yes? Because you have AND it will never be both BOF AND EOF after a move.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I did not test with the AND criteria, just with EOF. I tested with and without records. EOF was enough to prevent the looping when there weren't any records.
    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.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    not bad when I have to do one or two reports for individuals but when I have to do everyone (over 150 people)
    150 steps in a macro??
    Don't mean to sound condescending, but experienced developers don't use macros; perhaps mostly because they have no real error handling. When they fail, they fail completely. They are also quite limited in what they can do, compared to code.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-15-2015, 11:36 AM
  2. Replies: 1
    Last Post: 12-04-2014, 01:28 PM
  3. Print button quick print
    By xtrareal22 in forum Reports
    Replies: 1
    Last Post: 12-11-2013, 11:21 AM
  4. Print multiple reports choosen from a form.
    By Wieli in forum Programming
    Replies: 1
    Last Post: 11-26-2012, 02:20 PM
  5. Print different Reports at once
    By Brian62 in forum Reports
    Replies: 5
    Last Post: 01-21-2011, 11:19 AM

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