Using Option Groups to filter an Access Form

stevid
stevid used Ask the Experts™
on
Hi,

I have designed out a nice access 2003 database in which i have multiple forms and tables. I have one form in particular based on a table with a few hundred results.

One of the fields on the form is a lookup box with about 5 options to select from which we will say are option A - Option E.

The form in question also contains sub forms which may complicate things a little.

I have now placed an option group on the form and i want people to be able to select "All Records", "Option A only", "Option B only" etc etc

How can i filter the results based on this option box.

I have searched and searched the net and come up with all sorts of ideas but am not succeeding in finding an accurate result.

Anyone any ideas???

Thanks

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

an option group is always handled as one single value, numbered from 1 to X.

So you only need to create an unbound option group (for example in the header of the form) and assign the values any of the options from "All" over "A" to "E".

The you assign a change event to this option group and use a simple Select Case statement to switch between the option group values and create a filter in all the different options, in the end you can exchange the "Filter" property of the form with the new value and set the "FilterOn" property of the form to "True". It doesn't matter if you have subforms, normally you only filter the main form and the subforms showing the values of the different records which the filter displays.

Cheers,

Christian

Author

Commented:
Hi Christian,

Thanks for the reply,

I am only new to access and a lot of my issues are coming from Filter Commands and record Set Commands,

Could you please clarify how to use filter commands or point me to a link that explains it all in simple terms,

Your reply above looks spot on though, I just have no idea how to do the filter commands,

Thanks
Hi,

if you've added an option group you should have seen the assistant to add the options and the value assign. So if you now have an option group named "MyFilterFrame" then you can add a "AfterUpdate" event to the frame (not the options itself, add it to the frame!) like this:

Private Sub MyFilterFrame_AfterUpdate()
    Select Case MyFilterFrame
        Case 1   ' All
            Me.Filter = ""
            Me.FilterOn=False
        Case 2   ' Option A
            Me.Filter = "WantedFilterField = 'Option A'"
            Me.FilterOn = True
' and so on...
.
.
    End Select
End Sub

You must of course change the "WantedFilterField" to the field of your table where you want to set a filter and the value "Option A" to the value you want to filter. And you must add one Case statement for any value of your option group like shown above.

Cheers,

Christian
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks for all the help,

Can i just ask you do you have any more info on "me.filter", I have googled it lots and cant find anything useful on it,

Also i have seen a of people changing the record source??? Do you know anything about this or is this better or worse??

Thanks
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007
Commented:
"an i just ask you do you have any more info on "me.filter","
The Filter property of a Form  (or Report) is equivalent to including a WHERE clause in a query (instead of a table) that might be driving the Form.  It's just that simple. So, in the example above, it's like saying:

WHERE [SomeFieldName] = <SomeValue>  
ie
WHERE [WantedFilterField] = "Option A"

"Also i have seen a of people changing the record source??? Do you know anything about this or is this better or worse??"

Using the Filter property results in much better performance that changing the Recordsource.  When you change the Recordsource, Access has to do a lot of extra work, link re-bind all the bound controls to the new Recordsource, etc.    Using the Filter property simply re-applies the equivalent of a WHERE clause. Occasionally, there may be a reason to change the Recordsource, depending on how things are designed, etc.  

mx
Hi,

and to add something more, the "Me" in the statement above is a reference to the current object which is the form object inside of the code of a form. You will also find the "Filter" property in the properties of the form, this is what you access with "Me.Filter".

Filter or RecordSource:
Using a filter is NOT reapplying a WHERE clause to the data, no new SQL command will be executed when using a filter. So indeed you cannot simply say that changing the record source is less performant. Please read exactly what mx said: "an EQUIVALENT of a WHERE clause", not really a WHERE of a SQL command.

Using a filter always means that first the SQL command which is used to get the data from the table(s) is executed completely after loading the form and after the whole data is loaded Access filters the data record by record - this is really fast in case of a local database so there is not much difference in this case.

But if you ever work with a database backend which is not on the same computer then it is a good idea to requery the record source with parameters because in this case only the data which is filtered by the WHERE clause will be transported to the client - which is a lot faster than using filter, especially in big tables.

Changing the record source by reapplying a new SQL command is of course a little bit slower than requery a static SQL command with parameters but in case of big result sets it is of course very much faster than using a filter on a remote database - because you transport a very much smaller amount of data over the network.

Using filters has big advantages if you want more flexibility to the user: The user can simply create his own filter criteria (his own "WHERE") by using the built-in filter methods like "filter by selection" or the many predefined filters like "all dates of the last month" and so on. This is only possible if the SELECT used in the record source returns as many records as you need at least for this special form (which does not automatically mean that you should always load a complete table into a form - always think about what you really need, only select the columns you need and the rows you need and simply forget the existance of "*"...).

Filters are a lot faster than requerying a table if you have not too many records in the SQL result - because the recordset of the form always will have all records available with or without filter, so you can really switch fast between filtered and unfiltered resultsets, no data will be transported over the network (in the network backend scenario) when changing the filter.

So it depends on your need: If the users of your form will search heavily using custom filters on your form then filtering is the best method. If you only want to display a filtered result once or if you have big tables to load using a WHERE in the SQL command of the record source will be faster as you don't need to load the complete table first - but with the disadvantage of course that you can only filter the prefiltered records you got with the WHERE clause of the SQL command.
For example, I have a database where users displays all items of all orders ever made with this database. So to use both methods I have some drop down lists which does the first filtering like "only display the items of the last 30 days" (because delivery entrance only need the actual orders) or "only display part types" (which filters out any comment line or supply of service and so on because only parts can reach the delivery entrance) or "only show closed orders" (so only the orders which got a PO number will be displayed).

These "filters" will be set through parameters of a stored procedure in this database so the result set will be small and fast, now the users of the delivery entrance can additionally filter this result set to a special PO number or a special vendor and so on - the list don't need to be refreshed by the users (only sometimes) and using the filter can be done locally depending on the current need.
The dropdowns also contains an item "All records" so if someone really want to search through all records he can do that. But that means, around 150,000 records will be loaded to the client. If the user is not patient and don't wait until all are loaded and uses the local filter too early it can result in crashes of Access - so Access also have some problems using filters.

You see, there is no simple "use this or use that" answer for this question, you must decide it depending on your need in any new form.

Cheers,

Christian
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Christian ... it's right out of the Help File for Filter Property:

"The Filter property is a string expression consisting of a WHERE clause without the WHERE keyword."

""Me" in the statement above is a reference to the current object which is the form object inside of the code of a form"

Ahh ... that's not quite correct either.  Me refers the Form (or Report) module or Class Module you are currently in.  Again from VBA Help:

"The Me keyword behaves like an implicitly declared variable. It is automatically available to every procedure in a class module. When a class can have more than one instance, Me provides a way to refer to the specific instance of the class where the code is executing. Using Me is particularly useful for passing information about the currently executing instance of a class to a procedure in another module. For example, suppose you have the following procedure in a module:"

Unfortunately, I really don't have time to get into all the nuances of what JET does retrieving in response to what you posted above, but I think there is a lot more going on than you may be aware ...

mx
mx....

yes, I didn't wrote that it is not written in the same way as a WHERE clause without the keyword WHERE - I only said that it is no WHERE of a SQL command as a filter doesn't execute a SQL command.

Oh man... I really don't know what you think is wrong with what I said above. The VBA help file you quoted says exactly the same as I: It is a reference to the current object - and by the way not only forms and reports, ANY object, and forms and reports are class modules, too.
This is no VBA speciality, it is the same in all programming languages which supports object oriented programming. It's "Me" in VB6, it's "Me" in VB.Net and "My" in VB.Net for base classes. It's "This" in other languages and so on. The definition of this variable is always the same: It is a reference to the currently loaded object, in other words, the current instance of a class module. That is, in case of a form which is opened with DoCmd, always one instance.
Please don't try to correct things which don't need to be corrected.

the rest I leave uncommented...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
"and by the way not only forms and reports, ANY object, and forms and reports are class modules, too."
In Access (not concerned about elsewhere), It's Form Modules, Report Modules and Class Modules.  Not sure what you mean by "ANY".

And what I posted @ http:#a33930670 is in fact correct, so ... that does not need any 'correcting' :-)

mx
I didn't correct you, I extended what you said.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Humm ... then I really must have read this wrong:

"Using a filter is NOT reapplying a WHERE clause to the data, no new SQL command will be executed when using a filter. So indeed you cannot simply say that changing the record source is less performant. "

Oh well ...
Oh, maybe you need to read everythíng bevore you write, so please also quote the next sentence:

"Please read exactly what mx said: "an EQUIVALENT of a WHERE clause", not really a WHERE of a SQL command."

I for myself will not comment anything else abou this here as my time is too short to discuss senseless here and it's also not the right place as it has nothing to do with this thread anymore.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
That sentence has nothing to do with the first two per se.  And I did read that sentence.

mx

Author

Commented:
Hi,

Thanks for the replies :-)

I nearly have it now but i sill have one issue,

Me.Filter = "WantedFilterField = 'Option A'"

I am struggling to get this line to work, When i use it i am getting a dialogue box up asking me for data evn though i have the Wanted Filter Field in,

Any ideas???

I have obviously changed the wanted Filter Field to my own field names

Hi,

this was of course only an example so you must exchange the text "WantedFilterField" with the real name of the field you want to use. If you have spaces or special characters in your fieldname, surround the name with brackets like this: "[Wanted Filterfiled]".
Also the value "'Option A'" must of course be exchanged with the value you want to filter in your case. In this case it is a string, surrounded with single quotes. Use "#" for date values instead and nothing if it is a numeric value.

Cheers,

Christian

Author

Commented:
Hi,

Here is what i have, Can you see anything wrong with this,

        Me.Filter = "[Combo_Devstatus] = 'ProActive - A'"
        Me.FilterOn = True

Combo_Devstatus is a drop down selection box with about 5 options and i want to filter the records based on this,

Thanks


Author

Commented:
It is popping up a dialog box asking me what the value of Combo_Devstatus is !!

Hi,

yes, because you can't use names of controls inside a filter, you must use table field names instead.
Enter the name of the field where you expect the value "ProActive - A". And: The field must have exactly this contents "ProActive - A" and nothing more. If you want to filter a part of the field you must use "*" (or "%" in case of SQL Server) and "LIKE" instead of "=".

Cheers,

Christian
...oh, forgot to mention: If you want the filter value to be the value of the combobox it should be something like this:

Me.Filter = "[Your Table Field] = '" & Me.Combo_Devstatus & "'"

Author

Commented:
Ok, I am all confused now, I reckon i am missing out on something but i will explain what i am trying to do,

I have 5 options on my form. They are Proactive-A, Proactive-B, Proactive-C, Proactive-D & All

I then have a drop down list on my form that allows you to select
 Proactive-A, Proactive-B, Proactive-C & Proactive-D

I have about 300 records in the databse so far with more to add in the future,

I want people to be able to select Proactive-A and then all the records available to filter down to only people who have Proactive-A selected for there name,

I want to be able to select the different options or all and the records to filter down as i select them,

I have the case statements going fine, Its just the filter statement,

How do i do this filter statement????

Hope this makes sense but if not let me know
Hi,

now I'm confused...:-)
How do you want to filter? Using an option group as filter criterion or using a drop-down field? Both would not make sense.
If you have the option group then using
Me.Filter = "[Your Table Field] = 'ProActive - A'"
would be the right thing in the "After Update" event of the option group.
If you have the dropdown combobox field then using
Me.Filter = "[Your Table Field] = '" & Me.Combo_Devstatus & "'"
in the "After Update" event of the combobox would be the right choice.

And of course: You must change the name "Your Table Field" to the field name where the text "ProActive - A" is contained in your database table.

But if you have an option group to DISPLAY one of four values "ProActive - A" to "D" and the real value in the table field is a number and your combobox should set the filter, then the combobox should consist of two columns where the first is this number and the second the four texts for selection display (and an additional "All").

Simply remember: A filter must contain a table field name (no control name) and a value which should be filtered. That is what the contents of the filter string must be.

Cheers,

Christian

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial