Solved

Report Formating based on a field

Posted on 2001-08-03
10
195 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now