Results 1 to 7 of 7
  1. #1
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    Subform Filters not working when the form is opened from a report button

    I used to have a Main Menu with a button to open an order confirmation Form that users could navigate through.
    I am trying to change that so the user will first open the Report to view an order confirmation and then click a button "edit" on the Report to open the order Form to the correct record.

    On the order Form, I have 4 subforms all sourced from the same table but with different filters and defaults (filtered based on [Seller] and [Product] both of which have two options) so that the user does not need to input these defaults every single time.

    These filters work perfectly when the Form was opened from a button on the Main Menu, but when I try to open the Form from the Report, the filters don't work at all so that all records are displayed on all 4 subforms (The parent/child link works fine so they are still only displaying the subform records that corresponds with the main form record).

    I'm using a macro for the button

    Code:
    OpenForm
           FormName    F-OrderConf
               View    Form
          FilterName
    Where Condition  = [ConfNum]=[Reports]![R-SampleOrderConf]![ConfNum]
        Window Mode    Normal
    The filters for the subforms are on the subform property sheet.


    I can't figure out what is happening and how to keep the filters for all subforms.

    Let me know if there is any more info I can give about what is going on.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Assuming your report contains multiple records, I suppose this could work if the action was taken by clicking on a specific record. However, if using a button, there would be no "current" record since the button would get the focus, hence there'd be no filter. Speculation though, since I've never used a report to open a form. If you have to do it in this unusual fashion, consider passing the filter to the OpenArgs property of the report - IF you're not trying to open the form based on a single record in a report with many records. When you click the button, you can pass the report's OpenArgs to the opening form. Alternatively, I guess you could use a global variable, but IMHO, those should be avoided if there are better ways. I'd consider the OpenArgs property to be better.

    If it is a multiple record report, then I have no idea, since AFAIK, this would be done by passing a selected record, which means you'd have to click on the record, not a button.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    The way I have my report designed is to only have one confirmation number, which corresponds to the single parent record on the form.

    Here is a skeleton of the report layout:
    [Seller] 1
    [Confirmation] 4321
    [Product] 1 Multiple Records
    [Product] 2 Multiple Records
    [Seller] 2
    [Confirmation] 4321(Same as above)
    [Product] 1 Multiple Records
    [Product] 2 Multiple Records

    And the button does bring up the correct parent record, and the correct child records, it is just that the child records are not filtered between the four subforms.

    The form layout:

    Main Form [Confirmation] (single record)

    Subform 1 filter [Seller]1 and [Product]1
    Subform 2 filter [Seller]1 and [Product]2
    Subform 3 filter [Seller]2 and [Product]1
    Subform 4 filter [Seller]2 and [Product]2


    I have never used OpenArgs before so I will need to do a bit of research on how to do that.

  4. #4
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    I still have not figured out OpenArgs, but I tried setting the filter on one of the subforms as an event to test out.

    I tried this code for both the On Load event and then the On Open event of the subform

    Code:
    Private Sub Form_Open(Cancel As Integer)
        Me.Filter = [Seller] = "Seller 1" And [Product] = "Product 1"
        Me.FilterOn = True
    
    
    End Sub
    I am new to VBA so I also tried Me.Form.Filter since I have seen both ways somewhere on the internet.

    With either of these events the subform does not display any records at all.

  5. #5
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    I've tried a new thing that is also not working.

    I have added to the macro on the button so that the onclick event is this:

    Code:
    Open Form
         Form Name: F-OrderConf
         View: Form
         Filter Name:
         Where Condition = [ConfNum]=[Reports]![R-SampleOrderConf]![ConfNum]
         Data Mode:
         Window Mode: Normal
    Apply Filter:
         Filter Name:
         Where Condition = [Seller] = "Seller 1"
         Control Name: Forms!F-OrderConf!OrderConfInfo-F
    Now when I click the button I get error 3021 "The Setfilter (or apply filter since I tried both and got the same message) action requires a valid control name that corresponds to a subform."

    I have tried both "OrderConfInfo-F" which is the name of the subform control as well as "F-OrderConf-PSF" which is the name of the subform itself. I get the same message both times.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As I mentioned, whatever I've read about this "reverse" technique involved clicking on a report record to use one or more key values from that record. I don't believe that there is anything in the current record property when you click on a button. The focus has already moved off of the record onto the button, AFAIK.

    That being said, I don't use macros, so I'm guessing on this - in code, a form is oft opened using the DoCmd object. One of the parameters allows for a named filter, which is typically a stored query. Another is the WHERE condition, which is like the WHERE part of a sql statement without the word 'where'. What you posted looks like a where condition assigned to a filter. First though, ensure that you're requerying the subforms after applying the filter.

    As for this
    I have tried both "OrderConfInfo-F" which is the name of the subform control as well as "F-OrderConf-PSF" which is the name of the subform itself.
    I do believe it wants the control that contains the field upon which you are linking between the main and sub - not the subform control. If you want to post a zipped copy of your db, someone here can probably help figure it out. I may not be able to since you've likely used feature that are incompatible with my version.

    BTW - you should not use special characters (save perhaps the underscore) in any object names. F-OrderConf-PSF is a no-no. You are asking for more work at best, and trouble at worst. https://access-programmers.co.uk/for...d.php?t=225837

  7. #7
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Of course the answer was far more simple than I was trying to make it.

    I didn't have any query to use as a filter, and realized I should probably make one. So I just changed the record source of the subforms from the table to a built in query with the filters written in as criteria.
    Didn't have to do anything else, all the records came up as they were supposed to and I can add more without any problems.

    Thanks for the heads up on the object names. I had only ever heard "Don't use spaces! Whatever you do, don't put in any spaces!" So I made sure not to do that, not realizing that "spaces" meant spaces and other special characters. Changing names will be a big project in itself.

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

Similar Threads

  1. Replies: 6
    Last Post: 04-17-2017, 08:10 PM
  2. Replies: 31
    Last Post: 10-28-2016, 10:24 AM
  3. Replies: 5
    Last Post: 10-18-2016, 06:00 AM
  4. Replies: 4
    Last Post: 10-08-2012, 05:33 PM
  5. Replies: 8
    Last Post: 11-08-2011, 05:11 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