Solved

Report Formating based on a field

Posted on 2001-08-03
10
221 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

856 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