Results 1 to 10 of 10

run a report multiple times and print them

  1. #1
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    127

    run a report multiple times and print them

    I have a report that when ran will bring up an individuals weekly work. To run the report you are prompted to enter the persons name and then the date range you want the report to consist of.
    I am going to have to start running this report once a week for everyone in my system. This could be up to 175 times.
    Is it possible to create a macro that will look at all the active people in the system and then run and print a report based on a date range for all of them so that I end up with however many individual printed reports? I don't want one report with everyone in it but one for everyone.

    I know that in excel you can have the macro look at a list of names for the associates and then a cell containing the start date and a cell containing the end date and it would print however many reports you need.

    The report I am wanting is the weekly average report and the associates are listed in the associate list table.
    Attached Files Attached Files

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,209
    your db doesn't seem to contain any macros, so you mean sub or procedure (code) yes? If not, then I (and many others here) aren't real macro savvy.
    I'm guessing no to macro, so can you write a procedure that returns a recordset of names and whatever other fields you need, then loop over that recordset and send/print the report for each user? Do you really want to print 175 copies of basically the same info?

    BTW - "cell" is Excel speak. Access doesn't have cells but there are controls and fields that contain data. Thinking you would be referring to a form control...
    never mind - upon reviewing, I think you were in fact using cell in the right context
    Last edited by Micron; 01-04-2019 at 10:46 AM. Reason: added comment
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Code isn't smart. It does what you ask with what you give it.

  3. #3
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    127
    I am used to excel so that is why i thought it would be done with a macro. I am not really sure how you would do it in access but if you can point me to the proper terminology I can look up how to do it. Yes the report would be run 175 times or so but each individual would have different information so it would be different reports.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,567
    If you have a "standard report" that you run, and the difference from 1 run to the next is the Name of the Associate and his/her particulars, then I'm sure you can automate the process.
    How do you pass parameters to the report or query that supplies data to the report?

    So, you are dealing with active Associates, who have time/ task info recorded.
    You need to identify the "week" involved, and if all these weekly reports are for the same week, you only need to enter it once.

    Then this general logic ( if I understand your need):
    Code:
    get generic parameters (week...)
    For each active associate
       get the associates info
       pass associate and generic parameter to report/query??
       run the Custom report
    next Associate
    You would probably use a form with some comboboxes to get your generic parameter(s),
    then use some vba code to populate the Report/query parameters. Seems it could be automated without too much difficulty.

    With some test records, I'm sure you could set this up and test/revise to meet your needs.

    Good luck.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,209
    since I've seen your db, allow me to elaborate on what Orange is saying. At the bare minimum you would need to
    - have a query that gets the user info based on active users
    - open a recordset based on that query, then loop through it one user at a time
    - open/print the report filtered to the user for the first pass through the loop
    - close the report in code
    - move to the next recordset record
    - repeat the report filtering and opening/printing until the end of the recordset

    However, that is just the skeleton of the thing. If someone doesn't input values (or they're incorrect) from your prompts you will print a useless report. Thus a form with controls for this data and ensuring they're populated would be the way to go. Further, I'd have a table of reports with a combo on said form to pick the report. Doing so would make the necessary controls (fields) visible on that form and the button code ensures they are populated. To go further would be to validate that a date control doesn't contain text or vice versa. It will certainly take some effort if you're not adept with code.

    Also, you should avoid spaces in object names DailyCoaching or rptDailyCoaching etc. - NOT Daily Coaching. Nor would I append "report" to the end of a report name as it's redundant in a way when you do it only once for a set of reports. I suggest you be consistent with your naming convention as you seem to have one, but only some of the time (I would use rptDailyCoaching). Last but not least, you probably have multiple reports where 1 would do (daily/monthly/weekly look pretty much the same), but I think that's something you can work to as you develop your skills.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,567
    Yes, I agree with Micron. What I suggested was the mechanics of multiple reports for individuals who have their name and "work/task hours recorded". But as he suggests you need to vet/audit/ensure the data provided is complete and accurate. My set up was general and did not deal with validation of the data.
    Good luck with your project

  7. #7
    chriswrcg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    127
    Most of what you guys are saying is beyond me. I already have the report set up for what I need. I just need a way to tell it what names to use and what date range and then loop from name one to the next to the next until all the names have been used and the reports created. Seems simple and the way you are describing it seams simple, however it seams like you are telling me to grab a guy off the street, sit them in front of a patient, hand them a scalpel, and tell them to operate by doing one, two, and three then leaving the room. I need a little more direction or at least point me to some kind of tut that can teach me the basics of what I need to do.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,209
    I think you have the pointers in post 5. If you don't know what "open a recordset" means (not that you should) then that's what you Google.
    Same goes for "loop through a recordset", "open a report", "filter a report", etc. etc. You grab code examples that look like they do what you need a piece it together. Then you come back here when it's not really working.

    Perhaps we've made it seem too simple, but really, it only is if you've got the experience. It certainly is involved, though and I'm not saying it's a 10 minute exercise. Maybe it's like saying "start and drive a car from point A to point B". Sounds simple enough, but no doubt you know there are 100 steps in between. This is kind of like that. Maybe if you tried one step at a time, it would seem less daunting. You could start with the query (the first step I pointed out) then try building a recordset on it and come back with your progress. Alternatively, you hire someone to do it for you.

    I always say that M$ makes Access easy to do poorly. No doubt you have the smarts to learn it, but I'm here to tell you it's a long learning curve to do it well.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,567
    I think you are over reacting, but I can understand some confusion on your part.
    The mechanics of automating the individual reports for the active associates within a given week are relatively simple.
    What Micron highlighted, and I agree, is that your data may be inaccurate and/or missing. things/incomplete.

    Have you validated the input data? If associates enter data, how do you know there are no typos, or missing values? That's the more difficult part.

    Have you considered a test plan where you know the test data, the error conditions to be checked, and then run a sample set of data through your routine to ensure only good/validated data gets to the reports, and errors are gathered so corrections can be made? This kind of testing uses good, bad and missing data....

    As for a tutorial, it depends on how much of your database you have designed and coded yourself.

    Here is a small routine to mock up a loop through the Associates and show the basic logic to automate the process.
    It is simply printing a line for each individual, but it can be extended to Run your required report.

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: showAutoProcess
    ' Purpose: Demo logic to loop through a recordset based on a Table of Associates
    '  and show where/how custom reports for each associate would be generated.
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 04-Jan-19
    ' ----------------------------------------------------------------
    Sub showAutoProcess()
    10    On Error GoTo showAutoProcess_Error
              Dim db As DAO.Database
              Dim rs As DAO.Recordset
              Dim iCnt As Integer
    20    Set db = CurrentDb
    'Limit this test to Associates whose lastname begins with B or C.
    
    30    Set rs = db.OpenRecordset(" Select aLastname & ', '& aFirstname as xFullName " _
                                & " FROM tblAssociateList where aLastname >= 'B' and aLastname <= 'C' " _
                                & " Order By aLastName, aFirstName ;")
    40    Do While Not rs.EOF
    '  Code to set up the Report parameters for the individual would go here
    '  DoCmd.OpenReport  ReportName,,, with parameters
    
    50      Debug.Print "Create the weekly report for: " & rs!xFullname
    60      iCnt = iCnt + 1
    70      rs.MoveNext
    80    Loop
            Debug.Print " Total records created: " & iCnt
    90    On Error GoTo 0
    100   Exit Sub
    
    showAutoProcess_Error:
    
    110   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure showAutoProcess, line " & Erl & "."
    
    End Sub
    Sample output from routine
    Code:
    Create the weekly report for: Ball, Antoine
    Create the weekly report for: Banta, Ron
    Create the weekly report for: Barko, Jacob
    Create the weekly report for: Barnett, John
    Create the weekly report for: Blankenship, Darrell
    Create the weekly report for: borko, jacob
    Create the weekly report for: Brooks, Ronald
    Create the weekly report for: Brown, Jeremy
    Create the weekly report for: Caballero, Jose
    Create the weekly report for: Camacho, Antonio
    Create the weekly report for: Carson, Daylon
    Create the weekly report for: Cooper, Octavious
     Total records created: 12
    I added module 2 to your file.
    Attached Files Attached Files
    Last edited by orange; 01-04-2019 at 02:29 PM. Reason: Names restricted to B* thru C*

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,209
    some notes on the frmReports form will explain most of what I did. Not sure how much you can make use of on your own. tblReports supports this form.
    It should demonstrate what I referred to re form validation. As noted therein, I don't get any records in the Weekly (all) report because I don't supply a name to the query (I removed that prompt). The code shows how to validate the form and filter the report to the dates. What you'll need now is to wrap a report call in your recordset loop. I'm not saying you can take this and interject it into Orange's code - just that it provides a couple of puzzle pieces. I'd probably move the report opening Select Case block into its own procedure so that the loop code can pass the name and dates to it, then use those values as the report filter. I only used the dates. The idea of the (all) variation of your report is that it allows you to open it either for a single person or all (from your recordset).

    kkmsMasterMicron.zip
    Last edited by Micron; 01-04-2019 at 04:07 PM. Reason: added info
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Code isn't smart. It does what you ask with what you give it.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-08-2018, 07:48 AM
  2. Making report list item multiple times
    By DBR138 in forum Reports
    Replies: 3
    Last Post: 01-12-2018, 08:02 AM
  3. Report displays data multiple times
    By kermit in forum Reports
    Replies: 2
    Last Post: 05-11-2015, 02:50 PM
  4. Replies: 2
    Last Post: 04-08-2015, 10:01 AM
  5. Replies: 3
    Last Post: 02-04-2014, 03:33 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
  •  
Tech Forums: Microsoft Office Forums