Create temporary table as Recordsource for Report ? ? (1 Viewer)

liamfitz

Registered User.
Local time
Today, 18:38
Joined
May 17, 2012
Messages
240
Does anyone know if I can create a temporary table, 'on the fly' on the Report_Load event, ( based on fields from two other tables ) and set this as the recordsource for a report. I'm toying with the idea of 'INSERT INTO'. Would this be appropriate, and would it work ? Thanks.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:38
Joined
Aug 30, 2003
Messages
36,118
It could be done, but why not just use a query as the report's source?
 

liamfitz

Registered User.
Local time
Today, 18:38
Joined
May 17, 2012
Messages
240
It might be a complex query, and I'd be better off creating a table. I want two columns of data, but one 'column' on the report would be made up of several fields. I want the count of each of those fields ( with a WHERE clause ) to be listed as if they are several values from one field. As you know, you can normally only set one fiedl value ( or calcualted value ) for a given column of data/info. I'm considering CREATE TABLE, with the 2 fields I want to be shown on the report, then insert into, using calculations on the 'awkward' fields. I would like to know how to then set the recordsource of the report dynamiclaly in the Load event say ? Thanks.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:38
Joined
Aug 30, 2003
Messages
36,118
Me.RecordSource = "TableName"

But it probably needs to be in the open event.
 

liamfitz

Registered User.
Local time
Today, 18:38
Joined
May 17, 2012
Messages
240
Thank you pbaldy. I was just thinking that i.e. putting the table creation code in the load event ( especially as the values are likely to change often ), and then set the recordsource in the open event.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:38
Joined
Aug 30, 2003
Messages
36,118
Check the order the events fire in. I think you want it all in the open event.
 

Rx_

Nothing In Moderation
Local time
Today, 12:38
Joined
Oct 22, 2009
Messages
2,803
In general, just use the query is my preferred method. Sometimes, the data needs to be pulled into a local table. This is not something done for every report.

In these situations bringing the data over into tables allows time for a final analysis and assembly to be performed that can take several minutes. It also helps in the troubleshooting for version upgrades by seeing the result of each local table.

Here is a short version of creating a non-temp table for a complex report.
The tables have an autocounter so various user inputs create Cartesian products from different sources - then the resulting table's autocounters magically (with some planning) match in the different tables for later uses such as a combined cross tab query. The autocounters fields of each table are actually joined.

Table Data is purged, autocounters reset to zero, then selected data pulled from SQL Server back to the local tables. The local tables have a series of functions with business rules that cross check across these tables. These functions can take minutes to run. The idea is to avoid a multi-user environment from changing a record during the multi-step process.

All of this is part of calling the final function to create the reports in Excel. Having the local tables filled with data really helps with the rule-based Regulatory side of breaking down the steps. It also helps to pull the data that will be revisited across the network once, and maybe used to create multiple Excel based reports.

The lbl (labels) are just a way to let the user know something is going on, sometimes over 9 minutes depending on the request.

Code:
160       DoCmd.SetWarnings False
          Me.lblReportStatus.Visible = True
          Me.lblReportStatus.Caption = "Delete last Fed Data"
170       DoCmd.RunSQL ("Delete * From 08_Team_Permit_Federal_Excel_Local")
180       Call ChangeSeed("08_Team_Permit_Federal_Excel_Local", "CounterField", 1, 1)
190       DoEvents
          Me.lblReportStatus.Caption = "Delete last State Data" 
200       DoCmd.RunSQL ("Delete * From 08_Team_Permit_State_Excel_Local")
210       Call ChangeSeed("08_Team_Permit_State_Excel_Local", "CounterField", 1, 1)
220       DoEvents
230       DoCmd.RunSQL ("Delete * From 08_Team_Permit_Final_Excel_Local")
240       DoEvents
                  ' Deleted the State, Federal temp tables data and reset the seed
                  ' Deleted the temp table that combines State and Federal
                
                  ' Rebuild the State, Fed temp tables - and run the query that combines them
            Me.lblReportStatus.Caption = "Repopulate Fed Data"
250        CurrentDb.Execute "008_Team_Permit_Final_Excel_Fed_Append"
260        DoEvents
            Me.lblReportStatus.Caption = "Repopulate State Data"
270        CurrentDb.Execute "008_Team_Permit_Final_Excel_State_Append"
280        DoEvents
            Me.lblReportStatus.Caption = "Join State and Fed Data"
290        CurrentDb.Execute "08_Join_State_Fed"
300        DoEvents
          '   ---------------   Repopulated the  tables used for the report ----------
          DoCmd.SetWarnings True
This is all contained in a Function that starts the report process in the command button's click event.
 

liamfitz

Registered User.
Local time
Today, 18:38
Joined
May 17, 2012
Messages
240
Problem solved, thanks. I created a couple of recordsets, selecting the fields I want from the 2 tables in question. I then 'Recordset.Addnew' to the newly created table/recordset in a controlled loop, for each new record. Works perfectly.
 

Users who are viewing this thread

Top Bottom