Report --> Sub Report Issues

[jwhite]

With the code below, I am so close -- in the SUB Report's RecordSource, I am trying to refer to the value of IDNum on the MAIN Report to select the records out of tblProjectsAssigned where IDNum is the same, and pull all of the EmpID's that match the IDNum.

Below I believe I have copied all the information coded/related to the report. If I left out anything pertinent, please let me know. For now, I am at the crossroads looking 4 ways....

tblEmpList
- EmpID      PK AutoNumber, -one _____
- EmpNameLast   text                  \
- EmpNameFirst  text                  |
...                                   |
                                      |
tblProjectsAssigned                   |
- paID       PK AutoNumber     -one   |
- IDNum      FK to tblProjects -many  |
- EmpID      FK to tblEmpList, -many _/
.
.
MAIN REPORT: rptProjectsDet      
    Record Source: tblProjects
    Controls     : txtIDNum, textbox, ControlSource = IDNum
                   ...
.
SUB  REPORT: rptProjectsDetSubA 
    Link Child Fields : IDNum
    Link Master Fields: IDNum
.
    Form RecordSource: 
	SELECT EmpID, EmpNameFirst & ' ' & EmpNameLast AS EmpName 
	FROM tblEmpList INNER JOIN tblProjectsAssigned ON tblEmpList.EmpID = tblProjectsAssigned.EmpID 
	WHERE (((tblProjectsAssigned.IDNum)= Me.rptProjectsDet.Report.txtIDNum));
.
    Controls: txtEmpName, textbox, ControlSource = EmpName
.
   Field List in Design Mode:  EmpID, EmpName
.
.
In Main Report, the IDNum in the detail line prints okay.
When Sub Report is called, 'Me.txtIDNum' has no value and I have to enter a number (like a parameter query)
The dilemma is IDNum is not being passed to the sub report.

 

[boblarson]

Change your Sub Form RecordSource to:

	"SELECT EmpID, EmpNameFirst & ' ' & EmpNameLast AS EmpName " & _
"FROM tblEmpList INNER JOIN tblProjectsAssigned ON tblEmpList.EmpID = tblProjectsAssigned.EmpID " & _
"WHERE (((tblProjectsAssigned.IDNum)= " & Forms!rptProjectsDet.txtIDNum & "));"

The syntax to refer to a control on a main form from a sub form is:

Forms!YourMainFormName.YourControlName

The syntax to refer to a control on a subform from a main form is:

Forms!YourMainFormName.YourSubFormCONTROLname.Form.YourControlName