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

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.
LVL 2
PhythaelicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bossjohncCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PhythaelicAuthor Commented:
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
bossjohncCommented:
Fantastic! - even better!

It's great when both parties take something away : )
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.