Filter on load not working correctly

[gakiss2]

I want to utilize the Filter On Load. I have a datasheet view form that a user will use to look up a record then click to launch a form that shows detail for that specific record. There is a field in the datasheet form that had a click event which opens the single record form. When the user is finished working with that form he may want to go back to the datasheet form to find another item to work on. Since that user may have used some filtering techniques to get to the subset of records he is interested in then I want that datasheet form to have the same filters in place that were in place the last time he looked at it. I set the property of the datasheet form for Filter on Load to Yes.

I actually have a floating window form with some buttons to help filter the form but the user also has access to the native access filter functions as well.

I click the field to go to the record and it goes. When I go back to the datasheet form, the filter settings are NOT preserved.
At first I thought it was working some times but not other times. But after some trouble shooting I did discover that if I set the filter settings then click the Save Icon, the one right above the 'File' menu, then the filter settings WILL BE preserved. I tried to add code to save the form but that doesn't do it. there is something that happens when the big Save icon is clicked that I haven't been able to recreate with code. I also tried me.requery. I could use some help on how to fix this little glitch.

 

[The_Doc_Man]

First, here is an article on the subject:

Apply a filter to view select records in an Access database - Microsoft Support

Learn ways to filter data in Access to limit the records that are displayed in a query, form, or report.

support.office.com

That reference is actually near the bottom of a longer article that says that by default, filters go away (self-erase) when the object is closed. So it IS necessary to take affirmative steps to preserve the filter. The question is whether the datasheet in question remains open and a second question is whether the code that tries to save the filter is run at the right time. From what I read in that article, you have to save the filter before the datasheet closes. So... when you have the click event that launches something else, does that allow the datasheet to close or does it just stay open in the background?

 

[Pat Hartman]

When formA opens formB, set the visible property of formA to false to hid it. Pass the name of formA in the OpenArgs (not necessary if formB is only ever opened by formA). Use Me.Name as the argument. Then when formB is closed, it can open the form in the openArgs and since the form is already open, it will just become visible and will be filtered as it was left.

 

[gakiss2]

First, here is an article on the subject:

Apply a filter to view select records in an Access database - Microsoft Support

Learn ways to filter data in Access to limit the records that are displayed in a query, form, or report.

support.office.com

That reference is actually near the bottom of a longer article that says that by default, filters go away (self-erase) when the object is closed. So it IS necessary to take affirmative steps to preserve the filter. The question is whether the datasheet in question remains open and a second question is whether the code that tries to save the filter is run at the right time. From what I read in that article, you have to save the filter before the datasheet closes. So... when you have the click event that launches something else, does that allow the datasheet to close or does it just stay open in the background?

Thank you for your input. I had actually already read that one trying to figure this out. That last section where it talks about Filter on Load is what I am trying to do. But I am finding it doesn't really work that way in real life, or I am not understanding. I WANT it to just apply the last filter that was used but when I close the form and later go back to it, the filters are not in place anymore. After some trouble shooting I found that it only saves the filter settings if you (user) click the save icon. And its not that that is such an inconvenience, some users just can't learn simple things like that which are not automated for them. My frustration is that if I can do it with one mouse click, why can't I do it with vba code?? I may try making the form invisible instead, I hadn't thought of that. There is no real reason it HAS to close. I just like to take everything out of view of the user which they don't absolutely need. In fact I think there is a way to get away from the tabbed object view and present the user with only the one form (and my popup) at a time.

 

[gakiss2]

When formA opens formB, set the visible property of formA to false to hid it. Pass the name of formA in the OpenArgs (not necessary if formB is only ever opened by formA). Use Me.Name as the argument. Then when formB is closed, it can open the form in the openArgs and since the form is already open, it will just become visible and will be filtered as it was left.

Part of me wants to solve the original problem out of spite but I have to admit that just turning the form off visibly does achieve what I need to do as the only reason I was closing it was to keep blinders on the user. My controls pass the user form A to B and back again so instead of closing and opening I just have to turn visible off and on. I'll give it a try, it should work.

 

[The_Doc_Man]

Pat's approach of making the original datasheet invisible is probably going to be the easiest approach. She is noted for good solutions like that.

 

[arnelgp]

another approach would be to save the "Filter" to a table (tblFilters)

tblFilters(table)
FormName (string)
Filter (string)

on Unload event of the Form, save the Filter and the formname to this table.
use DCount("1', "tblFilters", "FormName ='" & Me.Name & "'").
if dcount() returns 0, Add the formname and the filter.
otherwise, Update the record with the filter.

on Load event, use dlookup for the Filter on same form
if dlookup returns, other than Null then
set the filter of the form to the returned value.

dim sFilter as variant
sFilter = DLookup("Filter", "tblFilters", "FormName = '" & Me.Name & "'")
If Not IsNull(sFilter) Then
Me.Filter = sFilter
Me.FilterOn = True
End If

 

[Gasman]

I have noticed when going in to Form Design that there can be a filter left over from running a form.?
The Filter On Load does not appear to work for me either, even though the filter button at the bottom of the form indicates Filtered.

However https://docs.microsoft.com/en-us/office/vba/api/access.form.filter(property) states

To apply a filter automatically when a form is opened, specify in the OnOpen event property setting of the form either a macro that uses the ApplyFilter action or an event procedure that uses the ApplyFilter method of the DoCmd object.

That does appear to work?, so I am also puzzled by the Filter On Load mechanism?

 

[gakiss2]

another approach would be to save the "Filter" to a table (tblFilters)

tblFilters(table)
FormName (string)
Filter (string)

on Unload event of the Form, save the Filter and the formname to this table.
use DCount("1', "tblFilters", "FormName ='" & Me.Name & "'").
if dcount() returns 0, Add the formname and the filter.
otherwise, Update the record with the filter.

on Load event, use dlookup for the Filter on same form
if dlookup returns, other than Null then
set the filter of the form to the returned value.

dim sFilter as variant
sFilter = DLookup("Filter", "tblFilters", "FormName = '" & Me.Name & "'")
If Not IsNull(sFilter) Then
Me.Filter = sFilter
Me.FilterOn = True
End If

Thank You for your comments. I had been hoping to take advantage of the Filter On Load so that I could avoid the extra code to do this. If I end up going this way, your code will be very helpful. I was wondering how I could save the filter and the idea of a table is helpful. One other question though: I have some functionality now to make the filter by use of a form with buttons the user can slice and dice with. The user could, after using those buttons, proceed to use the native filter functions on the form. By this I mean the arrow you click then you can filter further by checking / unchecking the boxes as well as some other filter functions. For this to work fully, I would need to capture those selections in the filter that I save in the table. Any chance there is a function to capture that?

 

[gakiss2]

I have noticed when going in to Form Design that there can be a filter left over from running a form.?
The Filter On Load does not appear to work for me either, even though the filter button at the bottom of the form indicates Filtered.

However https://docs.microsoft.com/en-us/office/vba/api/access.form.filter(property) states

To apply a filter automatically when a form is opened, specify in the OnOpen event property setting of the form either a macro that uses the ApplyFilter action or an event procedure that uses the ApplyFilter method of the DoCmd object.

That does appear to work?, so I am also puzzled by the Filter On Load mechanism?

I think the 'filter left over' is whatever filters were on the form when the Save Icon got clicked. That was my experience. If I set the filters the way I liked them then Clicked the Save Icon and THEN closed the form, I could later reopen that same form and the filters I had set up were in place. I then put that form into Design mode and looked at properties. The filter that is in place on the form is in the spot for the filter property. Try that and let me know if you have the same outcome.

I have a vague memory that Access vba has some sort of 'function' (property, method, or whatever) where you can replicate user keystrokes. I don't recall that syntax, I don't know what 'code' you would use to replicate clicking the save icon and I have no idea if this would work. Although I am very curious on the resolution of this, I feel the method of leaving the form open but making it invisible seems the quickest road to success.

 

[gakiss2]

I think the 'filter left over' is whatever filters were on the form when the Save Icon got clicked. That was my experience. If I set the filters the way I liked them then Clicked the Save Icon and THEN closed the form, I could later reopen that same form and the filters I had set up were in place. I then put that form into Design mode and looked at properties. The filter that is in place on the form is in the spot for the filter property. Try that and let me know if you have the same outcome.

I have a vague memory that Access vba has some sort of 'function' (property, method, or whatever) where you can replicate user keystrokes. I don't recall that syntax, I don't know what 'code' you would use to replicate clicking the save icon and I have no idea if this would work. Although I am very curious on the resolution of this, I feel the method of leaving the form open but making it invisible seems the quickest road to success.

I tried
SendKeys "^S" But it didn't work

 

[bastanu]

So what part of my solution posted in your previous post didn't met you expectation:

Strange behaviour - filters are not remembered between loads

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)"...

www.access-programmers.co.uk

If you want to "force" a filter save it on unload and load it on load....
Cheers,

 

[gakiss2]

So what part of my solution posted in your previous post didn't met you expectation:

Strange behaviour - filters are not remembered between loads

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)"...

www.access-programmers.co.uk

If you want to "force" a filter save it on unload and load it on load....
Cheers,

Thank You for your contributions.