Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Applying filters using VBA when "Apply Filter" button is NOT pressed.

Posted on 2003-12-10
3
3,173 Views
Last Modified: 2007-12-19
I am trying to filter the data on a form using the filter by form method, but I want to either have a button on the form to apply the filter, or apply the filter when the filter is closed.  I want to have the menu option disabled for filtering.

Here is some current code that I have...
Private Sub Form_Open(Cancel As Integer)
   DoCmd.RunCommand acCmdFilterByForm
end sub

here is the code I am trying to implement, with error
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
  If ApplyType = acCloseFilterWindow then
     DoCmd.RunCommand acCmdApplyFilterSort
  end if
end sub

error message - error 2046
The command or action "Apply Filter/Sort" isn't available now.

In general, I have not been able to have command buttons on such forms, because the filter-by-form method disables the command buttons.  (A slight work-around - set the visible property of the cmd button to false, activate the filter by form, then set visible to true - unfortunately, button events still do not work.)

The FormFilter opens correctly, but when the filter is closed, the Filter property is still null.
0
Comment
Question by:Phythaelic
  • 2
3 Comments
 
LVL 3

Accepted Solution

by:
bossjohnc earned 125 total points
ID: 9919064
Yes, this is a difficult one that I've come accross before too.

The solution lies in having another form with a single 'Apply Filter' button on it. When the user chooses to 'Filter by Form', this form should be opened in popup mode so that the user doesn't hide it when they go to insert their criteria.

The code behind the button should look something like this...

        DoCmd.Close acForm, "frmFilterEngage" 'Close your "button" form
        RunCommand acCmdApplyFilterSort 'Apply the filter to the form that falls in to focus.

This does work, although it looks like it shouldn't!

0
 
LVL 2

Author Comment

by:Phythaelic
ID: 9927364
It works, but I made some improvements, you might find interesting...
Add "frmFilterEngage" that contains the button(s) as a subform. (if you disable the dividing lines, and other formating characteristics, the buttons look like they are on the original form...)

frmFilterEngage button revised code:
   RunCommand acCmdApplyFilterSort

Revised code for Form_Open:
  me.frmFilterEngage.visible = false
  DoCmd.RunCommand acCmdFilterByForm
  me.frmFilterEngage.visible = true

You can actually add any buttons you want, and they won't be disabled in the filter mode.  Apparently, the code behind acCmdFilterByForm doesn't disable objects that are not visible.

Thanks!  This adds a bunch of functionality.
0
 
LVL 3

Expert Comment

by:bossjohnc
ID: 9929261
Fantastic! - even better!

It's great when both parties take something away : )
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question