Modify group header from code within detail?

  • Thread starter Thread starter Nathan C. Lee
  • Start date Start date
N

Nathan C. Lee

I have a VBA program which will decide the color of TextBoxes inside the
detail section based on dates in a query. If a TextBox turns red in ANY of
the records, the TextBox in the group header must turn red, otherwise it
will remain green. My code, which will run On Format in my detail section
reads:

Me.ProgramHeaderField.ForeColor = vbRed

The problem is that when I do this, the color is changed on the NEXT group
header, which is not appropriate. I realize that it is an order-of-execution
problem when Access is formatting the page, because if I store the TextBox
in a group footer instead of a group header, the color is changed correctly.
So how can I have it "go back" and change the color on a previously printed
group header?

Nathan
 
Nathan said:
I have a VBA program which will decide the color of TextBoxes inside the
detail section based on dates in a query. If a TextBox turns red in ANY of
the records, the TextBox in the group header must turn red, otherwise it
will remain green. My code, which will run On Format in my detail section
reads:

Me.ProgramHeaderField.ForeColor = vbRed

The problem is that when I do this, the color is changed on the NEXT group
header, which is not appropriate. I realize that it is an order-of-execution
problem when Access is formatting the page, because if I store the TextBox
in a group footer instead of a group header, the color is changed correctly.
So how can I have it "go back" and change the color on a previously printed
group header?
Click to expand...


You can't make it "go back", you have to make the descision
in the group header section's Format or Print event. What
is the condition that determines whether it should be red or
not?
 
There are five categories, each with 2 - 5 entries for each record. If an
entry is late (determined by comparing two dates within each record), then
the category turns red. If any category is turned red, the part number (the
key field for the record) is turned red. If any part number is red, then the
TextBox in the header field must turn red. The trick is that there is one
header text for multiple records. Any ideas how to pull this off? I don't
think I can put my VBA code in the header section, because it's relying on
results calculated in each record.

visual example:

Header Text

part no. category 1 category 2 category 3 ...
entry a1 entry a2 entry a3
entry b1 entry b2 entry b3

part no. category 1 category 2 category 3 ...
entry a1 entry a2 entry a3
entry b1 entry b2 entry b3

part no. category 1 category 2 category 3 ...
entry a1 entry a2 entry a3
entry b1 entry b2 entry b3

Header Text

part no. category 1 category 2 category 3 ...
entry a1 entry a2 entry a3
entry b1 entry b2 entry b3

part no. category 1 category 2 category 3 ...
entry a1 entry a2 entry a3
entry b1 entry b2 entry b3
 
Nathan said:
There are five categories, each with 2 - 5 entries for each record. If an
entry is late (determined by comparing two dates within each record), then
the category turns red. If any category is turned red, the part number (the
key field for the record) is turned red. If any part number is red, then the
TextBox in the header field must turn red. The trick is that there is one
header text for multiple records. Any ideas how to pull this off? I don't
think I can put my VBA code in the header section, because it's relying on
results calculated in each record.
Click to expand...


Let's try something along these lines. Assuming your two
date fields are named SchedDate and ActualDate and you want
to make the header text red if any one record is late, add a
text box named txtMaxDaysLate to the header section and set
its control source expression to:
=Max(DateDiff("d", SchedDate, ActualDate))

Then in the header's Format event, use:

If Me.txtMaxDaysLate > 0 Then
Me.headertextbox.ForeColor = vbRed
Else
Me.headertextbox.ForeColor = vbBlack
End If
 
The trouble with this is that there is a SchedDate and ActualDate for each
record. Multiple records can fall under one header, and any one of them can
cause the header to go red. I suppose I could evaluate the date fields in
each record while I'm in the VBA for the header, just duplicating some work
at worst. I'm not sure how to step through each record, then step back for
the individual records to run their code.

Nathan
 
Nathan said:
The trouble with this is that there is a SchedDate and ActualDate for each
record. Multiple records can fall under one header, and any one of them can
cause the header to go red. I suppose I could evaluate the date fields in
each record while I'm in the VBA for the header, just duplicating some work
at worst. I'm not sure how to step through each record, then step back for
the individual records to run their code.
Click to expand...


I don't think you tried my suggestion. The Max function is
an aggregate function that checks all the records in the
group and will have a positive value is any one of them is
late.
--
Marsh
MVP [MS Access]



 
Back
Top