Use the results of a Filter by Form

[Guest]

Hi!

Is there a way to capture and use the results of a filter by form? ie use
the filtered results in a report or query?

I have a form which acts as a search - several unbound text boxes and check
boxes which trigger srting searches - it basically replicates a filter by
form but with a nicer interface.
The filter works fine, but I would like to use it's results without having
to re-create the SQL (theres about 20 fields that can be searched on so it
would be very messy when accounting for "or")
Any suggestions?
ta

 

[Allen Browne]

Assuming you have a report that uses the same table/query as your form, try
using the form's Filter as the WhereCondition for OpenReport

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

In Access 2002 or later, you can filtered on the display text in a combo,
even if its bound column is hidden. If you are doing that, the above won't
work without modification.

 

[Ben]

Hi there,

You can access a form's filter using "[forms]![frmName].filter" - or just
"me.filter" from the forms code module.

To apply this to a Report use:

DoCmd.OpenReport "MyReport", acViewPreview, , Me.Filter

Just make sure that the RecordSource of the report is IDENTICAL to the
RecordSource of the form.

Applying the Filter to a query would be different and would depend on what
you wished to do with the query - display results (in which case use a
report), or allow the user to work with the queried dataset interactively
(in which case use a form). Generally, I would not open a query in datasheet
mode for a user, and I'm not sure whether you can programatically filter the
results in this way.

Anyway let me know!

Ben.

 

[Guest]

Brilliant - Thanks for the quick responses.
me.filter worked a treat for a report, and I can use the same query as the
record source for all the things I need to do.

The reason for wanting to generate a query or datasheet view is so that the
search form can be used to generate a list to send to another program (to
manage mass emails of newsletters to members).
OutputTo gets the whole recordset without the filter...the best I have
thought of so far is to have the option to change the view on the form to
datasheet.
Any better ideas?
Thanks again,
Meg

Hi there,

You can access a form's filter using "[forms]![frmName].filter" - or just
"me.filter" from the forms code module.

To apply this to a Report use:

DoCmd.OpenReport "MyReport", acViewPreview, , Me.Filter

Just make sure that the RecordSource of the report is IDENTICAL to the
RecordSource of the form.

Applying the Filter to a query would be different and would depend on what
you wished to do with the query - display results (in which case use a
report), or allow the user to work with the queried dataset interactively
(in which case use a form). Generally, I would not open a query in datasheet
mode for a user, and I'm not sure whether you can programatically filter the
results in this way.

Anyway let me know!

Ben.

Hi!

Is there a way to capture and use the results of a filter by form? ie use
the filtered results in a report or query?

I have a form which acts as a search - several unbound text boxes and
check
boxes which trigger srting searches - it basically replicates a filter by
form but with a nicer interface.
The filter works fine, but I would like to use it's results without having
to re-create the SQL (theres about 20 fields that can be searched on so it
would be very messy when accounting for "or")
Any suggestions?
ta

 

[Allen Browne]

As you found, OutputTo doesn't have a WhereCondition.

The alternative is to set the Filter of the form in its Open event, so the
filter still gets applied when you output the report.

1. In the General Declarations section (top, with the option statements), of
a standard module (click the Modules tab of the Database window, and then
click New), create a public string variable:
Public gstrReportFilter As String

2. In the Open event procedure of your report, apply and clear the filter if
there is something in the string:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If

3. Before you call OutputTo, set the string:
If Me.FilterOn Then
gstrReportFilter = Me.Filter
End If
DoCmd.OutputTo acOutputReport, "Report1", acFormatTxt ,"C:\\MyFile.txt"

 

[Guest]

Thanks again fo your help Allen - this does exactly what I was after and I
like the simplicity.
Have a great day in sunny Perth! (we are enjoying the rain in SE NSW)