Change font color in Report for Different Conditions

[sam]

Hi all,

I am working on Access 2003,

I have report generated by query results I want to change the font
color of the records which are repeating..

like with same txtZipcode the all records with multiple zipcodes are
Red and the Last one in that list is Green.

so if zipcode is 98789 then all records with this zipcode are Red and
last of them should be green.

This is my code.. as shown bolded is changes the color of whole column
insted of each record..

Do Until rst.EOF
deptCount = rst.Fields(0)
ziptmp = rst.Fields(1)
For i = 1 To deptCount
rst2.MoveFirst
While Not rst2.EOF
If (rst2.Fields(1).Value = ziptmp) Then
Me!txtZip.ForeColor = Color1
Else
Me!txtZip.ForeColor = Color
End If
rst2.MoveNext
Wend
Next i
rst.MoveNext
Loop


I hope its understandable...!!

Thanks!!
Sam

 

[Marshall Barton]

I am working on Access 2003,

I have report generated by query results I want to change the font
color of the records which are repeating..

like with same txtZipcode the all records with multiple zipcodes are
Red and the Last one in that list is Green.

so if zipcode is 98789 then all records with this zipcode are Red and
last of them should be green.

[code snipped]


Since "last" implies that the report is sorted on the zip
field, you can add a grouping on the zip field and use the
group header section (which may be invisible) to help
determine when you are processing the last detail in the
group.

Add a text box named txtGrpCount to the group header and set
its expression to =Count(*)

Add another text box named txtDetail to the detail section.
Set its expression to =1 and RunningSum to Over Group.

With that in place, the detail section's Format event can do
what you want by using code like:

If Me.txtDetail = Me.txtGrpCount Then
Me.txtZipcode.ForeColor = vbGreen
Else
Me.txtZipcode.ForeColor = vbRed
End If

 

[sam]

Thanks for quick reply...
Actually i am using group by zipcode i have a query
sql = "SELECT Count(*) AS depts, PeopleSoft_CCR.txtZip FROM
PeopleSoft_CCR GROUP BY PeopleSoft_CCR.txtZip;"
and two recordsets on it...

the problem is it changes the color of the whole section of txtZipcode
instead of particular record.

So when i run the report it shows all records of txtZipcode is Red...

Can you please give me a hint on that..

I really apprecieate yr help..

Thank You,

 

[Marshall Barton]

Thanks for quick reply...
Actually i am using group by zipcode i have a query
sql = "SELECT Count(*) AS depts, PeopleSoft_CCR.txtZip FROM
PeopleSoft_CCR GROUP BY PeopleSoft_CCR.txtZip;"
and two recordsets on it...

the problem is it changes the color of the whole section of txtZipcode
instead of particular record.

So when i run the report it shows all records of txtZipcode is Red...

Can you please give me a hint on that..

I'm afraid I can not figure put how that query relates to
your report. The query will return only one record for each
zip code so there will be no such thing as "last" and not
last records for a zip code.

If you are only using that query to assist in determining
when you are working on the last record in the table, it
isn't worth pursuing because it's a complicated way to do
what I posted.

 

[sam]

Let me explain you ...here is my code..
I am not sure this is the best way......but with this code it shows all
records (for txtZipcode) Red...

sql = "SELECT Count(*) AS depts, PeopleSoft_CCR.txtZip FROM
PeopleSoft_CCR GROUP BY PeopleSoft_CCR.txtZip;"
sql1 = "SELECT PeopleSoft_CCR.txtZip FROM PeopleSoft_CCR"
Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst.Open sql, cn
rst2.Open sql1, cn

Do Until rst.EOF
deptCount = rst.Fields(0)
ziptmp = rst.Fields(1)
For i = 1 To deptCount
rst2.MoveFirst
While Not rst2.EOF
If (rst2.Fields(0).Value = ziptmp) Then
If (i = deptCount) Then

[txtZip].ForeColor = vbGreen
Else
[txtZip].ForeColor = vbRed
End If
End If
rst2.MoveNext
Wend
Next i
rst.MoveNext
Loop


If you need any explaination then please tell me...

Thank You for your time..

 

[Marshall Barton]

Let me explain you ...here is my code..
I am not sure this is the best way......but with this code it shows all
records (for txtZipcode) Red...

sql = "SELECT Count(*) AS depts, PeopleSoft_CCR.txtZip FROM
PeopleSoft_CCR GROUP BY PeopleSoft_CCR.txtZip;"
sql1 = "SELECT PeopleSoft_CCR.txtZip FROM PeopleSoft_CCR"
Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst.Open sql, cn
rst2.Open sql1, cn

Do Until rst.EOF
deptCount = rst.Fields(0)
ziptmp = rst.Fields(1)
For i = 1 To deptCount
rst2.MoveFirst
While Not rst2.EOF
If (rst2.Fields(0).Value = ziptmp) Then
If (i = deptCount) Then

[txtZip].ForeColor = vbGreen
Else
[txtZip].ForeColor = vbRed
End If
End If
rst2.MoveNext
Wend
Next i
rst.MoveNext
Loop

That can not work because you are working in recordsets and
the report is processing the report's data in its own way.
It would take a lot more code spread over multiple
procedures to do what you want with this convoluted
recordset approach. I strongly suggest that you give it up
and try the simple approach I recommended.

 

[sam]

Hi Marsh,

I did try by your method and its working now,

Thank You very much...i really apprecieate your help.

Thanks Again to be so helpful..
Have a good day !!

 

[sam]

Hi Marsh,



I am really thankful to you for your help,
You helped me on access report for changin font color....by grouping
on zipcodes

But i got a problem...as i did as you told to group by zipcode and then
put a count field in header section
and one txtDetail field in detain section which runs fron 1 to Running
sum over group...

But i wanna my report in DeptId ascending and this solution gives me in
grouping by txtZipcodes...

And if Do by DeptId ascending it messes up all...it shows all records
green...only...



Can you help me with this..

Sorry to disturbing you...

Thank You Very much...
Sam.

 

[sam]

Hi Marsh,



I am really thankful to you for your help,
You helped me on access report for changin font color....by grouping
on zipcodes

But i got a problem...as i did as you told to group by zipcode and then
put a count field in header section
and one txtDetail field in detain section which runs fron 1 to Running
sum over group...

But i wanna my report in DeptId ascending and this solution gives me in
grouping by txtZipcodes...

And if Do by DeptId ascending it messes up all...it shows all records
green...only...



Can you help me with this..


Thank You Very much...
Sam.

 

[sam]

Hi Marsh,

Actually I want to do sorting on DeptId which is (Primary key..) But it
should be group by txtZipcode.. as DeptId is unique and there are
multiple departments in one zipcode....and other fields
are..DeptName,ResponsiblePerson.....

As i need for each DeptId with Same Zipcode Colored Red (the last one
should be green)..

With Groupting by zip code it shows perfact but not in ascending of
DeptId..as it is group and ascended by txtZip...

Can you help with this...
This is my School work(I am studying at SacState,California..)

Thanks,

 

[Marshall Barton]

Actually I want to do sorting on DeptId which is (Primary key..) But it
should be group by txtZipcode.. as DeptId is unique and there are
multiple departments in one zipcode....and other fields
are..DeptName,ResponsiblePerson.....

As i need for each DeptId with Same Zipcode Colored Red (the last one
should be green)..

With Groupting by zip code it shows perfact but not in ascending of
DeptId..as it is group and ascended by txtZip...

Ahhhh, I finally see why you were using your complicated
approach.

Scrap the Zip grouping approach.

If txtZip is a numeric type field, use this in the detail
section's Format event:

If Me.DeptId = DMax("DeptId", "PeopleSoft_CCR", _
"txtZip = " & Me.txtZip) Then
Me.txtZip.ForeColor = vbGreen
Else
Me.txtZip.ForeColor = vbRed
End If

If it's a Text field, change the condition to:
"txtZip = """ & Me.txtZip & """ "

 

[sam]

Hi Marsh,

Its working now perfactly..
This was great thank you for your help..

I really appreciate it

Thank You,
Sam