?
Solved

Advice on Filtering By Form

Posted on 2009-02-19
17
Medium Priority
?
417 Views
Last Modified: 2013-11-28
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?
Main-Form.jpg
0
Comment
Question by:9XqUwH3S
  • 9
  • 6
  • 2
17 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 1600 total points
ID: 23685015
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.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1600 total points
ID: 23685142
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.
0
 

Author Comment

by:9XqUwH3S
ID: 23685375
Could I work-around this by creating a pop-up form with these commands on them?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:9XqUwH3S
ID: 23685473
Can you recommend a good place to look to copy code for this purpose (regardless of how it is performed)?
 
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1600 total points
ID: 23685748
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...
0
 

Author Comment

by:9XqUwH3S
ID: 23685865
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?
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1600 total points
ID: 23685999
<Is that okay with you?>

Absolutely.

<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.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1600 total points
ID: 23686016
Just note that I can't guarantee my schedule, but I will receive an email notification and I will check in.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 400 total points
ID: 23690566
9XqUwH3S,

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
whatsoever.
;-)

You can build these systems yourself depending on how complex your requirements are and your skill level.
Here are my favorite links:
Simple:
http://www.fontstuff.com/access/acctut08.htm
More Complex:
http://www.fontstuff.com/access/acctut19.htm

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.

;-)

JeffCoachman
0
 

Author Comment

by:9XqUwH3S
ID: 23690884
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!
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1600 total points
ID: 23691592
<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.

0
 

Author Comment

by:9XqUwH3S
ID: 23694163
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.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 400 total points
ID: 23695148
OOPS!

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:
http://msdn.microsoft.com/en-us/library/aa338202.aspx

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

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

;-)

Jeff
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1600 total points
ID: 23698202
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:
http://www.databasejournal.com/features/msaccess/article.php/3790286/Setting-Up-Right-Click-Menus-for-Access-2007.htm 

Customizing the Ribbon/building custom ribbons:
http://www.databasedev.co.uk/access2007ribbon.html

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:
http://www.microsoft.com/downloads/details.aspx?familyid=4329d9e9-4d11-46a5-898d-23e4f331e9ae&displaylang=en

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.


RightClickMenu.txt
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1600 total points
ID: 23698265
<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.
0
 

Author Closing Comment

by:9XqUwH3S
ID: 31548919
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%)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 23700409
Glad to help out!
Good luck with your project.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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