Access 2007 - Referring to sub-report value from sub-sub-report

Question

I have a report that has several sub-reports as well as a sub-report to one of the sub-reports.

The one that concerns me:

rptProgressReport ->rptEmployee (sub of rptProgressReport) -> rptSubEmployeeProject (sub of rptEmployee)

So far everything is generating as needed. Employees list in order, and the sub-report pulls out various project details. Except I need to add together time spent from one table.

tblProject (main table) -> tblProjectHistory (related table to tblProject via projectID->fldProjectID).

tblProjectHistory has the following fields. [historyID],[fldProjectID], [History Information], [History Date], [Time Spent], [Employee].

I need to do a sum of all [Time Spent] for projects that equal what is being displayed and as long as the employee matches and the date is within the specified date range.

Specified date range is via the launching Form (frmReportGeneration) with fields txtStartDate and txtEnd Date.

Sub-report rptSubEmployeeProject has a text box (txtTimeSpent) that I have the following for a control source.

=Sum(DLookUp("[Time Spent]","tblProjectHistory","[Employee]='" & [Reports]![rptEmployee].[txtTempEmployee] & "' AND [History Date] > " & [Forms]![frmReportGeneration].[txtStartDate] & " AND " & [History Date]<" & [Forms]![frmReportGeneration].[txtEndDate] & "))

the rptEmployee field of txtTempEmployee correctly displays the current employee to match in that sub-report.

The problem is I get prompted for each value part of the above code - txtTempEmployee and txtStartDate/txtEndDate, even if I change the report value to be [Reports]![rptProgressReport]![rptEmployee].[txtTempEmployee]

Any idea how to correctly pull variables from the parent report or the first sub-report?

Thank you.

+++++ Update +++++ Okay update/close on this. I ended up needing to do something similar to what was suggested in the accepted answer. I could not get the idea posted to work - but i was able to set tempvars in vba and used those throughout the report/sub-report(s).

SEM_jmc SEM_jmc · Accepted Answer · 2014-04-23T14:53:28

it is not recommended to refer to other objects via this construct "Forms!...." (or "Reports!...")
i made some bad experience with it. i suggest to put the values into a variable and make a Get-Funktion:
in a Module you define:

Publicg dtStart As Date
Public gdtEnd As Date

in the form you assign the start and end date in the button where you fire the report

gdtStart = Me!txtStartDate
gdtEnd = Me!txtEndDate

now the Get-Function:

Public Function Get_Date_Start () As Date 
    Get_Date_Start = gdtStart
End Function

Public Function Get_Date_End () As Date 
    Get_Date_End = gdtEnd
End Function

in the Query you can use it now like this:

... AND [History Date] > " & Get_Date_Start() & " AND " & [History Date] <" & Get_Date_End()

BTW: don't use spaces in any object-name: instead of [History Date] name it History_Date, you can avoid Brackets in Code :-)

Access 2007 - Referring to sub-report value from sub-sub-report

1 Answers