Applying Filter to Report & All Bound Children on Report (1 Viewer)

mhorner

Registered User.
Local time
Today, 06:18
Joined
May 24, 2018
Messages
50
Hi everybody,

I have a report I've created which pulls in query data from two separate queries:

The report is called: rptMonthlyReport
It is bound to the source: qryMonthlyReport

This report allows us to select an employee from a dropdown (cboEmployee) and view all active project records for them. A need was introduced to print completed projects for the month by the same employee.

So, I created a child on the report and bound it to a second query:

Report: rptMonthlyReport
Source: qryMonthlyReport
Child: sbrptCompletedReport
Child Source: qryCompletedReport


All works fine and dandy, but....when I select an employee from cboEmployee, apply the filter, and open the report -- the filter is only applied to the rptMonthlyReport and qryMonthlyReport.

Code:
If Not IsNull(Me.cboEmployee) Then
ReportFilter = "[Representative] =" & "[Forms]![frmHomeProjects]![cboEmployee]"
End If

DoCmd.OpenReport "rptMonthlyReport", acViewReport, "", ReportFilter, acReadOnly, acNormal

sbrptCompletedReport and qryCompletedReport which are bound to the main report do not apply this filter, so all completed projects for EVERY employee are printed on this subreport.

I tried linking the master and child fields in the property sheet of the subform, but I can't seem to find the right combination of fields to link to give me results where the value in the cboEmployee control matches the [Representative] field both rptMonthlyReport and its subreport rptCompletedReport.

Any ideas how to apply this filter to both the main report and its subreport?
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:18
Joined
Jan 23, 2006
Messages
15,361
Can you post a copy of the database -- remove anything confidential/private first --in zip format?
We only need a few records to highlight the issue.
 

Ranman256

Well-known member
Local time
Today, 06:18
Joined
Apr 9, 2015
Messages
4,337
Don't put quotes around the form object....

If numeric:
SFilter= "[field]=" & forms!myform!txtBox

If string,
SFilter= "[field]='" & forms!myform!txtBox & "'"
 

mhorner

Registered User.
Local time
Today, 06:18
Joined
May 24, 2018
Messages
50
Can you post a copy of the database -- remove anything confidential/private first --in zip format?
We only need a few records to highlight the issue.

Yes sir, see attached. Forgive that many features have been removed to maintain confidentiality.

I've highlighted the Employee dropdown selector and report buttons in yellow. All you need to do is run press the yellow button and it will generate the report,

However, as stated above, take note that filter is applied based on the employee selected in the dropdown box. This filter only seems to apply to the main report and not any subreports. I'm ultimately looking to apply this filter to both the main and subreport.

Any ideas are appreciated!
 

Attachments

  • Database1.zip
    367.4 KB · Views: 108

JHB

Have been here a while
Local time
Today, 11:18
Joined
Jun 17, 2012
Messages
7,732
Could you show in a printscreen what you expect to see?
 

Users who are viewing this thread

Top Bottom