Error trapping filter on datasheet

[journeyman]

Hi all,

My form contains a subform. The source object is a query. This allows me to offer a filter for the recordset, without needing to write one into a form.

Whenever the user filters on this subform, however, Access considers this a design change. But it's not a design change. Its basic filter functionality.

So when the user closes the main form the following msg presents.

Do you want to save changes to the design on query 'Qry_MyQuery'.

The issue is that you can't trap this error and ignore it, because the source object is not a form.

How do I trap this error - thus not presenting my users with this design question.

Cheers

[June7]

Exactly how does user apply filter?

[journeyman]

The user selects from the datasheet filter options.

i.e. the dropdown button to the right side of the field title.

[June7]

I tested this with form bound to table and don't get that message.
I don't understand your reason for using query but even with query I don't get message.
Want to provide your db for analysis?

[Gicu]

Do you manipulate the subform properties (maybe its record source) in you main form VBA module? Maybe this older thread can help you:
https://stackoverflow.com/questions/...-the-layout-of

Cheers,

[journeyman]

Thanks for responses.

June7
As I mentioned the subform uses the query as the record source. The reason for this is to use the native filter feature of the query datasheet, which you can't do on a created form (you'd have to write one for the form)
The subform source object is set with:

me.subfrm_child.sourceobject = "Query.qry_MyQuery"

Various limitations prevent me from posting the database.

Gigu
Thanks for your link. I was hoping not to have to go to the effort of writing a class to capture a change to the subform datasheet.

It's a stupid error because there is no change to the datasheet. It's a filter, triggering a design change in a compiled user form.

Cheers

[davegri]

My research shows that FILTER is a property of a query. If you use the dropdown to set a filter in a query's datasheet, it is indeed a pending design change.



If you respond to the design request positively, then open the query again, you will see

[June7]

Okay, now I understand. Using query as SourceObject for a subform container control (not RecordSource of a form). Sorry, did not catch that in first post.

Build a form bound to table or query and use form as SourceObject for container. Set form for datasheet view and you will have the same filter tools and no message.

[journeyman]

Okay, now I understand. Using query as SourceObject for a subform container control (not RecordSource of a form). Sorry, did not catch that in first post.

Build a form bound to table or query and use form as SourceObject for container. Set form for datasheet view and you will have the same filter tools and no message.

Okay, good idea. Thanks June, I'll give this a go.


Also Davegri

Thanks, I didn't know this. Now I do!

Cheers