Solved

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

Posted on 2003-12-10
3
3,149 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now