469,292 Members | 1,326 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,292 developers. It's quick & easy.

How do I get a recordset from a filtered form?

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.

Apr 3 '06 #1
2 2080
"KashMarsh" <ka********@hotmail.com> wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:
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.


Grab the recordsetClone and loop through it, writing the pk to
a new table.

--
Bob Quintal

PA is y I've altered my email address.
Apr 4 '06 #2
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.
Apr 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by dbaxter7 | last post: by
3 posts views Thread by Thelma Roslyn Lubkin | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.