Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Report Formating based on a field

Posted on 2001-08-03
10
Medium Priority
?
243 Views
Last Modified: 2008-03-17
I would greatly appreciate if somebody could help me with a report design.  I am designing a report for a large collection of documents. What I would like to do is to change the back color of the detail section of the form to yellow to give it a highlighted effect, if the document is current based on a field called DOC_STATUS.
The value of DOC_STATUS is: ?Obsolete? or ?Current? or ?New?. I would also like to change the back color of the detail section to light blue if the document is classified as ?New? and leave the back color of the detail section transparent.
Here is an Example of the report:

Doc Number        Title     Creation Date      Status
0019920-B     M Prod Spec    01/01/01             Current
0002511-A     N Prod Spec    01/01/99             Obsolete

As an alternative, I would like to change the font color for the each record based on the DOC_STATUS
Field.
Conditional formatting does not work for me, because the attribute I want to change is not of the DOC_STATUS? text box.

accesspro
0
Comment
Question by:accesspro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 400 total points
ID: 6349837
In the format event of the detail section you can code a test for the value of DOC_STATUS and set the backcolor/fontcolor of the wanted fields depending on its value.
However you need to "cover" all possibilites as when it's set it will stay that way until changed...

Need more info ?

Nic;o)
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6350019
expanding on nico's idea.

what you would do is in the OnFormat event of the report, you would place code that changes the particular field (or whatever you need to change) to blue.

for example, in the OnFormat event you'd have something similar to:

Const YELLOW As Long = 9895935
Const LIGHT_BLUE As Long = 16770710

select case (DOC_STATUS)
     case is = "Obsolete"
          DOC_STATUS.backcolor = YELLOW
          DOC_STATUS.backstyle = 1 'on
     case is = "New"
          DOC_STATUS.backcolor = LIGHT_BLUE
          DOC_STATUS.backstyle = 1 'on
     case else
          DOC_STATUS.backstyle = 0 'off
end select


try that out and see if it does what you want...

dovholuk
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6350174
Just to expand on Nico's and dovholuk's thoughts ...


You actually wanted to change the background color of the *DETAIL SECTION*. So, I would advise that you do as they suggested, but with the following revisions:

   1) Change the background color of the object named Detail (instead of background color of DOC_STATUS):

        Detail.BackColor = YELLOW

   2) Change the BackStyle of each object within the Detail Section to Transparent (so that the yellow background may show thru)



-Dennis Borg
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:accesspro
ID: 6350274
I have another problem! When I try run the report, I get run time error 2465, can't find the reference to DOC_STATUS, even dough I checked the references I thought I needed.

Public Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const YELLOW As Long = 9895935
Const LIGHT_BLUE As Long = 16770710
    Select Case (DOC_STATUS)
    Case Is = "Obsolete"
            Detail.BackColor = YELLOW
            Detail.BackColor = 1
    Case Is = "New"
            Detail.BackColor = LIGHT_BLUE
            Detail.BackColor = 1
    Case Else
            Detail.BackColor = 0
    End Select
End Sub
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6350595
Three possible problems:
1) Try: Select Case DOC_STATUS
2) Try: me.DOC_STATUS
3) If a space is in the original name try: me.[DOC STATUS]

Nic;o)
0
 

Author Comment

by:accesspro
ID: 6359742
Problem solved!
I would like to thank you all for your prompt comments. I just needed a kick in the right direction. I think I owe it to everybody on this website, tell how I fixed the problem. (I also changed the colors a bit)

(1)Typed the following code into the OnFormat Event of the Detail Section of the report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Const LIGHT_BLUE As Long = 16777164
Const LIGHT_GREEN As Long = 15859688
Const RED As Long = 14145535
Const WHITE As Long = 16777215
'Detail.BackColor = 16777215
   Select Case Me.DOC_STATUS
   Case Is = "Obsolete"
        Detail.BackColor = RED
        'Detail.BackColor = 1     I did not need this
   Case Is = "Current"
        Detail.BackColor = LIGHT_GREEN
        'Detail.BackColor = 1     I did not need this
   Case Is=?New?
     Detail BackColor = LIGHT_BLUE
     'Detail.BackColor = 1     I did not need this
   Case Else
        Detail.BackColor = WHITE
   End Select
End Sub

(2)I added the DOC_STATUS field to the reports detail section and changed its visible property to NO. This is where I had the problem, this field wasn?t in the detail section of my report, therefore it couldn?t have been referenced before.
(3)I changed the BackStyle of all the fields in my detail section of the report to transparent, this way, when the background color changes I wouldn?t get white patches.


0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6359811
AccessPro:

Glad you hear you got it working, and that I could help in a small way!


-Dennis Borg
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6360015
Glad we could help.
BTW VBA has some predefined color values like:
vbRed, vbBlue, vbWhite, etc.

Nic;o)
0
 

Author Comment

by:accesspro
ID: 6360663
Thanks again!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6360943
You're welcome, success with the application !

Nic;o)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question