Create Table or Query from Open Recordset?

Posted on 2005-03-13
Medium Priority
Last Modified: 2007-12-19
I have a continuous form that the user can sort and filter to the nth degree.  They need to print a report that is based on the forms underlying recordset after they have filtered and sorted it the way they want it to look.   Is there an easy way to create a table or query from the form's recordset clone?  That way I could just have the report's record source point to this new table or query.  

This app is DAO, but I'm open to any method that would save me coding time for a createtabledef option with a lenghtly and complex SQL strings.  The recordset for the form and report is a complex query with multiple joins and parameters.  A maketable query is an option, but this will be a complex hassel because all of the field criteria references for the filters and sorts.

It seems like there should be an easy way to enumerate the fields in a recordset clone object and save it as a persistent table or query object.  However, I cannot find a reasonable way to do this.  Basically I just need to make the report's record souce the same as the form's resulting recordset after the form filters and sorts have been selected.  

Any help would be very much appreciated!

Question by:k2jeff
  • 3
LVL 54

Expert Comment

ID: 13530266
You can just pass the filter when opening the report like:

docmd.OpenReport "reportname",acViewPreview,,me.filter

Need more info ?

LVL 54

Accepted Solution

nico5038 earned 1000 total points
ID: 13530276
Hmm, when it's also possible that everything needs to be printed, the better solution is:

IF me.filteron = true then
   docmd.OpenReport "reportname",acViewPreview,,me.filter
   docmd.OpenReport "reportname",acViewPreview


Author Comment

ID: 13530359
Nico, Thanks for the quick response.  I will give that a try.  I thought the FilterName argument had to be a reference to an existing query object.  I didn't think it would work to pass it the form's filter.  But, I've never tried it before!

LVL 54

Expert Comment

ID: 13530405
The me.filter will contain the filter build using the right-click popup.
The only trouble you can run into is the fact that Access will add a tablename to the fields when there's a JOIN between tables with the same fieldnames.
When you use however the same query as for the report, this shouldn't be a problem.

Personally I use in general a subform for filtering and "echo" the value to the mainform to show it to the user.
For advanced users I even allow the storing of the build filter in a table so they can "re-use" it.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question