Strange behaviour - filters are not remembered between loads

[Notiophilus]

I have a search form which suddenly stopped remembering filters between loads. I managed to accidentally replicate the behaviour on a second database where I knew the filters worked - in this case the code was

Private Sub Form_Unload(Cancel As Integer)
      Me.Filter = "(False)"
      Me.FilterOn = True
End Sub

Turns out that simply commenting out then uncommenting this section (while the form was open) was enough to break it. Filtering still works, but the form's filter property is forgotten when it unloads, and the only way to change it permanently is in Design View.

So I made a very simple test db with a filtered form, started faffing around with the code while it was open, and lo and behold the filters have stopped working. But I've compared the functional and non-functional versions and I can't for the life of me see what's been changed. I suspect I've run into some kind of bug (not that I should be fiddling with code while in form view anyway) but what can I do to revert it?

It turns out that I can force the filter to be saved by hitting Ctrl+S before I exit the form. Still no clue as to why v1 mysteriously works and v2 mysteriously doesn't. In the meantime, can I replicate Ctrl+S in VBA? I tried DoCmd.Save acForm "FormName", but no luck there.

 

[MarkK]

It's not exactly clear to me what you are looking for. There is a question here, "what can I do to revert it?" but, revert to what?

You can save on close if you use DoCmd.Close(). Check out the third parameter, so you could use code like . . .

private sub cmdClose_Click()
   DoCmd.Close acForm, Me.Name, acSaveYes
end sub

. . . which I expect would save your filter too.

 

[gakiss2]

It's not exactly clear to me what you are looking for. There is a question here, "what can I do to revert it?" but, revert to what?

You can save on close if you use DoCmd.Close(). Check out the third parameter, so you could use code like . . .

private sub cmdClose_Click()
   DoCmd.Close acForm, Me.Name, acSaveYes
end sub

. . . which I expect would save your filter too.

I can say, if it is the same thing I am experiencing, using DoCmd.close… acSaveYes does NOT work as I tried it. Somehow saving the form is not the answer. In my thread I mention that if I click the Save Icon (which looks like it might be the same as Ctl-S noted above) then the filter settings will be successfully saved and then applied next time the form is open.

 

[gakiss2]

I have a search form which suddenly stopped remembering filters between loads. I managed to accidentally replicate the behaviour on a second database where I knew the filters worked - in this case the code was

Private Sub Form_Unload(Cancel As Integer)
      Me.Filter = "(False)"
      Me.FilterOn = True
End Sub

Turns out that simply commenting out then uncommenting this section (while the form was open) was enough to break it. Filtering still works, but the form's filter property is forgotten when it unloads, and the only way to change it permanently is in Design View.

So I made a very simple test db with a filtered form, started faffing around with the code while it was open, and lo and behold the filters have stopped working. But I've compared the functional and non-functional versions and I can't for the life of me see what's been changed. I suspect I've run into some kind of bug (not that I should be fiddling with code while in form view anyway) but what can I do to revert it?

It turns out that I can force the filter to be saved by hitting Ctrl+S before I exit the form. Still no clue as to why v1 mysteriously works and v2 mysteriously doesn't. In the meantime, can I replicate Ctrl+S in VBA? I tried DoCmd.Save acForm "FormName", but no luck there.

sounds like you are getting exactly what I did. Sorry no great solutions to offer. I was having luck clicking the Save Icon which is probably exactly the same as Ctrl+S you are doing. But, of course, that is not a serious solution to the problem. I had gotten a suggestion to just have the form go invisible instead of closing it. For now that is my path but it would be satisfying to get to the bottom of why saving the form by vba doesn't work or at least some vba code which could do it.

 

[gakiss2]

I have a search form which suddenly stopped remembering filters between loads. I managed to accidentally replicate the behaviour on a second database where I knew the filters worked - in this case the code was

Private Sub Form_Unload(Cancel As Integer)
      Me.Filter = "(False)"
      Me.FilterOn = True
End Sub

Turns out that simply commenting out then uncommenting this section (while the form was open) was enough to break it. Filtering still works, but the form's filter property is forgotten when it unloads, and the only way to change it permanently is in Design View.

So I made a very simple test db with a filtered form, started faffing around with the code while it was open, and lo and behold the filters have stopped working. But I've compared the functional and non-functional versions and I can't for the life of me see what's been changed. I suspect I've run into some kind of bug (not that I should be fiddling with code while in form view anyway) but what can I do to revert it?

It turns out that I can force the filter to be saved by hitting Ctrl+S before I exit the form. Still no clue as to why v1 mysteriously works and v2 mysteriously doesn't. In the meantime, can I replicate Ctrl+S in VBA? I tried DoCmd.Save acForm "FormName", but no luck there.

found the info on replicating keys

https://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/sendkeys-statement

 

[gakiss2]

found the info on replicating keys

https://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/sendkeys-statement

SendKeys "^S" didn't work

 

[Deleted member 147267]

Don't know whether or not you noticed, but you've resurrected a 5 year old thread so you probably won't get replies from the original posters.
FWIW, acSaveYes is only for saving design changes. Such code will not save form or report properties changed in normal form view. If you're saying you can save an applied filter with the ribbon save button while in form view, that's interesting, but I'm pretty sure code will not do so.

 

[gakiss2]

Don't know whether or not you noticed, but you've resurrected a 5 year old thread so you probably won't get replies from the original posters.
FWIW, acSaveYes is only for saving design changes. Such code will not save form or report properties changed in normal form view. If you're saying you can save an applied filter with the ribbon save button while in form view, that's interesting, but I'm pretty sure code will not do so.

No, I hadn't noticed it was old. Ooops!. but thanks for the response. It took a lot of 'trouble shooting' but yes, I have validated after several attempts that clicking the save icon works to have the filter settings 'take'. I tried SendKeys "^S" but that did NOT work. I have had some success with the idea to just turn off visibility of the form rather than close it so I will go with that but I am still curious how to make Filter on load work as advertised.

 

[Deleted member 147267]

All depends on what exactly you are trying to do but if in form OnLoad, something like

Me.Filter = "someField = "dog" (or "someField = 2, etc.)
Me.FilterOn = True

Can also be done by the code in the calling form. Again, all depends...

 

[gakiss2]

All depends on what exactly you are trying to do but if in form OnLoad, something like

Me.Filter = "someField = "dog" (or "someField = 2, etc.)
Me.FilterOn = True

Can also be done by the code in the calling form. Again, all depends...

Yes, I know how to apply a filter that I create. I really wanted to be able to bring back the filter that was in place. I have a popup form that the user can push buttons to filter out big groups of data. For example the users create the records they are looking at so one button is for them to filter for the user that created it and they often are interested in ones they created themselves. I haven't done it but I suspect I could figure out how to to pull that created filter back up if the form is reopened. The issue is that they also might then use the drop down filters to select say, a part number. I would then have to find a way to add that selection to the created filter. You can see that this could get complicated. that is why I wanted to use Filter On Load because access records the last filter you used on the form. At least that is what it is supposed to do. At this point this is just a crusade for logic and justice and truth in advertising because now I am just turning the visible feature off and leaving the form open. the filter criteria is right there when I make it visible again.

 

[Gasman]

From https://support.office.com/en-gb/ar...database-2a493ded-e544-4144-9103-b9b1d1865147
Save a filter
It might be useful to save a filter if you will be using it again. The filter settings that are in effect at the time when you close a table, query, form, or report are automatically saved along with the object, and they are available for reapplying. However, by default, the filter settings are not automatically applied for you when you next open the object.

To ensure that the current filters are automatically applied when you next open a table, query, form, or report, from the object's property sheet, set the object's FilterOnLoad property to Yes. The FilterOnLoad property is applied the next time you open the object. Whenever you change this setting, you must close the object and reopen it in order to apply the new setting.

Note: The FilterOnLoad property setting is only applied when the object loads. Setting this property for an object in Design view and then switching to another view will not cause the setting to be applied. You must close and reopen the object for changes to the FilterOnLoad property setting to take effect..

I could not get it to work either.?

We have had another user trying to use a remplate file for an email as per MS docs, that does not appear to work as described either?

I could only get the filter to work, by pressing toggle filter twice?
The filter button at the bottom of the form shows Filtered on Load, but I am on a record not in the filter range. I toggle filter to get Unfiltered, Toggle again and then I get the filtered records.

 

[bastanu]

Add a local table (I use usysFormFilter as its name as it becomes a hidden system table) and use it to store the form's filter/sort order/ recordid on unload:

Private Sub Form_Unload(Cancel As Integer)
'lets save the filter, sort and record id
Dim sFilter As String
Dim sOrderBy As String
Dim lRecordID As Long

lRecordID = Me.ID
sFilter = Me.Filter
sOrderBy = Me.OrderBy

CurrentDb.Execute "UPDATE usysFormFilters SET [LatestFilter]='" & sFilter & "',[LatestSort]='" & sOrderBy & "',[LatestRecordID]=" & _
lRecordID & ",[FilterOn] = " & Me.FilterOn & ",[OrderByOn]=" & Me.OrderByOn & ";", dbFailOnError


End Sub

You can add a FormName in the table if you need to do this for multiple forms.
Then in the Load Event simply retrieve the values from the table using dlookups. This way each user will get their own values as you describe.

Private Sub Form_Load()

DoCmd.Maximize
'load last form settings
Dim sFilter As String
Dim sOrderBy As String
Dim lRecordID As Long
Dim boFilterOn As Boolean, boOrderByOn As Boolean
Dim vID As Variant


sFilter = Nz(DLookup("[LatestFilter]", "[usysFormFilters]"), "")
sOrderBy = Nz(DLookup("[LatestSort]", "[usysFormFilters]"), "")
lRecordID = Nz(DLookup("[LatestRecordID]", "[usysFormFilters]"), 1)
boFilterOn = DLookup("[FilterOn]", "[usysFormFilters]")
boOrderByOn = DLookup("[OrderByOn]", "[usysFormFilters]")

    Me.FilterOn = boFilterOn
    Me.Filter = sFilter
    Me.OrderByOn = boOrderByOn
    Me.OrderBy = sOrderBy

'go to the last record
Me.ID.SetFocus
DoCmd.FindRecord lRecordID


End Sub

Cheers,
Clad

 

[Gasman]

Hmm, just had a simple form based on a query/table and the FilterOnLoad works as described.? Really confused now.

 

[bastanu]

@ Gasman, my main requirement was to navigate back to the last viewed record so I added the filter and sorting on top of that.
Cheers,