?
Solved

Create Table or Query from Open Recordset?

Posted on 2005-03-13
5
Medium Priority
?
2,637 Views
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!

K2Jeff
0
Comment
Question by:k2jeff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 54

Expert Comment

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

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

Need more info ?

Nic;o)
0
 
LVL 54

Accepted Solution

by:
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
else
   docmd.OpenReport "reportname",acViewPreview
endif

Nic;o)
0
 

Author Comment

by:k2jeff
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!

K2Jeff
0
 
LVL 54

Expert Comment

by:nico5038
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.

Nic;o)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …
Suggested Courses

765 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