Results 1 to 4 of 4
  1. #1
    6thDAY is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    2

    Weird Behaviors Filtering a Report in a Navigation Form

    I have a Navigation Form with multiple tabs. Each tab contains a subform with a different report, as well as buttons. In one of my tabs, I have a report that I want to filter based on a certain criteria. I created a combo box on top of the report, which filters all items in the report based on a criteria.



    Assume we're on the specific tab mentioned, the diagram looks something like this:



    I have the following code in the Subform containing the combo box filter and report, with let's say the values in the combo box are 1-5. :

    Code:
    Private rpt As Report
        
    Private Sub Form_Load()
        Set rpt = Me.subreport_TestReport.Report
    End Sub
    
    Private Sub comboBox_FilterByID_AfterUpdate()
    
        rpt .Filter = "ID = " & Me.comboBox_FilterByID
        rpt .FilterOn = True
    
    End Sub


    I encounter the following issue with the above code as soon as I select a value in the combo box filter:




    I updated the code:

    Code:
    Private Sub comboBox_FilterByID_AfterUpdate()
    
        Me.subreport_TestReport.Report.Filter = "ID = " & Me.comboBox_FilterByID
        Me.subreport_TestReport.Report.FilterOn = True
    
    End Sub


    Now, the report filters based on whatever value I select from the combo box, but I encounter the following issues where the value I select does not populate in the combo box, but instead, the value is populated with the "last" value I selected, not the current one. Secondly, every other value I select to filter, I receive this error:




    The above error points me to the following code:

    Code:
    Me.subreport_TestCasesBCR.Report.FilterOn = True


    I don't know what's causing this issue and I posted the same issue on StackOverflow, but all I was told was to avoid using a Navigation Form. Unfortunately I cannot re-factor the code.

    Anyone know what is causing this issue?

    Please see download link for demo of issue: https://www.file.io/download/KiEFLdvY2nvH

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,897
    Hard to co-relate the image to the code so do your object references follow this syntax?
    Forms![main navigation form].[NavigationSubform].Form

    Access defaults the name of the subform to [main navigation form] and the sub container name to [NavigationSubform] IIRC. I'd also question the space that you have rpt as in rpt .filter

    Hopefully you're also aware that a nav form only loads one form at a time, so if you switch tabs, one closes and another opens so not possible to refer to another navigation subform from another. Probably not the reason why it complains that the object is closed or doesn't exist - more likely your syntax I'd say.

    Not sure I see the value in the first few lines of code when you can just have
    Forms![main navigation form].[NavigationSubform].Form.Filter = Me.comboBox_FilterByID
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    6thDAY is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    2
    Quote Originally Posted by Micron View Post
    Hard to co-relate the image to the code so do your object references follow this syntax?
    Forms![main navigation form].[NavigationSubform].Form

    Access defaults the name of the subform to [main navigation form] and the sub container name to [NavigationSubform] IIRC. I'd also question the space that you have rpt as in rpt .filter

    Hopefully you're also aware that a nav form only loads one form at a time, so if you switch tabs, one closes and another opens so not possible to refer to another navigation subform from another. Probably not the reason why it complains that the object is closed or doesn't exist - more likely your syntax I'd say.

    Not sure I see the value in the first few lines of code when you can just have
    Forms![main navigation form].[NavigationSubform].Form.Filter = Me.comboBox_FilterByID
    I tried your suggestion, using the syntax at the last paragraph, but Access was still giving me the same error.

    I've temporarily fixed causing the run-time error '2467', but the issue of the current value in the combo box remains.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,526

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

Similar Threads

  1. Report filtering by Form
    By tihmir in forum Reports
    Replies: 14
    Last Post: 12-16-2018, 11:16 AM
  2. Replies: 6
    Last Post: 02-22-2018, 02:12 PM
  3. Replies: 11
    Last Post: 02-08-2018, 04:04 AM
  4. Printing a report in a navigation form
    By DanKoz in forum Forms
    Replies: 2
    Last Post: 08-29-2011, 12:11 PM
  5. Weird Text on Report
    By j2curtis64 in forum Reports
    Replies: 1
    Last Post: 05-03-2011, 03:16 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
  •  
Other Forums: Microsoft Office Forums