Results 1 to 6 of 6
  1. #1
    dragon232 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Posts
    24

    How To Make Filter Macro Apply to Reports when it Applies to the Form?


    Okay, here's the problem I'm having. I have a form built on a query, and I have introduced a filter button in the form (which uses a query to filter the main query on which the form is built). The filter button works, and filters as desired, however, when, in the form, I click a certain display Report button (with the report also being built on the same query as the form), the report shows all the data, without taking into account the filter I've applied. How can I make the report button take into account the filter settings I have applied using the filter button in the form?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If the report is using the query the form uses,then it MUST show the exact same data.
    if the query uses the form filters,the so does the report.

    are you using form references in the query?
    select * from table where [field]=forms!myForm!cboBox

  3. #3
    dragon232 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Posts
    24
    Quote Originally Posted by ranman256 View Post
    If the report is using the query the form uses,then it MUST show the exact same data.
    if the query uses the form filters,the so does the report.

    are you using form references in the query?
    select * from table where [field]=forms!myForm!cboBox
    Yes you are correct, thank you very much, it saved me a lot of time. I did something very stupid, I was having multiple filters instead of having only one, and assigning the values manually instead of taking them from a combo box in the query. I did it and now it's fixed, however I have a different problem. The form the filter applies to is a subform in my main navigation form. So when I click the filter button from the navigation form, it gives me the following error: "The action or method is invalid because the form or report isn't bound to a table or query". However it works fine if I just open the subform from the navigation form separately and click the filter button. How could I fix this? Thanks!

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    not sure since i cant see it. But if the subform is using multiple filters, then i dont see the query using it. They must be live filters.

  5. #5
    dragon232 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Posts
    24
    Quote Originally Posted by ranman256 View Post
    not sure since i cant see it. But if the subform is using multiple filters, then i dont see the query using it. They must be live filters.
    Okay let me try to explain a bit better. The filter exists in the "Salaries" form, which is built on the Salaries Query. It filters by the company employees are working for (as there are more than 1 company in the group). If I open this "Salaries" form, select the filtering criteria, and click the custom filter button I made, it filters them as intended, including filtering the reports that one can access from this form. However, if I open this "Salaries" form as a subform in my main navigation "HOME" form, I select the filtering criteria, and when I click the custom filter button, I get the error I mentioned above. The custom filter button is a macro running the ApplyFilter command which runs the filter query.

    The full error reads:
    "The action or method is invalid because the form or report isn't bound to a table or query

    You tried to use the ApplyFilter or SearchForRecord action or method. However the form or report you applied the filter to is not based on a table or query, so the form or report doesn't have any records to apply a filter to"
    I get what the error is, but I can't seem to see how to get around it. The problem is that because the "HOME" form is open, the "SALARIES" form is there only as a subform, and hence the ApplyFilter thinks I'm trying to apply the filter to the "Home" form, not to the "Salaries"... How can I target the "Salaries" subform?

  6. #6
    dragon232 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Posts
    24
    Hi, I got it to work if anyone else is interested in a similar problem! I erased the existent Macro and made an SetFilter Macro on the button with the filter as the query. Then in the filter query as the filter condition I wrote:
    Code:
    
    [field]=forms!myNavigationForm!NavigationSubform!cboBox


    Thanks!

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

Similar Threads

  1. Replies: 4
    Last Post: 07-11-2014, 05:51 AM
  2. Replies: 1
    Last Post: 07-02-2014, 11:22 AM
  3. Macro to apply filter to form
    By mattzkn in forum Macros
    Replies: 3
    Last Post: 12-11-2013, 12:57 AM
  4. Replies: 1
    Last Post: 04-28-2013, 09:43 PM
  5. Replies: 2
    Last Post: 02-25-2013, 10:47 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