~~Dcount / Subform issue~~ (1 Viewer)

[Geordie2008]

Hi All,

I have a Dcount formula in a normal report that is working fine.

When I try and put this into a sub form it falls over and returns #name.

Do I need different syntax when doing a subform dcount? Ive been working on this for several hours and have gotten nowhere....

Thanks,
Mandy

 

[pbaldy]

The syntax would change:

http://www.mvps.org/access/forms/frm0031.htm

 

[Geordie2008]

Sorry, I meant Reports and Subreports.... (not subform)

Does that make a diff? also, when calculating a count of IDs in a table using dcount, would that be "To refer to a form property, like RecordSource"

Regards,
Mandy

 

[pbaldy]

I'm guessing, since you haven't posted the formula, but yes, the same thing applies to reports (just change "Forms" to "Reports" and "Form" to "Report" in the appropriate places). And I suspect you want the next one, "to refer to a control".

 

[Geordie2008]

My original formula (which works fine) to reference a table from the main report (a main report with no sub reports) is:

=DCount("ID","Tbl_MAIN_Staff_Details","New_Tier_II_Owner ='" & [Reports]![rpt_Current_Report_Synergy_Tier_II_Owner_Count]![New_Tier_II_Owner] & "' AND Synergy ='" & [Reports]![rpt_Current_Report_Synergy_Tier_II_Owner_Count]![Synergy] & "' AND Employee_Indicator ='" & [Reports]![rpt_Current_Report_Synergy_Tier_II_Owner_Count]![Employee_Indicator] & "' AND Employee_Status ='" & [Reports]![rpt_Current_Report_Synergy_Tier_II_Owner_Count]![Employee_Status] & "'")


My updated formula, to reference a table from the sub report is:

=DCount("ID","Tbl_MAIN_Staff_Details","New_Tier_II_Owner ='" & Reports![report b]!New_Tier_II_Owner & "' AND Synergy ='" & Reports![report b]!Synergy & "' AND Employee_Indicator ='" & Reports![report b]!Employee_Indicator & "' AND Employee_Status ='" & Reports![report b]!Employee_Status & "'")

But this doesn't work and I still get #Name...

Please could you help me fix what Im doing wrong?

Thank you,
Mandy

 

[pbaldy]

Well, the syntax from the link is:

Forms!Mainform!Subform1.Form!ControlName

so for a report try:

Reports!MainReportName!SubReportName.Report!ControlName

 

[Geordie2008]

It was actually far wasier than that.....

=DCount("ID","Tbl_MAIN_Staff_Details","New_Tier_II_Owner ='" & [New_Tier_II_Owner] & "' AND Synergy ='" & [Synergy] & "' AND Employee_Indicator ='" & [Employee_Indicator] & "' AND Employee_Status ='" & [Employee_Status] & "'")