create temp table that holds query results

[NRK]

I have a form that creates a filtered report based on user criteria. The users may wish to save the filter (which is a query).

My thought is that I should create a temporary table that holds the data that the query retrieves. That way, users can come back later and access the data (in the table) by opening the form or by running the report.

I think that the way to go about this is use a Make Table query when a button is clicked. Is there a way to do this? Is there a way to dynamically prompt the user for a table name, then create the table based on the query?

Would appreciate feedback on my idea or recommendations if there is a better concept.

Thank you, in advance.

 

[B827]

NRK

I find that most of my users like to get an Excel spreadsheet of the results when they are viewing them. Then they go and play to their hearts content. This means you don't have an ever expanding number of tables and queries to handle. Assuming the report is based on a query taking parameters from the form then this is straight forward. I find this code (Access97) very useful:

Dim qdf As QueryDef, mydb As Database, SQLstring As String, oApp As Object
Set mydb = CurrentDb
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, QueryName, FileName, True

Set oApp = CreateObject("Excel.Application&quot
oApp.Visible = True
On Error Resume Next
oApp.UserControl = True
oApp.workbooks.Open FileName'Open the spreadsheet


Sandy

 

[NRK]

Very true...they already have me pushing the query/report to Word and Excel files.

The purpose behind creating tables is that when they select records from the form there could be hundreds. By saving the query in a temp table, the user can go back and run reports/export data from the temp table or can modify the query. Does that make sense?

As a point of information, I am concerned with users having the ability to create tables, but it will only be 2-3 users whom I will be working with relatively closely.

 

[B827]

NRK,
It does, I'm off home to put Dad's taxi into operation so I'll mull it over - if anything positive comes up I'll post it in the morning. Unless someone comes up with something better come up in the mean time. Sandy

 

[B827]

NRK,
I think that you would have quite an administrative task on your hands. I don't know how skilled the people are but have you thought of splitting the database and giving them their own front end and some training on Queries and reports. Then they can generate and maintain their own reports etc. There is no danger of them messing up each others reports then. You still have a problem in keeping them synchronised but as there are only a few people involved importing into the new version shouldn't present too great a problem.

Sandy

 

[NRK]

I have been thinking about this and because of my method of creating the query for the report, I don't know if this idea would work.

Better description of my situation:
User opens form (QBF), selects Category field (combo box), selects records in listbox (populated by selection in combo box). Then, the user selects the records from the listbox and moves them to a second listbox. This listbox is the selection criteria for the report.

To do this, I have two checkboxes on my table (which is the basis for the query/ report). So, as selections are moved from the first listbox to the second, a checkbox changes value (False to True).

This value (T or F) is what the report searches for. Upon exit of the report and form, all the checkboxes are reset to default value (False). So, that is why I believe I need to save the query by creating a Make Table. Otherwise, just saving the query or generating a report a saving it will not be effective.

Hope that clarifies my position. I thank you for your continued feedback - I agree with you on your last statement, but I am unsure if it is feasible given my unique situation.

Thank you, again.