Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249

    Best way to handle multiple filtered reports?

    I have a table (tblLabIssues) and a report (rptLabIssues) that have the following fields:

    Lab
    System
    OpenedDate


    ClosedDate
    Issue
    Impact
    Resolution

    I need to be able to filter the report on Lab, System, and ClosedDate. My initial thought was to put either some buttons on the report itself to filter, but I guess buttons do not work very well on reports? If that is the case, what would be the best way to allow users to filter by any of those fields? If at all possible, I'd like the filtering to take place on the report so that the user can, for example, open the report to see every issue and then filter down to the Lab and then to only Open issues as needed.

    Thanks for any help or insight you can give,

    Keith

  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
    Buttons don't work on report in PrintPreview, will in ReportView. But if you want dynamic filtering on any combination of those fields, better use a form http://allenbrowne.com/ser-62.html
    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
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by June7 View Post
    Buttons don't work on report in PrintPreview, will in ReportView. But if you want dynamic filtering on any combination of those fields, better use a form http://allenbrowne.com/ser-62.html
    Thanks June7. Right now I'm only displaying the report in ReportView (I think). The issue I have with using a form to filter the report is that the user has to know what they want to filter on before seeing the report. What about a combobox on the report to filter the Lab and Systems and then checkboxes for Opened or Closed tickets (although, those could be a single combobox as well I guess).

  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
    Well, try it and see what happens. You should see a combobox displays like a textbox - no dropdown.

    Why can't user see data on form and decide there what they want to filter for?
    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
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Let's say they are looking at all the issues in Lab A just to get an idea of the lab's status, when they notice what they think is a pattern with System X having issues. With the formed based filtering, they would first have to open the report to display all the issues. Then once they see a pattern, they would then have to go back to the form, add more filters in and then generate a completely new report. With the filters right on the report, it is much easier and quicker to select them there and have the results update.

    I guess another option could be to have a form that displays a "subreport" (if that is even possible) at the bottom of it.

    Another function that I would like to add to the report is a save to PDF button/option. That would allow someone looking at the filtered data to easily generate a PDF of what they were reviewing to share with others.

  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
    Yes, a report can be displayed on form.

    Button on report in ReportView could execute code to switch report to PrintPreview and run OutputTo command for PDF 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.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I guess another option could be to have a form that displays a "subreport" (if that is even possible) at the bottom of it.
    perfectly possible - after user has entered their revised criteria, you just have to requery the subreport.

  8. #8
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Thanks for the suggestions and guidance. I've now created a form that has a subreport on it. I've added the combobox cboLabFilter in the header of this form. I've tried a couple of different ways to filter the report based on the selection in the combobox from the web, but none of them work for me. The combobox Row Source is
    Code:
    SELECT [Lab].[Lab] FROM [Lab] ORDER BY [Lab];
    (I know, the table Lab should be tblLab, but I created it before I knew to add the tbl in front of it).

    What I currently have as the UpdateAfter event for the combobox is this:

    Code:
    Private Sub cboLabFilter_AfterUpdate()
    
    
        Me.Filter = "[Lab] = """ & Me.cboLabFilter & """"
    Me.FilterOn = True
    
    
    End Sub
    When I open the form, it shows all of the records. When I use the combobox, regardless of which lab I select, it just returns the OCL-1 lab. I have to exit the form and reopen it to see all of the records again.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am trying your combobox idea but my combobox on report does not have AfterUpdate event. And as I said earlier, it only displays as a textbox. Very odd.

    However, I tested button Click event to apply filter and it works.

    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.

  10. #10
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by June7 View Post
    I am trying your combobox idea but my combobox on report does not have AfterUpdate event. And as I said earlier, it only displays as a textbox. Very odd.

    However, I tested button Click event to apply filter and it works.

    If you want to provide db for analysis, follow instructions at bottom of my post.

    My combobox with the AfterUpdate is actually on the form and not the sub report in that form. I'll work on stripping the production data out of my db and enter some dummy data today so that I can upload here for others to look at.

  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
    You don't need a "subreport" on the form; move your combo's to the form's header, bind the form to the tblLabIssues table (set the form to continuous view) so the forms shows all the records on open. Then after each update of a combo dynamically modify the record source of the form to include it in the Where clause (I find it more reliable than using filters). Finally when you want to view the report (or export to PDF) you can save the form's record source as a query that acts as the report record source (basically you edit the QueryDef's SQL property to make it the same as the form's record source).

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

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ummm, you are aware that "System" is a reserved word??

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If combobox is on main form and not on the subreport (or subform), then Me.Filter will not work. Reference the subform/subreport container:

    Me.subreportContainername.Form.Filter =
    Me.subreportContainername.Form.FilterOn
    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.

  14. #14
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by Gicu View Post
    You don't need a "subreport" on the form; move your combo's to the form's header, bind the form to the tblLabIssues table (set the form to continuous view) so the forms shows all the records on open. Then after each update of a combo dynamically modify the record source of the form to include it in the Where clause (I find it more reliable than using filters). Finally when you want to view the report (or export to PDF) you can save the form's record source as a query that acts as the report record source (basically you edit the QueryDef's SQL property to make it the same as the form's record source).

    Cheers,
    Vlad
    Thanks Vlad, I'll look into doing this way as well.

    Quote Originally Posted by ssanfu View Post
    Ummm, you are aware that "System" is a reserved word??
    No, actually I was not. I'll change that our for something else, thanks.

    Quote Originally Posted by June7 View Post
    If combobox is on main form and not on the subreport (or subform), then Me.Filter will not work. Reference the subform/subreport container:

    Me.subreportContainername.Form.Filter =
    Me.subreportContainername.Form.FilterOn
    Thanks for pointing that out June7, I'll fix that and see what it does. I'm also attaching my sample db for this portion of the project for anyone to look at if that helps.
    Attached Files Attached Files

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Or if using a report:

    Me.subreportContainername.Report.Filter
    Me.subreportContainername.Report.FilterOn
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Storing filtered reports
    By CJ_London in forum Forum Suggestions
    Replies: 5
    Last Post: 09-18-2020, 05:42 AM
  2. Allow user to create custom, filtered reports
    By zzzjoshzzz in forum Access
    Replies: 5
    Last Post: 05-01-2015, 12:33 PM
  3. Reports filtered by Month
    By Douglasrac in forum Access
    Replies: 3
    Last Post: 01-27-2011, 05:01 PM
  4. Can't keep dates filtered on my reports!
    By Mr. Coffee in forum Queries
    Replies: 5
    Last Post: 12-02-2010, 11:27 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