KashMarsh wrote:
Access 2003
I need to have a user filter records on a linked, continuous form and
then I want to run various reports/queries from this recordset the user
created. I only need to see the PK values they filtered for.
On the form, there could be 10 controls displaying that the user
filters/sorts in whatever way they want to, but I only need to extract
the control holding the PK values as a "recordset" so that I can use
this to create a various amount of Reports/Queries from.
Can anyone please shed some light on how to do this?
Thanks in advance.
If all of the reports can use the same filter, create the report with no
filters in the recordsource. Then create the filter prior to calling
the report.
Method 1
For example, let's say you had a form with 10 controls used to filter.
First, create a Where clause. IE, cycle though the filter fields on the
form. Ex:
If Me.FirstName > "" Then strFilter = "[FirstName] = ""Joe"" And "
If Me.LastName > "" Then strFilter = "[LastName] = ""Blow"" And "
strFilter = Left(strFilter,Len(strFilter)-5 'get rid of And
Docmd.OpenReport "MyReport",,strFilter
This will open the report and filter it.
Method 2
You can also get the SQL statement of the report's recordsource and
break it out into parts; Select&From, Where, OrderBy
Build the Where filter string for the fields, a sort order string, and
create a SQL statement. Now make that SQL statement the Report's
recordsouce or a Query's SQL.