Conditional Formatting in a Group on a Report

[Guest]

I have a report with data such as the following:

Name ---- Course ----- Dept ----- Grade

John ----- Algebra ---- Math ----- 77
John ---- Geometry --- Math ---- 81
John ---- Sculpture --- Art ------ 99
John ---- Painting ---- Art ----- 59

The report is grouped by department so that all the math courses appear in
one group, art in another, etc.

In the group footer, I have already created a box that shows the number of
courses passed per department (>=65) as well as the number of courses
required to complete in that department. For example, students must pass 3
art courses to graduate. So, based on the data above a message tells them
that they have passed 1 course out of the 3 required. The required number is
done by referencing a table that has departments and required number of
courses.

What I would like to do now is conditionally format text boxes so I can make
it look more graphical. The part that I am stuck on is how to conditionally
format using criteria from 2 separate fields. For example, there should be 3
boxes in the Art group, and 8 in the math group. Then, the additional
condition is if the student passed 1, the first box should turn green. If a
student passed 2, the first and second box should turn green. I know how to
set these conditions, but not how to set the number of boxes that show up
depending on the number of courses required to take in the department.

I hope this is clear. Thank you.

 

[Wolfgang Kais]

Hello "vanguardaccess".

I have a report with data such as the following:

Name ---- Course ----- Dept ----- Grade

John ----- Algebra ---- Math ----- 77
John ---- Geometry --- Math ---- 81
John ---- Sculpture --- Art ------ 99
John ---- Painting ---- Art ----- 59

The report is grouped by department so that all the math courses
appear in one group, art in another, etc.

In the group footer, I have already created a box that shows
the number of courses passed per department (>=65) as well as
the number of courses required to complete in that department.
For example, students must pass 3 art courses to graduate.
So, based on the data above a message tells them that they have
passed 1 course out of the 3 required. The required number is
done by referencing a table that has departments and required
number of courses.

What I would like to do now is conditionally format text boxes
so I can make it look more graphical. The part that I am stuck
on is how to conditionally format using criteria from 2 separate
fields. For example, there should be 3 boxes in the Art group,
and 8 in the math group. Then, the additional condition is if the
student passed 1, the first box should turn green. If a student
passed 2, the first and second box should turn green.
I know how to set these conditions, but not how to set the number
of boxes that show up depending on the number of courses required
to take in the department.

First:
You didn't reply to my answer to the ">=65" question, so it't nice
to hear that you got it to work.

I've never liked conditional formatting, I'd use the format event of
the section containing the boxes (department footer section I guess).
You can write code for that event that changes the format/visibility
of controls (provided that you have enough "boxes" in the section:
(something similar to this)

Dim i as Integer
For i = 1 to 10 'suppose you have 10 boxes named Box1 to Box10
If i <= NumBoxes Then 'NumBoxes: number of boxes for department
Me.Controls("Box" & i).Visible = True
Else
Me.Controls("Box" & i).Visible = False
End If
Next

For i = 1 to NumBoxes
If i <= NumPassed Then 'NumPassed calculated using ">=65"...
Me.Controls("Box" & i).BackColor = vbGreen
Else
Me.Controls("Box" & i).BackColor = vbRed
End If
Next

As I said: something similar...

 

[Guest]

I really appreciate your help. I don't think I have the technical expertise
yet to fully understand what you said, and therefore need more guidance.

In the Event tab under format, I believe you are saying that I need to put
the expression that you wrote. How do I make sure that it will apply only to
the 10 text boxes that I put in and not other things in the group footer?

Also, I have put 10 text boxes. Do the labels matter? What expressions
should I give them?

Finally, I don't understand a few parts of the code. What do "dim" and
"me." mean?

I appreciate your help very much.

 

[Guest]

I've been looking at the situation more closely. If you can answer the
following question, I think I may have a solution.

Is there a way to create a text box that is visible only on the condition
that another field's value is equal to a certain number? If so, how?

For example: I have a field called [Required_credits]
My text box gets its value from another source
(=Sum(IIf([Vangrade]>=1,1,0)). However, I would like it only to be visible
when [Required_credits] is greater than or equal to 4.

Thanks.

 

[Wolfgang Kais]

Hello "vanguardaccess".

I really appreciate your help. I don't think I have the technical
expertise yet to fully understand what you said, and therefore need
more guidance.
In the Event tab under format, I believe you are saying that I need
to put the expression that you wrote. How do I make sure that it
will apply only to the 10 text boxes that I put in and not other
things in the group footer?

Also, I have put 10 text boxes. Do the labels matter? What
expressions should I give them?

Finally, I don't understand a few parts of the code. What do "dim"
and "me." mean?

I appreciate your help very much.

In the event tab under format, choose [event procedure] and click on
"...". The code editor will open with a blinking cursor in the event
procedure for the format event.
The "Boxes" can be any control type you want to use to display
something "green", for example a rectangle or label. I thought you
were going to use textboxes for that purpose. The labels only matter
if you want them to be shown, otherwise delete them.
The code assumes that the textboxes are named Box1, Box2, ... Box10
and it only affects these controls.
"Dim i as Integer" declares an integer variable in the sub procedure.
This variable is used for two loops. In the first loop, only those
boxes are visible that are needed (I suppose that there is a control
NumBoxes that holds the number of exams for the department).
The second loop changes the color to green for the first NumPassed
Boxes where NumPassed is assumed to be a control that holds the
number of exams passed calculated using "=Sum(Iif([GRADE]>=65,1,0))".
The name of the control to be modified inside a loop is built as a
concatenation of the word "Box" and the number i in the variable.
"Me" in a class module references the object whose code is executed,
in this case "Me" means the report itself and the dot is used to
navigate in the object hierarchy. "Controls" is the collection of
controls on the report and the value inside the brackets are used to
identify a certain control on the report.

 

[Wolfgang Kais]

Hello "vanguardaccess".

I've been looking at the situation more closely. If you can answer
the following question, I think I may have a solution.

Is there a way to create a text box that is visible only on the
condition that another field's value is equal to a certain number?
If so, how?

For example: I have a field called [Required_credits]
My text box gets its value from another source
(=Sum(IIf([Vangrade]>=1,1,0)). However, I would like it only to be
visible
when [Required_credits] is greater than or equal to 4.

When using an event procedure for the format event of the section of
the textbox ("txtMyBox"?), there should also be a textbox for
[Required_credits] (which can be made invisible), let's say "txtRC".

The code should read:
If Me.txtRC >= 4 Then
txtMyBox.Visible = True
Else
txtMyBox.Visible = False
End If

But believe it or not: I created a completely different solution
without any vba code and without conditional formatting and without
10 additional textboxes, only 2 more textboxes are needed:

Suppose you have txtMaxScore (the number of exams in the department,
=Count(*)) and txtScore (=Sum(Iif([GRADE]>=65,1,0))) in the footer.
Create two more textboxes, txtMaxScoreDisplay and txtScoreDisplay.
Format them to have the same size, background and frame transparent,
font size 24. Choose the font colors gray for txtMaxScoreDisplay and
green for txtScoreDisplay. Place them at the same position (Top and
Left properties should be equal), but move txtMaxScoreDisplay to the
background (use the format menu). Now the trick is the ControlSource:
txtMaxScoreDisplay: =String([txtMaxScore], CharW(9679))
txtScoreDisplay: =String([txtScore], CharW(9679))