Filter form by column

[smikkelsen]

I am trying to set up a filter on a form that works much like excel does. So, each column in my form would have a dropdown box that listed each unique value, and you could check the boxes for which ones you want, and uncheck boxes for records you don't want. Also, i would like this to be progressive, so it would use an and statement of sorts to continue filtering as you do the same to other columns. Basically exactly how excell does it when you turn on the filter sort.

Quick example:

I have 3 columns. first is COLOR second is SIZE and third is STYLE
if there are 5 unique colors listed in the color field, this filter would display those 5 colors and let me check or uncheck them. or have the blanks as well. Then I could go to size and check the smalls and XL's, then to style and you get the point.

I hope this is not an insanely hard thing to do. I would have thought Microsoft would have a built in thing to already to this, but I have not had any luck with macros.


Thanks in advance.

[maximus]

I assume you have a continous form. Chect the filter and sort options that Ms Access offers i think you will find the exact thing that you need without doing anything with macros

[smikkelsen]

Ok, a couple of problems with that. I have all of the menus, nav pane, etc. turned off for the end user so they dont screw stuff up. Its also locked as an accde. Even before I save as accde, the filter options aren't available on the form.

Second problem, even if all those options were available, some of my users are not bright enough to use access filters unless its a simple button, checkbox, combobox etc. That is why I am trying to design some filters that pertain to the actual form. Ones that will "speak their language"

I wish it were that simple, but I don't think that would work for those reasons.

Thanks for the suggestion.

[maximus]

To bypass the first problem I would suggest you to build your own customised menubar which will have the filter options. I do it all the time its great as its less time consuming . You are right users are never bright tell me in my 8 years of development I have seen all variety of the user breed. Throw in some more details I will design the filter for you.

[smikkelsen]

Man, I have to say, I have had nothing but good experiences with this forum. In a lot of other programming communities, people are arrogant, and treat you like crap if you don't understand everything they throw at you.

Its people like you that really make it enjoyable (and much easier) to learn. So thank you SO much.

I will just include a zip file with the form that i was going to start with, and the table with the fields required to test it. Sorry, you will probably see a bunch of errors because all the macros and functions etc are not in there. i tried to include everything needed to view and work with the form.

The buttons on the top of the colums are what i want to get rid of because they are horrible. and there is only one way to sort using those.

Eventually I would like to create a sort filter for every column. if you are able to maybe do an example for one or two, that would be amazing!

I guess i should explain the fields better also. The last fields in the PR_Main form are all date fields (except the very last). On these i need to sort by 7 different things:
1. no contact = assigned field is not null, but consult field is null
2. in build = consult is not null, but preview is null
3. preview = preview field is not null but revisions is null
4 revisions = revisoins field is not null but complete is
5 complete = complete is not null but rating is null
6 on hold = on hold is not null
7 surveyed = rating is not null

So, i was trying to figure out a way to have those 7 options in a dropdown box, and once selected, it would filter based on those criteria.
but to make it more complicated, the other fields would have dropdown boxes that listed the available values just like excel does. for example, the ratings field would have a dropdown that I could check 1,2,3,4,5,6,Auto or all, or blanks.

Then the last thing (which i will probably just worry about later) is to just have an ascending/descending button above each date column.

Then it would continue adding to the filter until i clicked the "show all records" button which would just remove the filters.

Now this is way over my head as far as where to even begin. I hope I make sense though.

My assumption is that this is a HUGE job. So any sort of small example you can do for me, i would be very greatful for.

[smikkelsen]

Also, I like the idea you suggested about custom menus. Do you have a good link on where I could maybe start reading up on that? Microsoft always seems a little vague on some of their tutorials.

Thanks again!

[maximus]

here are two links that you can start with:
http://www.msaccesstips.com/2007/07/...and-tool-bars/
http://www.learnaccessnow.com/chap19b.html

can you attach the sample file converting it to Ms access 2000 coz I am in the office and we use Opensource and Not MS Office. My Lap as Ms Office 2000.

[smikkelsen]

Thanks for the links, i will check those out.

Thanks again for your time, I really appreciate it.

[smikkelsen]

Hi Maximus. I just wanted to check up and see if you had any luck with a filter or not.

Thanks again!

[maximus]

I am sorry it went completely out of my mind will certainly give you the sol today.....

[smikkelsen]

Not a problem at all, just following up.

Thanks again for looking at it for me, you are a great help!