Solved

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

Posted on 2003-12-10
3
3,205 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
[X]
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
  • 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

691 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