Advice on Filtering By Form

I'm looking for advice on how to go about creating and encoding a filter by form buttons on my Access 2007 main form.

I want the command button to change my form to look like it does when I use the toolbar's Filter By Form option and a command button that performs the same action as Apply Filter/Sort.
To summarize: I want buttons on my form that do the same things as the options on the toolbar.

I'm new to coding but would like to do this myself, more or less. I haven't found much stuff online that wasn't about advanced stuff like ADO and DAO programming.

Any thoughts, tips, insights or helpful suggestions, out there ot help me on my way?

Is this best suited for macros?
Who is Participating?
The easist way to do this as a novice is to use Access's built in wizards to build the code for your command buttons.

Place a command button on your form
When the wizard opens select Form Operations -> Edit Form Filter.
This should set up the button for "Filter By Form"

Do the same, using "Apply Form Filter" for your find records buttons.
Actually, since you want this to resemble your form, ignore my last comment.  That will bring up a query grid for you to enter the desired filter.

Instead, in the code behind your FilterByForm command button (Click Event), add the following line:

    DoCmd.RunCommand acCmdFilterByForm

To apply the filter:
    DoCmd.RunCommand acCmdApplyFilterSort

However, I believe that the command buttons on your form become disabled during a FilterByForm.  If that is the case, the user may have to use the right-click menu (this is automatic - no additional code needed) to apply the filter.
9XqUwH3SAuthor Commented:
Could I work-around this by creating a pop-up form with these commands on them?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

9XqUwH3SAuthor Commented:
Can you recommend a good place to look to copy code for this purpose (regardless of how it is performed)?
Curious why you want to avoid using the built-in toolbar for this?

Would a custom tool-bar or short-cut (ie: right-click) menu work for you?  

<creating a pop-up form >

This sounds pretty close to a custom short-cut menu...
9XqUwH3SAuthor Commented:
I see your point. I wanted to search by field without using a seperate form including using multiple criteria.
Can I get back with you, tomorrow, after I send a picture of the Allen Browne code I adapted (and decided not to fiddle with)?
It involved creating combo boxes, etc., in the form header and a search/cancel command button. I was having some snafus, but I think it was pretty close to working.
I was hoping to avoid adding more junk on my form. I wanted to idiot-proof the user form so other's could use it in my absence.
I think I can present it in a clean-cut and straightforward manner that will make it clear as to what needs tweaked.
Is that okay with you?
<Is that okay with you?>


<after I send a picture of the Allen Browne code I adapted >

Ideally, upload a sample database containing the relevant objects using the attach file feature.  Just remove any sensitive data first.
Just note that I can't guarantee my schedule, but I will receive an email notification and I will check in.
Jeffrey CoachmanMIS LiasonCommented:

Please remember that in "Filter" mode, you cannot really "Edit" records.
(Change a Name, Delete a record, add a record... ect.)
Perhaps this is irrelevant, but many people ask, just thought I'd mention it.

Also note that creating a system like this may not be what a beginner would call "easy".
On this point, I side with mbizup. :
  ("Would a custom tool-bar or short-cut (ie: right-click) menu work for you?")
... as this solution could be implemented in a matter on minutes, with no code required

You can build these systems yourself depending on how complex your requirements are and your skill level.
Here are my favorite links:
More Complex:

I personally find it much easier to filter the form's underlying recordset with a query than to use the Filter properties.

A bit of history on these systems:
This "Universal form filter" seems to be somewhat of a "Holy Grail" for Access developers.
Therein lies it's issues.
The more options you need to build into it (to make it easy for the users), the more complex it becomes for you, the developer, to create.

For example:
AND and OR logic.
How to handle Null criteria fields (Fields for which a user left blank)
Criteria spelling errors

But mbizup can assist you with any of these issues as well.

Good luck and keep us posted.


9XqUwH3SAuthor Commented:
Thank you, both. It may be as much as 24 hours before I can implement, get back and award points. All this stuff looks like sound advice.
See you then!
<implement, get back and award points.>

Please do follow-up with your sample and explanation as you mentioned in ID:23685865 before you actually close this question.

I had suggested the custom command bar and short-cut menu without actually seeing this.

My gut feeling is that this will meet your needs, but it would be good to get additional details to make sure that is the way to go.  I can also give you instructions on how to build those menus, if you need it.

9XqUwH3SAuthor Commented:
A.) I think the custom toolbar might be most practical for now. Is there a web tutorial you can refer me to, or, is it easier simply to explain it?
I look forward to hearing from you.
B.) Also, as my skills grow, which strategy do you recommend I look to in order to employ the ability to edit a filtered/queried recordset? Using plain code? ADO?
I was hoping for a 1-word/1-sentence answer, so as to know what to pay close attention to as I study. Thank you.
Jeffrey CoachmanMIS LiasonCommented:

I see that you are using Access 2007, where creating anything custome is more involved.
I fear you may have to get wise to the ways of the Ribbon:

Let's see what mbizup has to say....

(Let's hold off on "B." untill we get this squared away.)


Access 2007 - egads!  Thats why it took me so long to get back to you.   I haven't experimented with ribbons before, but now is as good a time as any.  :-)

<A.) I think the custom toolbar might be most practical for now. Is there a web tutorial you can refer me to, or, is it easier simply to explain it?>

A little of both...

- You can create a custom Ribbon containing just the filter/sort group (or whichever of it's components you want to see), and associate it to a particular form by the ribbon property in the form's design.
- You can also create a right-click menu for simpler filters, like Filter By or Excluding Selection.  In Access 2007, this can be done using macros.  This can be associated with a form by calling the Macro in the shortcut toolbar property.

The problem with applying either of these options to a form is that when you choose "Filter By Form", the functionality of these ribbons and toolbars is lost.

The work around is to apply them to your database as a whole, using Access Options in the Office "blob".

The following links will get you off to a fast start doing this (this was the first time I have experimented with ribbons and short-cut menus in A2K7 myself).

Using Macros to set up right-click shortcut menus: 

Customizing the Ribbon/building custom ribbons:

Excel downloads containing all of the MSO control ID's for the built in toolbar functions.  This downloads them all, but the only file you will need for now is AccessRibbonControls.xlsx:

When you go to try this yourself, I recommend actually typing in the XML into the table rather than copy/pasting it.  That will give you a much better feel for what the XML does, and also the case-sensitivity involved.

I found that those three links and some trial and error were all that I needed as a novice at this to create this uploaded sample.  I've changed it to a .txt extension so that I could upload it.  Rename it to .accdb on your end.

Also if you enter the database without the shift key, you will see the custom ribbon at all times.  If you hold down the shift key while opening the DB and keep it down through Access's security popup, you will be in design mode and the regular toolbar will be restored when frmTest is closed.  Hitting F11 will bring up the Navigation Pane.

<B.) Also, as my skills grow, which strategy do you recommend I look to in order to employ the ability to edit a filtered/queried recordset? Using plain code? ADO? >

Take the time to learn how to use both the built in options (filter-by-form, etc) and building your own custom filter forms (involvng queries and code) as shown in some of Jeff's links. Both methods have their applications at any skill level. I use both methods professionally.

There are plenty of variations on that custom filter form as well. One of my favorites is to place unbound controls in the top half of a form for each field on the form I want to filter. Combo boxes allow users to select from data that already exists.

I use a code behind a custom "Apply Filter" button to write all of the User's criteria in the form of a SQL statement.

That SQL statement is then used to populate a listbox in the bottom half of the form to display a list summarizing records matching the user's criteria. Code in the listbox's double-click event will open the form in question to the record selected from the listbox.

<I was hoping for a 1-word/1-sentence answer>
LOL! Just find and try as many examples as time will allow, and see what works best for you.
9XqUwH3SAuthor Commented:
mbizup & boag2000, you both sunk so much time and energy into instructing me! I've converted this to .pdf doc so I can review this, offline. Once I realized how far over my head I found myself, I realized that I need to go back to the proverbial 'Brick pile". But I like that idea of customizing the Ribbon. And I understand that SQL manipulation is going to be the expert way to do stuff, when I get to that.
Thank you for my online college experience, here!

(The points added up to 80/20%)
Glad to help out!
Good luck with your project.
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.

All Courses

From novice to tech pro — start learning today.