make temporay table

M

Michel Peeters

I am opening a report (based on a cross tab query) via DoCmd... and using
the where clause to filter the records.
For some reasons I want to make a temporary change in the underlying records
(in order to group togehter some customers - with group by it does not go).
Therefore I would like to make a temporary table from these records and edit
the records from this table. This table should be the recordsource from the
report.
What is the best way to do this?
Michel
 
A

Allen Browne

Create the temporary table ahead of time, and populate it with an Append
query. Typically you only need to write the primary key values to the temp
table, and the report is then based on a query joining that to your main
table(s).

This example shows how to clear out the temp table, populate it with the
customers where the PickMe field is True, and open the report:

Dim db As DAO.Database
Dim strSql as String
Set db = dbEngine(0)(0)

'Clear out the temp table.
strSql = "DELETE FROM MyTempTable;"
db.Execute strSql, dbFailOnError

'Populate the temp table.
strSql = "INSERT INTO MyTempTable ( CustomerID, PrintDate) SELECT
CustomerID, Date() AS PrintDate FROM Customers WHERE Customers.PickMe =
True;"
db.Execute strSql, dbFailOnError

'Open the report
DoCmd.OpenReport "Report1", acViewPreview

If you are not sure how to get the SQL statements, you can mock up a query,
change it to an Append query (Append on Query menu), and then switch to SQL
View (View menu) for an example of what you need.
 
Top