Multiple Filters Continuous Form - Same as Excel

[Cat_129]

Hey,

I have done lots of googling but dont understand what its telling me

I have a continuous form, lots of records, working great, but we need to filter it within multiple columns (same as excel)

For Example filter by 'Open / Closed' to show all the open records, and then filter the first results by 'Owner' so I only see Bob's records.

Therefore it is filtered to Open Records in Bob's name. This example would be for two filters but I actually need 14 filters that all work together.

Hopefully I have explained this well enough. Thanks,

Cat

 

[arnelgp]

you need to add textboxes/comboboxes to your form.
then you filter your form based on the values on those controls.

 

[Cat_129]

you need to add textboxes/comboboxes to your form.
then you filter your form based on the values on those controls.

I have done this but each time the next filter is selected it resets the first filter. I dont know how to get it to keep the previous filter regardless of what order they are clicked. I want it to behave the exact same as Excel would behave with multiple filters

 

[CJ_London]

if you are talking about filtering a table or query with multiple filters (i.e. no form), use the advanced filter option on the ribbon.

 

[arnelgp]

maybe what you need is a Custom filter.

 

[Cat_129]

if you are talking about filtering a table or query with multiple filters (i.e. no form), use the advanced filter option on the ribbon.

This needs to be done with the form, the front end of the db only allows the users to access the forms that are relevant to them.

maybe what you need is a Custom filter.

This is fantastic but I need it to be like Excel where you can see the values that are available and select them from a combo box not type them.

Sorry to be so picky but its what the big boss wants:banghead:

 

[Gasman]

I think you will need a multiple selection listbox for each as Excel allows multiple values for a filter?

 

[arnelgp]

if you can share your db, i'll make all combos (above each column headings).

 

[CJ_London]

I need it to be like Excel where you can see the values that are available

Be aware that as with Excel, there is a limit to how many choices you have. And are users selecting more that one value from the list? If not then a combo with a rowsource of

SELECT DISTINCT fldName
FROM myTable
ORDER BY fldName

change fldName and myTable to suit

will give you your list for them to select from

 

[theDBguy]

Hi Cat. Pardon me for jumping in, but if this has to be like in Excel, would it be possible to convert your continuous form into a datasheet form then? Or, is that out of the question?

 

[Cat_129]

Sorry for the delay getting back to everyone, was working off site.

Unfortunately I can't share the database due to the nature of our company and it has confidential data in it, I would like to learn how to do the combo box's as filters because I am going to need to do this on multiple continuous forms as I continue with this monstrous task.

I dont think it can be a datasheet because it contains images that need to be viewed. Please correct me if I am wrong.

I have been tasked with getting all of our Excel forms / logs / information into one database but so that it behaves / looks / has the same controls as before. So although I can change things a little it needs to stay mostly the same.

 

[MajP]

I dont think it can be a datasheet because it contains images that need to be viewed. Please correct me if I am wrong.

Obviously the datasheet solution is the easiest and is a built in answer. I am building a demo to fake this for a continuous form, by building a class module. The user then only needs a few lines of code to make this work. If it is needed check back here in a few days.

 

[Pat Hartman]

Have you tried the Filter by Form or Advanced Filter options? Look in the Filter section of the ribbon under advanced.

 

[MajP]

Here is a pretty good fake. I did not add any functionality, only make it appear as if I do. This requires very little code.

 

[Gasman]

MajP
For the benefit of the O/P

I get an error in the ApplyFilter sub so commented it out

Error 2220 cannot open file 'Filter'

'  If InStr(Me.Filter, cmd.Tag) > 0 Then
'    cmd.Picture = "Filter"
'  Else
'    cmd.Picture = "Down"
'  End If

Said it could not find the file Picture

 

[Cat_129]

Here is a pretty good fake. I did not add any functionality, only make it appear as if I do. This requires very little code.

This is brilliant, exact ally what I needed. Thank you so much

 

[isladogs]

Also have a look at this example Student Target Grade Explorer

This allow you to filter using multiple combo boxes