[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2003-12-10
3
Medium Priority
?
3,223 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 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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