TempVars in Reports Record Source?

mdnuts

Registered User.
Local time
Today, 12:06
Joined
May 28, 2014
Messages
131
Access 2007 Sub-Report "rptSubEmployeeProject" inside report "rptProgressReportDay".


I need to dynamically change the table in the sub-report's record source. I tried (line wrapped in code tags below for reading purposes)

Code:
SELECT tblProjectHistory_fldProjectID, 
  FirstOfHistory, [History Date],  [Time Spent],
  Employee, fldAssigned, TheFieldPriority, 
  fldTitle,  employeeID, fldTimeSpent,
  fldStatus, fldHistoryID, fldOrder 
FROM " &  [TempVars]![TempEmpTempTable] & " 
ORDER BY fldOrder;
and I get the error of invalid bracketing of name and it refers to the [TempVars]![Temp part. Makes me believe that I cannot use TempVars in a Reports RecordSource, is that accurate? If So that leaves me trying to set a sub-reports recordsource via vba right?
 
I am in doubt if you can update the recordsource of a report like that.

But why don't you use one temp table, which you empty and fill and then use in your report. Why does your temptable need to be "dynamical"?
This is at least what I do.
 
If you select data from fields of the same name but from different tables, then you have a data normalization problem. Just like you do not keep a separate wallet for each week's spending, you do not store similar data in different containers.
 
I've already got that going - sort of. in order to use one query for multiple similar reports i have the end report pull whatever specific data relates to it. (e.g. one user, entire team).

Currently when the query runs it dumps records into a temp table for the time period selected, that temp table is copied to a second temporary table. The secondary table then loops against the first table to find certain duplicate possibilities. Then the report opens and filters the first temp table with records remaining for the user or team.

What I found happens is if multiple people pull reports at the same time it fails until the first person exists out of the report. Given it's a time recording type of database, the likelihood of this happening frequently is high. My thought process then turned to create temp tables based on the username (from environ).

Which so far is all great, except telling the report what table to look into.
 
Don't make bandaid patches. Make-table queries are oftentimes also a sign of inappropriate data structure. Tells us what is going on - which data needs to be stored and how it is related, and you might get a better data structure out of it all.
 
The problem happens because you are writing the temp tables to the back end where every use can see them.

Each user should have separate temp tables. Some developer put them in the front end but I consider that a bad practice. Better put in a separate local database which I call a Side End.
 
And yeah,,, temp tables are shared, unless you mark it within the table which user made the temp data.
 
ok,

So it's a project database that tracks time.
tblProject stores the main info on that
tblProjectHistory stores specific history added to the project including employee, time, project id, etc.
tblProjectStatus retains the status information on the project, opened, closed, etc. this could repeat for example a project can be opened, then closed and later opened again - the dates with this are stored, along with the project ID
**note** an employee can have their own history for someone elses project.

for project reports what has to happen is each project that had activity for the desired timeframe has to be listed. As history can often be repeating we only want it to show one time but still add the time together. example project "meetings", history of "weekly meeting" - 1 hr, and the next week "weekly meeting" - 1 hr - shows up once but time of 2 hrs.

if someone had time allotted to anothers project, the project shows up on their report but only the time they spent is on it..

Overall reports loop through each employee, then each project and related project history with time (and status's).

Other reports pull their own project report for a given day, week, last week, month, etc. the interface is the same for everyone.
 
The problem happens because you are writing the temp tables to the back end where every use can see them.

Each user should have separate temp tables. Some developer put them in the front end but I consider that a bad practice. Better put in a separate local database which I call a Side End.

I should have mentioned split, dont know why I didn't think of that.

It's reversed in this case. When the report gets generated the tables get stored in the front end. All the rest of the data is in the back end.
 
If anyone else needs this.

First upon loading the main form page I checked for the users temp table, created it if it did not exist and set a tempvar for the Users username (after converting it to only alphanumeric.

Then i have VBA pushing and shoving things through tables as normal. For the report I left recordsource blank and put the following in the Open event. Of course your SQL will vary.

Code:
Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String
Dim strTempTable As String

strTempTable = "tblEMPTemp_" & [TempVars]![sUserName]
strSQL = "SELECT FirstOfHistory, [History Date], [Time Spent], Employee,  fldOrder FROM " & strTempTable & " WHERE Employee='" & [TempVars]![TemporaryEmployeeName] & "' ORDER BY fldOrder;"
Static Initialized As Boolean
If Not Initialized Then
  Me.RecordSource = strSQL
Initialized = True
End If
End Sub
 

Users who are viewing this thread

Back
Top Bottom