Results 1 to 4 of 4
  1. #1
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16

    Filter Report record source on separate form checkboxes

    Good afternoon! I need some help filtering a report. I have 3 tables. Table 1 is my main "top level" table. Table 2 is the "mid level" table, and Table 3 is my low level table. For clarity, data is like this:

    Table 1 data: 3.1, 3.2, 3.3...etc
    Table 2 data: 3.1.1, 3.1.2, 3.1.3, 3.2.1, 3.2.2, 3.3.1, 3.3.2, … etc.
    Table 3 data: 3.1.1[a], 3.1.1[b], 3.1.1[c], 3.1.2[a], 3.1.2[b], 3.1.3[a], 3.2.1[a], 3.2.1[b]… etc.

    I have my report set to group and print the following on each page (among other things not relevant to this question):

    Pg 1:
    3.1
    3.1.1
    3.1.1[a]

    Pg 2:
    3.1
    3.1.1
    3.1.1[b]

    And increments through Table 3's data which increments Table 2's data, which increments Table 1's data so my last page is:

    3.5


    3.5.6
    3.5.6[d]

    I have that part of the report working fine. There is also a ton more data that goes with each record, but I have not listed them here for brevity.

    An enhancement request was put in to create a check box form to filter the report by the selected Table 1 field. I have the 5 checkboxes created on the form and a command button that opens the report, but I can't figure how to filter my report on the selected checkboxes.

    The report is not based on a created query, but the record source for the report is a SELECT query from the tables. Any idea on how to filter the report?

    Thanks!!

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The OpenReport method has several arguments, 2 of them being a filter and a where clause argument.
    Code:
    expression.OpenReport (ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
    You could construct either one of those in your form and apply it to your openreport call.

  3. #3
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Quote Originally Posted by moke123 View Post
    The OpenReport method has several arguments, 2 of them being a filter and a where clause argument.
    Code:
    expression.OpenReport (ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
    You could construct either one of those in your form and apply it to your openreport call.
    I got it figured out using the Where condition. Thanks!

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Good luck with your project.

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

Similar Threads

  1. Replies: 6
    Last Post: 08-10-2018, 07:22 AM
  2. Replies: 20
    Last Post: 03-16-2017, 08:36 AM
  3. Trying to set sub-report source filter in VBA
    By GraeagleBill in forum Programming
    Replies: 9
    Last Post: 02-06-2016, 09:42 PM
  4. VBA for Report Filter based on Form Checkboxes
    By Kirsti in forum Programming
    Replies: 11
    Last Post: 04-23-2014, 04:52 PM
  5. Replies: 14
    Last Post: 05-25-2012, 02:40 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