Filter Lost After Switching Views

Question

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?

Answer 1

Hi Traci,

I believe I have seen a discussion about the same issue before, and the suggested solution was to add a request at Access User Voice. As for a workaround, it might involve some VBA and using global variables to store the current filter.

Just a thought...

Answer 2

I never allow end users to switch between views - in fact, I never allow them to use datasheet view.

Thus the problem that you describe doesn't arise...

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 3

DB Guy ... Yes, I posted this on UA about a month ago. You also replied and suggested split forms.  That does work, but changes the layout dramatically.  Thought I would try posting this other places in search for a better solution.

Answer 4

DB Guy ... Yes, I posted this on UA about a month ago. You also replied and suggested split forms.  That does work, but changes the layout dramatically.  Thought I would try posting this other places in search for a better solution.

Ah, I knew it sounded familiar. Hope you find a better answer here. Good luck!

Answer 5

"When I do step 5 ... the filter I originally applied on Column 1 is lost!!"

You need to include the original filter criteria in addition to the new criteria when you apply the second one. The form will automatically remove filtering when you switch views. But I agree with Hans. Don't allow users to switch views on the fly. Just use Form View and forget about the problems switching views can cause.

Answer 6

Hi TraciMarie,

your steps:

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!!

I try to follow the steps and try to reproduce the issue.

you had mentioned that,"When I do step 5 ... the filter I originally applied on Column 1 is lost!!"

above you can see that in column 1 filter is still there after filter adding to the second column.

both column contains the filters.

did I missed any step? if yes, then let me know about that.

I made this test on MS Access 2016 version, let us know which version you are using.

also try to test the way I did above and check what output you get on your side and try to post it here.

so that we can see the difference between them.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

Lawrence,
"You need to include the original filter criteria in addition to the new criteria when you apply the second one."

Is there a way to do this?

This form is used in a group meeting where viewed on a projector screen.  My users need to have it where they look at the records in datasheet view, then they filter on which records they want to discuss.  They switch to form view to look at the records full screen, with subforms.  Data gets edited there.  Then they go back to datasheet view and filter further to discuss other records.  That is when the problem occurs.  I have thought about just having the datasheet form open a separate form if they double-click on the main field and it would only show that record.  The problem is, once in form view, the users want to scroll through all the records in that filter.  That's why I allow both views and they can go back and forth.

I am currently working on doing two separate forms and having them double-click a field to go back and forth.  I found a way to pass the filter from datasheet form to form view form.  But occasionally I'll get "Not a valid bookmark" error when I try to switch to the form view form.  I can't figure out what's causing it.  It's only when I do certain filters.

Answer 8

Deepak,

Thanks for the demo.  I am using Access 2010 and mine definitely does not act that way.  I wish it did.  Looks like your steps are correct, although it looks like you are running the filter on a subdatasheet?  I am just running it on the main record.

As I told Lawrence above, the workaround I am trying to do now is:  two separate forms and having them double-click a field to go back and forth.  I found a way to pass the filter from datasheet form to form view form.  But occasionally I'll get "Not a valid bookmark" error when I try to switch to the form view form.  I can't figure out what's causing it.  It's only when I do certain filters.

Answer 9

Hi Traci,

Pardon me, but I just had a thought. Earlier you said using a Split Form does not destroy the filter, but it won't work for your current layout. From what you just described, I am wondering why it can't. If you use a Split Form, rather than switching between Form View and Datasheet, you can simply "minimize" the datasheet part to display the whole form. Then, to switch back, you "maximize" the datasheet portion to hide the form section. By "minimize" and "maximize," I was referring to just moving the Splitter Bar down or up.

Like I said, it's just a thought...

Cheers!

Answer 10

Hmmm ... Now I see what you are saying.  I just played with the test split form I have and I see that I can move the bar up and down.  Let me think about that.  Is there a way to smoothly 'maximize' the datasheet portion or the form portion via VBA code?  Just thinking it would be smoother / more user-friendly if they could use a command button or double-click event to do this.

Answer 11

Never Mind ... I think I figured out a solution.  If I leave the split form with the bar all the way up (basically showing the form view and hiding the datasheet view completely), I can go back and forth between Datasheet View and Form View (really split view) and the filter seems to stay in tact.  I'll do some more testing, but I think that might do the trick.  I didn't realize you could completely hide one view or the other.  Thanks!

Answer 12

Hi Traci,

I think you can play with the following properties to see what works best for you.

SplitFormOrientation

SplitFormSize

SplitFormSplitterBar

Good luck!

Answer 13

Thanks.  Those properties help!  I am able to change the form size with SplitFormSize using VBA, basically showing mostly datasheet or mostly form view, as if I was moving the splitter bar up and down.

However, there's a glitch I can't seem to figure out (as always).  When I move to a different record in the datasheet section, if the form view height is very small (because I'm trying to hide it), to the point where the ID is hidden ... when I resize the form section to I can see the record there, it is NOT the same record I selected in the datasheet section.  The form section shows the record I was previously on.  Only when the ID field is showing will it work properly.  Ugh!  I have tried this a bunch of times - even tried Refresh and Recalc and doesn't seem to work.  I was thinking of putting a tiny ID field at the very top of the form section so it is 'visible' to Access.  I'll try that, but not sure if it will work.  And I'm not even sure it's an issue with the ID showing or just the fact that the record isn't showing.

Any other ideas to get the two sections to be in sync, even if one section is somewhat hidden?

Answer 14

Hi,

Just as a troubleshooting step, add a MsgBox to display the ID value after you move the Splitter Bar in code. We're just trying to make sure what you're looking at is the same as what Access "sees."

Answer 15

Hi Traci,

You could try the following. At the end of your code to display the whole form (minimize the datasheet), set the focus to a control on the form. For instance:

Me.SplitFormSize = SomeValue

Me.ControlName.SetFocus

Hope it helps...

Answer 16

Thanks for the suggestions.  Setting focus on a control that is only on the form and not on the datasheet does not help.

I did put a message box at the end of the code just before it the form resizes ... both when going from form to datasheet and the other way.  The ID is correct (it's the one I double-click on to get to the other 'view'), but that record is not the one showing in the form section.  A couple interesting things I noticed:

1)  When I move from the form section to datasheet section (double-click to resize the form and see the other section) ... the ID is always correct ... the focus moves to the correct record in the datasheet section.  But when I go back the other direction, it is always wrong (which leads me to #2).

2)  When moving from datasheet to form, let's just say I double-click ID #1234 and let's say #1234 is record number 5, as displayed in the navigation bar on the bottom left.  It still shows that it's record number 5 on the bottom left, even though the record displayed on the form is the previous record I was on (when I was on the form view last time).  When I use the navigation buttons at the bottom to move to the next record (record #6), then it corrects itself and really does show the correct ID and record for record #6.
Furthermore ... When I'm on record #5 and it's displaying the wrong record, if I go to the navigation bar and type in "5" (to move to record #5), then it shows the correct record ... the one it should have displayed in the first place.

I have tried Repaint, Recalc, etc.  Those don't work.  Any ideas?  Thinking it's a recordset or bookmark issue?

Answer 17

Hi Traci,

When I was trying it out, I didn't know how you were resizing the datasheet, so I just added two comboboxes on the header of the form: one to minimize and another to maximize. Could you do me a favor and try using buttons to see if the problem is the same? Thanks.

Answer 18

Hi Traci,

For me to duplicate your setup, could you please tell me where you put the code to minimize the datasheet and where you put the code to maximize it? Thanks.

I imagine using the double-click for the ID to minimize the datasheet, but what do I use to maximize it (since the datasheet is supposedly out of the way now)?

Answer 19

I have two procedures that run ...
1)  I double-click the WorkRequestID field while on the datasheet section to show the form (make split form size large).
2)  I double-click the Title field while on the form section to show the datasheet (make split form size small).

I don't know if this is what I'll end up with, but it's a way to go back and forth for now:

Private Sub WorkRequestID_DblClick(Cancel As Integer)
    'Go to form section (move bar up)
    Me.SplitFormSize = 7452
End Sub

Private Sub Title_DblClick(Cancel As Integer)
    'Go to datasheet section (move bar down)
    Me.SplitFormSize = 144
End Sub

Like I said above, it if it's too small, it doesn't show the correct record.  I am going to try putting that ID at the very top tomorrow, and see what it does.  But it does seem that if the form section is too small, it does the behavior I described above.  If I bring the record partially into view, it's fine.  But it defeats the purpose and makes it look messy if part of the other section is showing at the top or bottom.

Answer 20

Hi Traci,

Thanks for the additional information. In your code to display the form, try adding the following code at the end:

Screen.ActiveForm.Controls("WorkRequestID").SetFocus

Hope it helps...

Answer 21

Hi TraciMarie,

you had mentioned that,"Never Mind ... I think I figured out a solution."

is your issue is solved now?

I find that you had continue the discussion after that post.

let us know about the status of your current issue.

so that we can try to look in to that and try to reproduce the issue on our side and try to provide you further suggestion.

you can also try to post your sample database. so that we can get exact idea.

if your issue is solve then let us know about that and try to mark the answer.

Regards

deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 22

DB Guy ... Thank you so much!!  It worked!! :) :)  I had tried setting focus before, but I was using:

Me.WorkRequestID.SetFocus

I guess there is a big difference between that and:

Screen.ActiveForm.Controls("WorkRequestID").SetFocus

??

I will remember that in the future.  The users will love this.  Thanks again!

The code I ended up using (so others can see):

Private Sub WorkRequestID_DblClick(Cancel As Integer)
    If Me.SplitFormSize < 7452 Then
        'Go to form section (move bar up)
        Me.SplitFormSize = 7452
        Screen.ActiveForm.Controls("WorkRequestID").SetFocus
    Else
        'Go to datasheet section (move bar down)
        Me.SplitFormSize = 144
    End If
End Sub

This way the users clicks on the WorkRequestID in both cases.  It moves the bar depending on what the current SplitFormSize is.

Answer 23

Deepak ... That last post from DB Guy was the missing piece.  I marked the posts from him that were part of the answer.  You can close this now and mark as solved.  Or ... do I need to close it?  If so, how do I do it?

Answer 24

Hi Traci,

Glad to hear you got it to work. I also posted a demo for you at UtterAccess. Good luck with your project.

Answer 25

Got it.  Thanks!

Answer 26

Hi Traci,

You're welcome. Good luck with your project.