Create Table From Report

[cfobare79]

I would like to create a table from a report to save critical data. The code I have below is what I have been able to get so far but it's not working.

Private Sub Command192_Click()
CurrentDb.Execute "INSERT INTO Report (ReportNum, AgencyID, AgencyName, ServiceGroup, StartDate, EndDate, ServiceName, Total) " & _
" VALUES (" & [Text137] & ",'" & [AgencyID] & "','" & _
[AgencyName] & "','" & [ServiceGroup] & "','" & [Text106] & "','" & [Label175] & "','" & [Text0] & "','" & [Text188] & "')"


End Sub

Please provide any guidance you can.

[pbaldy]

I'd use a variable so you can examine the finished SQL:

http://www.baldyweb.com/ImmediateWindow.htm

Generally, date/time fields need # as a delimiter, not ', text fields need ' and numeric fields have no delimiter.

[ranman256]

You don't create tables from reports...you do it from a query.
if that is what you mean, then take the existing query shown and change it to a MAKE table qry.

[cfobare79]

This is what I have..... I have Tables are filtered for information with a Form that runs a Query. The Query produces a report with the requirements the Form required. I would like to save the information that ends up on the Report into a Table. The main reason is to be able to view reports that were run in the past.

[pbaldy]

Personally I'd create a PDF of the report and save it somewhere (OutputTo).

[cfobare79]

I already have it exporting as a PDF. I would like to have further search criteria if the information needs to be reviewed.

[pbaldy]

Normally you'd just be able to run reports on an older date range and get the same results. If your data is changing, it might indicate a problem. In any case, as ranman mentioned you should be able to export the query. Of course, that would likely still be a restricted set of data.

[TommyK]

You might try puting the Form's 'requirements' into the query instead. Simply running the query (not through the report) will display a table... of sorts. You could then export the query to an un-linked Excel spreadsheet for archival purposes.

If the report performs column totals on rows of data, I don't think that can be done, but the raw data, will still be there.