Filter Form based on records in table

[ECEK]

Pretty much what it says on the tin.
I open my customer form and it is filtered by the customers that are in Table1

Easy right?

Or

I manually filter my form. When I open Access tomorrow the same filter is still applied.

Thanks in advance as always

 

[Minty]

Okay - I don't think I know what your question is.

You want to store the filter you applied if you applied one?
What if someone else opens that form and doesn't realise you had a filter applied ?

Do you want this to work globally (All users get the same filter) or individually(It's stored per user front end?)

 

[theDBguy]

Hi,

Please pardon my confusion but did you have a question?

 

[ECEK]

Each user has their own (same) front end and will filter as they require.
Yes I want to store the filter you applied if you applied one?

 

[ECEK]

Yes sorry guys.

I would like know how to filter my data in an Access form and for that filter to be the same the next time I open Access?

 

[theDBguy]

Hi,

Thanks for the clarification. Are you deploying ACCDEs or ACCDBs?

 

[ECEK]

The Front ends are in accdb but with a accdr file extension to open as runtime with the data in a SQL Server.

 

[theDBguy]

Okay, that tells me users won’t be able to change form designs, so perhaps the closest thing you could implement is to create a local table to store the applied filter and then apply it again, using code, when the form opens.

Hope it helps...

 

[ECEK]

Wouldn't that mean that the user would then be working on local data and not the global SQL data? The data needs to be seen by several parties.

 

[theDBguy]

No, it doesn’t mean that at all. What I was suggesting is to store only the “filter “ information locally so each user can have their own filter.

Hope it makes sense...

 

[ECEK]

Thanks DBguy.

How do I save the filter?

 

[theDBguy]

You can use an UPDATE query or VBA.

 

[Minty]

Just to fill out on the suggestion, you would create a local table in the front end.
You need a few fields like
FormName, strFilterValue

You may want a few others fields depending on how user friendly you want your db to become.

I store form filters, form positions and sizes in mine, as it allows users to "customise" their environments a little bit and makes them feel all warm and fuzzy

 

[theDBguy]

Hi Minty,

Thanks for the assist. I’m only using my phone, so had to be a little brief.

 

[Minty]

Glad to assist !

Happy Thanksgiving , we have offices in SD - I must shout you up next time I get over the pond !

 

[theDBguy]

Hi,

Thanks! Sounds like a plan.

Cheers!

 

[ECEK]

Anyway !!!
I have no idea what you are talking about ?
Create a table ? to store the filter.
Could you perhaps point me in the right direction to study what your'e on about.
Thanks for you time so far

 

[theDBguy]

Hi,


Since we do not know what you know, here are some links I hope will help.


1. Create a table in Access
2. How to create a query
3. Create and run update queries
4. Form events (to use for your VBA)

 

[ECEK]

What I don't know is what you are talking about?

I know how to create a table
I know how to create a query
I know how to create and run updates
I know how to use VBA to a degree

What I don't know is how to answer the question that I put to you!

I simply want to be able to either

a) retain the filter that I selected on a form or
b) open my form filtered based on data in another table

I suppose the answer is "do it in VBA" but then that's not very helpful?

 

[ECEK]

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.