Solved

Report Formating based on a field

Posted on 2001-08-03
10
231 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 100 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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