how to change recordsource

[Ravingdragon]

Hello there,

I am showing a report based on a temporary recordset because I there join records from different sources and would like not to change the master recordsets.
I do this in a Report_Open macro which also builds the .RecordSource SQL query statement.
This works fine but so far I cannot get rid of the temporary recordset as it is still "open" in the Report_Close event and thus deleting fails.
Anyone knows away to "unlock" or close the active recordset of a report ?
Thanks for any help

Greeting
Christian

 

[mdthornton]

Not sure how you are doing this but why do need to close it? This will be done for you when the report closes.

Did you build your SQL in a macro or module? I can't see how
you can do it in a macro.... Best Regards,
Mike

 

[Ravingdragon]

what i want to do is

Report_Open (macro (or coded event handling if u like) in report itself):
1. create temporary recordset (or temporary table in my case)
2. fill it
3. build query upon this recordset
4. put query as .recordsource
5. show report

... (let)
.. (user)
. (see data)


Report_Close:
6. unlock or close temporary recordset
7. delete it

What I am missing is step 6. because now I get the error 3211 on my drop operation meaning something like "unable to lock table as it is used by another user/process". I think that when the closing event is thrown I should be able and allowed as well to remove the source recordset because the report is no longer shown. Another solution would be to change the .recordsource (to a regular table for example) which should automatically unlock the old source in my opinion.

 

[mdthornton]

Well if you can do it with a recordset you don't need to do anything as this will be "deleted" when you close the report. If you have to use a temporary table why not build and/or populate the table before you open the report and then just open/close the report normally. Don't get hung up trying to delete tables in the report close event. Best Regards,
Mike

 

[cmmrfrds]

What do you mean by creating a temporary table? Is this done through ADO? Are you creating a recordset using a client side cursor and then doing some data manipulation on the data before displaying on the report? If not, there should be no need "drop" it. A temporary ADO recordset can just be closed and set to nothing.

If you are creating sql to equate to the recordsource on the report, there is no need to drop the recordset as it will disappear when the report is closed.