Query as subform SourceObject, possible to capture a filter?

[rubbernilly]

Hello all. This is my first foray into this community, but hopefully someone can help with this problem.

I have a subform object gets its SourceObject property set programatically and dynamically. The source object is always a query.

With the subform control displaying a query, the user can still choose to filter the data s/he is looking at. They can choose the "Filter By Selection" or the "Filter By Form" buttons.

When I print the report or export the dataset, I want it to match what they are looking at, right down to any extra criteria that they might have included in their filter. I know that if I used a Form as the SourceObject, then I could use the language:

strFilter = me.subForm.Form.Filter

But this does not work when the SourceObject is a Query.

So my question is...Is there a way to capture the Filter being applied to a query through the Filter by Selection/Form options?

Please help. I have posted this in three other forums and the one, single response I have gotten has been from someone who did not understand the question.

Thanks,

Tim

 

[Remou]

Welcome to Tek-Tips. er ... I don't understand the question .

I have tried a subform with a variety of data sources and filters. Me.SubFormName.Form.Filter always returned the filter applied. Could the problem be coming from somewhere else? Perhaps you could give an Access version, subform name and sample code?

 

[Remou]

Here is piece of rough code:

Dim strSQL, QDFName
Dim qdf As QueryDef
QDFName = Me.[i]SubformControlName[/i].Form.RecordSource
Set qdf = CurrentDb.QueryDefs(QDFName)
strSQL = Replace(qdf.sql, ";", " And " & Me.[i]SubformControlName[/i].Filter)

Assuming the record source is the name of a query and that the query contains a WHERE clause.

 

[rubbernilly]

Absolutely. Thank you for the response and the welcome, Remou. Here is more information about the situation...

In Access 2000, if you have a main form called "MyForm" and a subform control called "MySub", set the SourceObject property of MySub to be a query, "MyQuery". Put a CommandButton on the main form called "MyButton", and put the following code in its OnClick event:

Private Sub MyButton_Click()
msgbox Me.MySub.Form.Filter
end sub

Open MyForm and view the query results in the sub-form. Click on the "Filter By Form" button on the toolbar to set up a Filter, or just pick a field and choose "Filter On Selection." Once your Query shows "(Filtered)" at the bottom of the recordset, click on your button.

You will get an error that says:

Runtime Error 2455 - You entered an expression that has an invalid reference to the property Filter.

There has to be another way to get at this filter information... Access obviously has it stored somewhere.

Any help you can offer would be appreciated. Thanks!

 

[rubbernilly]

I see your code above, Remou, but I can't get the Filter in order to apply it.

In your code, if Me.SubformControlName.SourceObject was set to a query...

Me.SubformControlName.SourceObject = "Query.QueryName"

...then the line...

strSQL = Replace(qdf.sql, ";", " And " & Me.SubformControlName.Form.Filter)

...would produce the error I am describing. Try it, you'll see.

 

[Remou]

Try:
[tt]Set qdf = CurrentDb.QueryDefs("MyQuery")
Debug.Print qdf.Properties("Filter")[/tt]

I think you will find it is storing the filter in the query.

 

[Remou]

Oops, my timing is off! Ignorr my second post, your second post clarified things, so I think you will find my third post more helpful.

 

[Remou]

Still asleep. I should have said the property is only available after a filter has been created. However, you can set the filter property to a space in the initial query, which will make the property available.

 

[rubbernilly]

SWEEEET! You rock, remou. You don't know how long I have been looking for this solution. Thanks!

One question now... there does not seem to be a "FilterOn" property to the querydef. How can I tell if the query is actually being applied or not?

 

[Remou]

You should be able to get this from the forms filter on property: Me.FilterOn

 

[Remou]

Looking at the filter again, this seems to be best:
[tt]Set qdf = CurrentDb.QueryDefs("MyQuery")
DoCmd.RunCommand acCmdSave
MsgBox qdf.Properties("filter")[/tt]

But users will need permission to save changes to forms.

 

[rubbernilly]

Perfect.

You're right, the Query needed to be saved before I could access the current version of the filter as applied by the user from the form.

And even though it doesn't make sense, the main form's FilterOn property does get set when I apply the filter in the subform object. weird.

Remou, you're a life saver. Thank you!