Filter Lost After Switching Views (1 Viewer)

traci · Jun 27, 2017

Out of all the bizarre bugs and behaviors I have experienced with Access over 20 years, this one has got to be one of the strangest. One of my users helped me discover this. This happens on ALL forms, no matter what. If anyone has seen this behavior and knows what to do to fix it, please let me know:

1) I have a very simple form based on a very simple table (no code, nothing special).
2) I open the form in datasheet view and apply a filter to Column 1 using the filter menu (checkbox options) or the 'Selection' option.
3) I switch to Form View.
4) I switch back to Datasheet View. The filter I applied is still set and applied.
5) I apply a filter to Column 2 using the filter menu, expecting the Column 1 filter to stay set, as I am just adding more filter criteria. But this is not what happens!!

When I do step 5 ... the filter I originally applied on Column 1 is lost!! I can tell by the records displayed that the filter in that column was removed. The filter symbol for that column also disappears to confirm there is no filter applied there anymore. I expected that original filter to remain in place and as I add filters to other columns ... just like it would if I had remained in datasheet view the whole time! But it only applies the filter I set in Column 2!

If you've never seen this behavior, try the steps above and see for yourself! BTW, this also happens in the reverse (i.e. first applying a filter in form view and then switching to datasheet view and back).

Thoughts? Solutions? Workarounds?

isladogs · Jun 27, 2017

Keep column 1 selected whilst you add the filter to column 2
In other words, have both columns selected
Both filters are then applied.

You can extend this to 3 or more columns in the same way

Of course you can also avoid the issue by only allowing one view e.g. datasheet

traci · Jun 27, 2017

Thanks for the suggestion, but that doesn't work. Once both columns are selected, as soon as I click the small filter menu on the column header of Column 2, it unselects Column 1 automatically. And the large "Filter" button on the Ribbon is disabled if more than one column is selected.

isladogs · Jun 27, 2017

I had thought what you wrote would be wrong but found what you said to be true.
However my workaround definitely did the job for me in Access 2010.
I did find it slightly tricky to keep both selected but with a little practice it worked ok.

Try right clicking on a record in the 2nd column rather than the header. It might help.

Nevertheless I still recommend you prevent users changing from one form view to another

traci · Jun 27, 2017

I tried what you suggested earlier and the filter options do not appear in the shortcut menu if more than one column is selected.

Seems the advice I have gotten from you and others is to not allow users to switch views. I thought of possibly having two different forms based on the same query ... one would be the form version and one the datasheet version. Users could 'switch views' that way, so to speak. But I wanted to see what others said before throwing that idea out there.

I am testing this idea right now and I think I have it working where I pass the filter criteria from the datasheet to the form and then setting the Filter On = True. And then since I'm not truly switching views, the filter stays in tact. It's not the best, but it is a workaround.

Thanks for your input.

isladogs · Jun 27, 2017

Tried to do a screenshot to show you but its tricky doing that without losing the context menu.

one would be the form version and one the datasheet version. Users could 'switch views' that way, so to speak.

Use a split form or if you don't like that instead use an emulated split form
See Emulating the split form. Its in sample databases area

traci · Jun 27, 2017

Split Form was suggested to me on another site where I posted this problem. It works, but layout is not workable for what I am doing. Thanks for emulated split form idea, but think I am going to try the two-forms idea.

Gasman · Jun 27, 2017

I have just created a form based on a table.
I filter on a date, get 4 records. Filter on another column and get just the one.

My 2007 behaves as the Excel filter does.?

I don't have too much data to be able to filter on yet another column, but I have telephone records in work that should allow a good spread of filters.

I use the headers and then select the criteria in the same you would do in excel.
If I wanted anything adventurous I would use the Advanced filter query option.

The reason I did this? was because I was pretty sure I had been doing this all the time, but with my memory could also be mistaken.

What I have discovered howeverm is that Select All on the second filter column *sometimes* removes the filter on the first column as well.

traci said:
Out of all the bizarre bugs and behaviors I have experienced with Access over 20 years, this one has got to be one of the strangest. One of my users helped me discover this. This happens on ALL forms, no matter what. If anyone has seen this behavior and knows what to do to fix it, please let me know:

1) I have a very simple form based on a very simple table (no code, nothing special).
2) I open the form in datasheet view and apply a filter to Column 1 using the filter menu (checkbox options) or the 'Selection' option.
3) I switch to Form View.
4) I switch back to Datasheet View. The filter I applied is still set and applied.
5) I apply a filter to Column 2 using the filter menu, expecting the Column 1 filter to stay set, as I am just adding more filter criteria. But this is not what happens!!

When I do step 5 ... the filter I originally applied on Column 1 is lost!! I can tell by the records displayed that the filter in that column was removed. The filter symbol for that column also disappears to confirm there is no filter applied there anymore. I expected that original filter to remain in place and as I add filters to other columns ... just like it would if I had remained in datasheet view the whole time! But it only applies the filter I set in Column 2!

If you've never seen this behavior, try the steps above and see for yourself! BTW, this also happens in the reverse (i.e. first applying a filter in form view and then switching to datasheet view and back).

Thoughts? Solutions? Workarounds?

isladogs · Jun 27, 2017

Split Form was suggested to me on another site where I posted this problem.

As I'm sure you know it is against all forum guidelines to cross post unless you inform forum users on each forum that you are doing so.

The reason is to prevent forum users wasting time repeating points already made. How was I to know that it had been suggested before?

When I saw your post I thought it looked familiar from elsewhere.
However, after a quick search, couldn't find it so I assumed I was wrong.
Otherwise I would have flagged it immediately

Good luck but in future follow the forum guidelines

Gasman · Jun 28, 2017

Tried the filters in work on data that would allow me more than two filters at a time.
I was able to set 3 filters, chop and change the filters and not lose any pre existing filters.

The split form does seem very suitable for this if the number of controls are small, otherwise that simulated split form with a tailored subform would be brilliant.

traci · Jun 28, 2017

Ridders ... Sorry, I didn't know. I will let people know next time. I posted it over a month ago on Utter Access. Besides split form, no other suggestions or solutions were provided to solve the problem. So I decided to post elsewhere to try to find answers.

Filter Lost After Switching Views (1 Viewer)

traci

Registered User.

isladogs

MVP / VIP

traci

Registered User.

isladogs

MVP / VIP

traci

Registered User.

isladogs

MVP / VIP

traci

Registered User.

Gasman

Enthusiastic Amateur

Attachments

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

traci

Registered User.

Similar threads

Users who are viewing this thread