Possible to filter by form using a command button?

[trashchannel]

Hi, I'm an Access newbie. I've designed a fairly simple database to replace an old spreadsheet that was becoming unwieldy -- it was never really used so much for calculation as for data storage, so Access seems more appropriate anyway.

One of the functions I used in Excel was the autofilter, which I found very useful for zeroing in on specific portions of the data. I was wondering how I would do the same thing in Access, and then I discovered the "Filter by Form", which works just fine -- even better actually, since it has the whole and/or functionality.

I was wondering though if it would be possible to invoke "Filter by Form" using a command button. That would make it even easier, especially for some of my users who would almost certainly not know about "Filter by Form" otherwise.

 

[ir26121973]

Hi,

I'm pretty new to Access. From what i known there are 2 possible ways.

If you go to create a new macro. Under the first cell under action click on the box and choose 'Run Command'.

A second text box will appear 3/4's of the screen down and will say 'Command'. In the drop down box next to it, choose 'Filter by Form, save the macro giving appropriate name.

Then go to your form, open the toolbox and add a command button. If it goes into the 'Command Button Wizard' choose 'Miscellaneous' from the left hand list, then choose run macro and point to the one you want.

That should work.

The other thing you could do is create a query and in the criteria put the recorsd you are looking for. Like if you were looking for all employees called Bob, you'd put Bob in the Criteria.

Then as above create a new macro, then instead of choosing 'Run Command', select open Query and follow the on screen instructions.

Sorry it's a bit long winded, hope it helps. Those with a bit more knowledge on here, may have better ideas, but it works for me.

 

[trashchannel]

Thanks for your reply ir26121973, I really appreciate it.

Although I've used macros in Excel, I hadn't used them in Access until you suggested it. That does work well, but I realize now that while it opens the Form by Filter function, I now need another button in order to actually *apply* the filter! (I've hidden away the toolbar.)

Do you have any idea where I might put such a button?

 

[trashchannel]

I solved my problem by using this tool in order to create a Query By Form for my main entry form. It's not perfect, but it'll do for now. When I have more time I'll have a go at writing my own QBF so I can get it to do exactly what I want.

 

[Hagridore]

Okay, folks, I suspect I'm going to need something a bit more specific. Here's what I want to do. I want to program a button on a dashboard so that it will open a search form that is a replica of my main data entry form in Filter by Form mode. My users need to be able to type in any combination of criteria in any of over 30 fields and search what will be a database of well over a thousand records. I'm designing reports with queries that will answer some of the most oft asked questions, but there are a lot of others that will best be served by Filter by Form. This is especially true for situations where users will need to see all information for each record in the filtered list rather than some subset. Thanks for your help!

 

[pbaldy]

With that many fields I'd probably build dynamic SQL, as demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

You could do what the sample does and set the source of a subform or build just the wherecondition and use with this to open a form filtered:

http://www.baldyweb.com/wherecondition.htm

 

[Hagridore]

I guess I'm being dense or something. I'm not clear how the SQL directions apply to what I need to do. I have never done any programming in SQL. I have the form already created which I want to use for the filter capability. What I want to do is to click on a button in a dashboard and have it open the form I've already got created in Filter by Form mode. This will allow a person to type information in any field they need to search by. When the filter is applied (I'm assuming by creating an Apply Filter button on my form since the goal is to compile this into its own stand-alone app so that totally non-Access users can use it) each record matching the criteria specified by the search form should be listed, with the ability to either print all of them or navigate through the list using the navigation buttons. It appears the SQL listed in your example had a lot of information about the specifics of the input and output, all of which is normally taken care of by the built-in Filter by Form processes. Again, I'm sorry if I'm just missing it. :banghead:

 

[pbaldy]

Did you look at the sample db? The point was building a filter with a number of optional fields to search on. You can use a query and this if you want, but you won't like what happens in design view after you save the query and open it in design view again.

http://access.mvps.org/access/queries/qry0001.htm

You can use a query with Like and wildcards, but it doesn't work well with numeric values. As I said, I'd use the method in the sample db, modified to create the filter only, and use it with the other link I posted.

 

[Hagridore]

I understand that that's what your directions did. I don't want to create another query or query set...I just want to automatically open the form in the filter by form mode. The filter by form mode handles the criteria exactly like I want to. It opens a blank form, allows me to select any number of criteria by filling in the appropriate controls related to the records I want to find, and it returns a subset of the full database, one record per form, exactly like I want it to. I just need to be able to open this form and go to the filter by form mode, and I'd like to be able to do it directly from a dashboard. I may have to have the dashboard button open the form and then another button on the form itself shift to filter by form That'd be okay, but I'm trying to save my users from having anything that looks like Access to deal with. I want them to be able to do everything in the database without seeing the standard ribbons and such. :banghead:

 

[pbaldy]

Sorry, I've never used that feature. A quick Google turns up this code:

DoCmd.OpenForm "FormName"
DoCmd.RunCommand acCmdFilterByForm

See if it works for you.

 

[Hagridore]

Thanks a lot, pbaldy, for your help. I found a setting in the form that was not allowing filters. Once I changed that to "yes" I was able to make the form open as I want. It does allow searches based on multiple criteria from multiple fields. Unfortunately, I haven't been able to run the filter/do the search without clicking the "Toggle Filter" button in the ribbon. I created a command button that I think would work, but when the form opens in "Filter by Form" mode, the button is greyed out and non-functional. I think I saw something in another topic on these boards addressing that issue. I'm going to search, but if you have any advice on the matter, I'd appreciate it. I'm in awe at the rapidity of and the expertise behind the responses on this board!

 

[pbaldy]

Sorry, I've never tried to manipulate the built-in filter by form. I always do something customized, which is why I didn't realize at first what you were trying to do. It doesn't enable itself once you've put in something to filter by I assume?

 

[Hagridore]

Right. The form is open in the Filter by Form mode, allowing entering of criteria for search in any of the controls. The button that is supposed to run the filter isn't accessible right now...it's grey. However, the search happens exactly as I want it to if I click "Toggle Filter" in the ribbon.

 

[gemma-the-husky]

I forget the code offhand, but its something like

 screen.previouscontrol.setfocus
 runcommand accmdfilterbyselection  'might by slightly off

click in a control - then click a button with the code above, and you can successively apply filters to a form. Useful interface feature

 

[Hagridore]

Gemma-the-Husky, thank you for your reply. I'm not exactly sure where this code should be entered. Should the screen and focus command be entered in the button where the command is to open the search form in Filter by Form mode? Should it be in another location in the properties of the form? Thanks for your help.

 

[Hagridore]

I haven't heard back from anyone about this. Here's what the macro commands look like for my Filter by Form button. This is the button on the main dashboard which opens the search form in Filter by Form mode. Is this where the command line about focus needs to go? What is that code?

CloseWindow
Object Type Form
Object Name frmOpen
Save Prompt

OpenForm
Form Name frmFindRecord
View Form
Where condition
Data Mode
Window mode Normal

RunMenuCommand
Command FilterbyForm