Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Separate Reports for each Criteria (2000 - SR2)

    I generate a weekly report (Overdue) which lists information for all Departments which are behind (overdue) on getting issues closed. The Departments in this report change regularly, depending on who had overdue items. Currently I save this report as an .rtf file, open it, manually split the report into separate reports for each Department who has the overdue items, and then email those "Department specific" reports to the appropriate manager. No section has access to any other sections information.

    What I would like to do is to eliminate the time spent manually tearing the report apart and making individual reports for each Department. Is it possible to set a criteria on the [DepartmentName] so that when the Overdue Report is ran, it will create separate reports for each Department? I then can save these as .snp files, and email them accordingly, with no additional manual work needed to separate the total file into separate Department files.

    As always, I thank you in advance for your assistance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    Check out <post#=275460>post 275460</post#> for an approach using SendObject, and the thread starting at <post#=218831>post 218831</post#> for an approach using Automation to control Outlook from Access.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    Once the separate reports are generated they will be saved on our network, and then emailed out as a "packet" of information. So I don't really need all of the email coding information. I did try to hack up the code you showed in the other post, and tried to change it for my needs, but was not successful in getting it to work. Basically I just need to get the report to open with an individual report for each of my "over due" sections. Once I have the multiple reports I will save them as .snp files to our network.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Separate Reports for each Criteria (2000 - SR2)

    You can't get a report to "open with separate reports". You have to use code to pass the criteria for each report using the DoCmd.OpenReport statement to print or preview the report. However, you can transfer the report directly to snapshot format in code.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    In the code in <post#=275460>post 275460</post#>, the instruction

    DoCmd.SendObject acReport, stDocName, acFormatRTF, _
    "Postmaster", , Employee, "Report Text", _
    "Here is the test report.", False

    can be replaced by something like

    DoCmd.OutputTo acOutputReport, stDocName, acFormatSNP, _
    "C:ExportReport" & gstrEmployeeName & ".snp", False

    to export the individual reports to .snp files in a fixed folder.

    If you would like specific help, please provide some more details (name of the record source of the report, ...)

  6. #6
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    The Report feed off of "qryOverdue", so the [Section] is already filtered to those which have over due items.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    Here is a modified version of the code from the post I referred to, adapted for the information you have provided:

    1. Declare a global variable in a standard module:

    Dim gstrDepartmentName As String

    2. Put the following code in the On Open event of the report:

    Private Sub Report_Open(Cancel As Integer)
    If Not (gstrDepartmentName = "") Then
    Me.RecordSource = "SELECT * FROM qryOverdue WHERE DepartmentName = " & _
    Chr(34) & gstrDepartmentName & Chr(34)
    End If
    End Sub

    If gstrDepartmentName is blank, the report will open normally, otherwise it will limit the record source to the department whose name is in the variable.

    3. Use this code to generate the reports. For illustration purposes, it is the On Click event procedure for a command button cmdOverdue. The reports will be named Overdue_<department>.snp with the name of the department substituted. Adapt the path and filename as needed. (It must be unique for each department.)

    Private Sub cmdOverdue_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilename As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryOverdue", dbOpenForwardOnly)

    Do While rst.EOF = False
    gstrDepartmentName = rst!DepartmentName
    strFilename = "C:ExportOverdue_" & gstrDepartmentName & ".snp"
    DoCmd.OutputTo acOutputReport, "Overdue", acFormatSNP, strFilename, False
    rst.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    gstrDepartmentName = ""
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub

  8. #8
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    Loaded everything up, and tried it, however I am not ending up with any completed .snp file. I set up a temporary folder under C:/temp/. I think I may not have the model set up correctly. I just copied and pasted your 'Dim gstrDepartmentName As String' into a new module, and named it DepartmentName. When I press my command button the screen flashes quickly, like a program is trying to run, however, as I mentioned, I am not ending up with the end files.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    Click in the line Private Sub cmdOverdue_Click() and press F9 to set a breakpoint.

    When you press the command button on the form, code execution should pause in the line mentioned above. You can now execute the code step by step by pressing F8 repeatedly. You can inspect the value of variables etc. by hovering the mouse over them. Try to find out why no files are exported.

  10. #10
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    Not sure what's going on. When I pass my mouse over the first line:

    gstrName = rst!DepartmentName, it shows = Empty

    When I run my mouse over the second line:

    strFilename = "C:tempOverdue_" & gstrDepartmentName & ".snp", it shows = ""

    However, if I then move the yellow line arrow (to the left) back up to the first line, my Department Name value has been added, and when F8 back to the second line, the correct file path is now shown.

    But, if I cancel out of the VB editor, go back and press the Command button again, and go through the F8 steps, the empty and "" values are shown again.

    Also, when I try to compile by code, I keep getting a variable error pointing back to the "onOpen" event of the report. Error is "Compile Error: Variable not defined"

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    When the code is first paused, most of the variables do not have a value yet; they are assigned as you step through the code.

    The code should not contain onOpen. Where do you have this?

  12. #12
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    Its the OnOpen event on the Report:

    If Not (gstrDepartmentName = "") Then
    Me.RecordSource = "SELECT * FROM qryOverdue WHERE DepartmentName = " & _
    Chr(34) & gstrDepartmentName & Chr(34)

    I am getting the Event error on that first line, specifically the gstrDepartmentName = ""

    (Sorry, I typed the code identifiers wrong the first time)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    Open the report in design view, and activate the Event tab of the Properties window. Have you entered the code directly in the On Open event there? If so, clear that box, then select [Event Procedure] from the dropdown list. Next, click the ... to the right of the dropdown arrow. This will take you to the form module. That is where the code should be.

  14. #14
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    The code is the Event Procedure for "OnOpen", not in the properties line.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separate Reports for each Criteria (2000 - SR2)

    We're not getting anywhere this way. Can you post a stripped down copy of the database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it. (Of course, this only helps if you're using Access 2000 or later.)
    <LI>Attach the zip file to a reply.[/list]That will allow us to view the problem directly.

Page 1 of 2 12 LastLast

Posting Permissions

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