Query as subform object / capture filter by form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

OneTwoRomeo - 19 Sep 2005 21:58 GMT

I have a main form with a subform called "DynamicQuerySub". There is a
combo-box on the main form listing queries that the user can pick from. The
query selected is then set as the SourceObject for the subform. I also have a
button which exports the SourceObject of the DynamicQuerySub to excel.

What I would like to do is to take into account the functionality of the
"Filter By Selection" and/or "Filter By Form" options. If the user pulls up a
particular query and applies a filter to it to limit the output to a
particular ProductID, for example, I want the output-to-excel to match what
the user is seeing. That is, I want the output to take into account the
filter that the user applied. I do NOT want to output the entire query
dataset.

I know that with Forms that are used as subforms you can use the
"Form.Filter" property to return the filter being applied:

strFilter = Me.DynamicQuerySub.Form.Filter

But that does not work when the source object is a query. Unfortunately, the
source object must be a query, because this needs to be flexible in case of a
new, user-built query that they want to output. I don't want the user to have
to build a subform for every new query.

My question, then, is how do I return or capture the filter being applied to
a query used as the source object in a subform?

Thanks for your help!

Reply to this Message

'69 Camaro - 20 Sep 2005 01:45 GMT

Hi.

> I know that with Forms that are used as subforms you can use the
> "Form.Filter" property to return the filter being applied:
>
> strFilter = Me.DynamicQuerySub.Form.Filter
>
> But that does not work when the source object is a query.

One must use the name of the subform control, not the name of the subform.
Otherwise, this syntax works for me, but perhaps I'm using the Filter
Property differently or I'm using a different version of Access (2003) than
you are. In the following example, subFindings is the name of the subform
control, C:\Work is the directory to output the file to, and Results.csv is
the name of the output file.

Private Sub ExportDataBtn_Click()

On Error GoTo ErrHandler

Dim sName As String
Dim sqlStmt As String

sName = Replace(Me!subFindings.SourceObject, "Query.", "", 1, 1,
vbTextCompare)
sqlStmt = "SELECT * " & _
"INTO [TEXT;HDR=TRUE;DATABASE=C:\Work\].Results.csv " & _
"FROM " & sName

If (Len(Me!subFindings.Form.Filter) > 0) Then
sqlStmt = sqlStmt & " WHERE " & Me!subFindings.Form.Filter
End If

CurrentDb().Execute sqlStmt, dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in ExportDataBtn_Click( ) in " & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

> I have a main form with a subform called "DynamicQuerySub". There is a
> combo-box on the main form listing queries that the user can pick from. The
[quoted text clipped - 23 lines]
>
> Thanks for your help!

Reply to this Message

Tim - 20 Sep 2005 02:55 GMT

Hey, Gunny,

Thanks for the response. This is my problem, but I couldn't get signed on
during the day, so I had a friend post the question for me. Now that I'm
home, I can get signed on again. Go figure.

Anyway, the problem is a little different than what you answered. Let me lay
it out a little more clearly.

The main form is named "FlexReports". It has a combo-box populated by query
names that users have entered in a table, thereby "enabling" those queries
for use on the FlexReports form. Also on the FlexReports form is a subform
which has a query assigned as the source object. The subform object is named
DynamicQuerySub. When the user selects a query from the combo-box, the source
object of the subform is reset to that query. Thus, there is no form being
displayed in the subform control.

So, a user is using the combo-box to examine the data in a query. However,
they can use the Filter By Selection and Filter By Form options to limit what
they are looking at. So if they are looking at a particular query that
normally lists all productIDs, they could apply a filter to show only one
ProductID. In this case, I want my output to Excel to match what they are
looking at. However, if I output the basic query that they have Filtered, I
will output the entire dataset - including every ProductID. If I could get
the Filter that they are applying, I could open the Query with a " WHERE "
statement. The problem is, how do I get the Filter?

*If* a form had been used as the subform, then I know that the code:

strFilter = Me.DynamicQuerySub.Form.Filter

...would work. But because this is a query that they are looking at and
Filtering, I can't use the ...Form.Filter property. The SQL of the query is
not being altered by the Filter, so I can't test the QueryDef of the query
being displayed... I'm running out of ideas. :(

Any help you can give would be greatly appreciated!

TIA

Reply to this Message

Tim - 20 Sep 2005 15:10 GMT

Does anyone know if this is possible?

Can you capture the "Filter By Selection" and/or "Filter By Form" filters
that are being applied to a query that is being used as the object for a
subform?

Reply to this Message