strage report/subreport behavior...

[dmattarn]

:banghead::banghead::banghead:Ok so this is driving me nuts. i have a subreport that has a control that sums the number of employees on a project. the subreport calculates this number fine and displays it in the main report no problemo. When i attempt to make a control on the main report that references the subreport control value, it also displays it fine. Heres the strange part.... when i set an OnFormat event on the main report that has an if statement to point to the control on the main report, it changes the value on the report to 1.

this is my onformat event:

If Text68 = 5 Then
    Me.Text15.FontSize = 9
ElseIf Text68 = 6 Then
    Me.Text15.FontSize = 8
ElseIf Text68 = 7 Then
    Me.Text15.FontSize = 7
ElseIf Text68 > 7 Then
    Me.Text15.FontSize = 6
Else
    Me.Text15.FontSize = 10
End If

**I have tried using both me.text68 and just text68 as shown above, both give incorrect values in the text68 box and dont change my font size.**

this is the contents of text68:

=[POSummaryAvgRate].[Report]![numEMP]

and just in case you need the info, here is the contents of "numEMP":

=Sum([CountOfEmployees_EmpFull])

 

[llkhoutx]

I think that your control referencing is incorrect. A report's controls should always include the report name.

"=[POSummaryAvgRate].[Report]![numEMP]" puzzles me. Shouldn't in be in the form "Report![Reportname]!ReportControlName]"?

 

[dmattarn]

this is the contents of text68:

=[POSummaryAvgRate].[Report]![numEMP]

this is what the expression builder gave me when i went to define the control source for the text box on the main report.

trying your method still yields the same results. ive also tried changing the if statement to reference the subreport control directly without luck

 

[llkhoutx]

I'd test those values and components with the Debugger on the Onformat event. You may have stumbled upon an undisclosed feature of of the expression builder, this is, building something incorrectly.

Try =Report!numEMP!POSummaryAvgRate. Note that the brackets are only necessary if spacea are in a report, form, or control name.

 

[dmattarn]

Try =Report!numEMP!POSummaryAvgRate

this asks me to enter parameter value for numEMP

 

[llkhoutx]

Evidently that's not a report. Tell us what the parts of that expression mean. Is a report name in the expression? I think not.

 

[dmattarn]

The main report is called "ProjectSummaryReport"
The subreport is called "POSummaryAvgRate"
the subreport control containing the sum of employees is called "numEMP"
The main report control im working with is "Text68"

 

[spikepl]

What happens if you in your IF replace text68 by [POSummaryAvgRate].[Report]![numEMP]?

BTW: Report!numEMP!POSummaryAvgRate is not a reference to anything. To refer to a report you need to start with Reports or simply drop it, when referring to a subreport on current report, so [POSummaryAvgRate].[Report]![numEMP] is quite correct.

 

[llkhoutx]

Google/Lookup how to reference Access report controls.

 

[dmattarn]

What happens if you in your IF replace text68 by [POSummaryAvgRate].[Report]![numEMP]?

no luck there either, font size stays the same still.

 

[spikepl]

Your format event is in the right section?

 

[dmattarn]

On Format event is in the "POID Header" which is where the text box it should be styling is also located.

 

[spikepl]

and you say that text15.fontsize is 1, right after the line containing the End If shown in your first post? Please show entire code for that event, not just snippet

 

[spikepl]

which section is text15 in? Should be same section for which you call the format event

 

[dmattarn]

Text15 is in the POID Header section.
On Format for the POID Header section is as follows:

Option Compare Database

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If [POSummaryAvgRate].[Report]![numEMP] = 5 Then
    Me.Text15.FontSize = 9
ElseIf [POSummaryAvgRate].[Report]![numEMP] = 6 Then
    Me.Text15.FontSize = 8
ElseIf [POSummaryAvgRate].[Report]![numEMP] = 7 Then
    Me.Text15.FontSize = 7
ElseIf [POSummaryAvgRate].[Report]![numEMP] > 7 Then
    Me.Text15.FontSize = 6
Else
    Me.Text15.FontSize = 10
End If

End Sub

 

[spikepl]

So where is the statement telling you what the Me.text15. fontsize value is and what the [POSummaryAvgRate].[Report]![numEMP] is? Insert a msgbox and /or debug.print into that routine and see

 

[dmattarn]

I will insert a message box after i finish this reply.

I can tell the font size has been staying at 10 because i can visually see it across multiple pages of the report. As for the value of numEMP, i have 2 extra text boxes on my report that contain "=[POSummaryAvgRate].[Report]![numEMP]" both boxes show the correct value on all pages unless i choose to reference one of them as a source in my IF statement.

ill try the message box to see whats up with the values now

 

[spikepl]

WHat you see is not important. What Access sees is. This is why you always need to check which actual values you feed in and where do they change, if they do.

 

[dmattarn]

so after throwing in some debug.prints, its showing that [POSummaryAvgRate].[Report]![numEMP] is always 1 and fontsize is always 10 as i flip through the pages of the report. the last page of my report the numEMP should be 7

 

[dmattarn]

some of the odd behavior can be seen in my attachments