Create Table or Query from Open Recordset?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
K2Jeff
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)
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)
docmd.OpenReport "reportname",acViewPreview
Need more info ?
Nic;o)