Format GroupHeader based on Detail Format

[HomeGrowth]

I have 12 ‘If’ statements to check on 16 fields on Detail section, then I format one field (called txtCurr_Version) based on the 12 ‘If’ statements. This is one of ‘if’ statement on the Detail_Format.


If IsNull(Me.txtRevision_num) Then
Me.txtCurr_Version.BorderStyle = 1
Me.txtCurr_Version.BorderColor = vbRed
Me.txtCurr_Version.ForeColor = vbRed
Exit Sub
End If


The Datail_Format is correctly formatted that way I wanted. Now, I have to format the GroupHeader1 based on the Detail format. The if logic is this:

If any of Detail’s txt.Curr_version = vbRed, then the GroupHeader1’s Me.txtTrainingName.BorderColor = vbRed, otherwise = vbGreen

The questions are
(1) Can I format the GroupHeader1 based on the Detail formatting (can I go back to format the GroupHeader)?
(2) If yes, How should I loop the detail from GroupHeader on format property?

Thank you.

 

[lameid]

No you can't look at the individual sections.

You could however use a variable that you initialize in the group header that has module scope and set it everytime you set something to red.

However, I think the group header format is going to be done by the time that code would run. Maybe if you conditionally sum the nulls...

=sum(IIF(isnull(revision),1,0)

If that is > 0 then red else green.

 

[HomeGrowth]

Thanks for the ideas...I tried many things and I have half way through.

I added a unbound field, this field turns to 1 if Me.txtCurr_Version.BorderColor = vbRed, otherwise, stay 0.

Now, how can i sum up this unbound field

I used

=Sum(txtCountRed)

That doesn't work.

 

[dhookom]

There must be an expression or something that determines your formatting. Share it so someone can help.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

You cannot sum controls in a report only data that you could get out of a query...

What was wrong with my suggestion of using a control source that sums? I did assume based on your code that the control source of txtRevision_num is revision (mostly by missing the _num)... Perhaps it is revision_num...

=sum(IIF(isnull(revision_num),1,0)

You can test that control for your condition in order to set your color.

 

[HomeGrowth]

Thank you hdookom and lameid for your reply.

Hdookon. Here is my GroupHeader on_format


Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

If Sum(IsNull(Me.txtCountRed), 0, 1) > 0 Then
Me.txtTrainingName.BorderColor = vbRed
Else
Me.txtTrainingName.BorderColor = vbGreen
End If

End Sub


But the if statement the Sum returned compile error 'sub or function not defined'

lameid. I don't think I could use

=sum(IIF(isnull(revision_num),1,0)

because the database checks more than IsNull on revision-num field. I have other 11 'if' statements that check addition conditions. If one of 11 'if' statements is false, format the Me.txtCurr_Version.BorderColor = vbRed

Now right, I modified to mark the unbound field = 1 everywhere the Me.txtCurr_Version.BorderColor = vbRed.


If IsNull(Me.txtRevision_num) Then
Me.txtCurr_Version.BorderStyle = 1
Me.txtCurr_Version.BorderColor = vbRed
Me.txtCurr_Version.ForeColor = vbRed '255
Me.txtCountRed = 1
Exit Sub
End If

(There are other 11 'if' statements to check other conditions)

Back to my question:
How can you sum up the unbound field on my GroupHeader on format?
Thanks!

 

[dhookom]

You can't sum unbound fields. You can create a text box with a running sum and then reference it in the Control Source of another text box in another section of the report.

I would have to agree with lameid that you should create an expression that uses fields from your report's record source. You could use a user-defined function. I believe this would need to reference your 11 other if statements.



Duane
Hook'D on Access
MS Access MVP

 

[HomeGrowth]

Thank you again hdookom and lameid for your reply.

I took your advice to add a calculation field on report's record source. This calculation field use a function to check all the conditions, the result will return 0, 1, & 2 for each record. I sum up on the headergroup, and the format will be based on the sum. It works out pretty good, I need redo and built the user-defined function.