Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Conditional Formatting for an Access Report

Posted on 2009-05-11
7
Medium Priority
?
359 Views
Last Modified: 2013-11-28
The Access database I work with contains specific information about records as they are processed.   The records are organized by state.  

Users can generate a report concerning all the records of a specific state.  Each row of the report shows information about a specific record, in a pattern like this -

Row 1, Record 1:  Field 1   Field 2   Field 3   Field 4   Field 5   Checkbox 1
Row 2, Record 2:  Field 1   Field 2   Field 3   Field 4   Field 5   Checkbox 1
Row 3, Record 3:  Field 1   Field 2   Field 3   Field 4   Field 5   Checkbox 1

In the data entry form, Checkbox 1 is checked when the processing of a record is finished.  I added Checkbox 1 to the report as an object that is not visible on the report.  Checkbox 1 occupies a field in the same table where Fields 1 through 5 reside.

If Checkbox 1 is checked for a specific record, I would like to cause the BackColor property to be yellow for all the fields in the row concerning that record; if Checkbox 1 is not checked, I would like the BackColor property to be white.  This way, the user can easily distinguish between the records that have been processed from those that have not.

I have not had much success with this.  For example, the code below highlights the entire column (i.e., all records or rows) with the field entitled "fldDATE", even if "fldUSED" (i.e., Checkbox 1) has been checked for only one record or row in the dataset.  

In the code below, "fldUSED" is the equivalent of Checkbox 1 described above.  "fldDATE" represents a test field that I added to see how the coding would play out in practice.  "fldCOUNTER" is an Auto Number in the table where all the records and fields reside.  

On its face, this does not seem to be all that difficult, but I am stymied.  I have tried Case Select, For Each . . Next, multiple loops, other potions.  
Private Sub Report_Load()
Static intCounter As Long
Dim intStop As Long
intCounter = DMin("[fldCOUNTER]", "tblRULESNOTICE")
intStop = DMax("[fldCOUNTER]", "tblRULESNOTICE")
Do While intCounter < intStop + 1
If Me.fldUSED = Yes Then
    Me.fldDATE.BackColor = vbYellow
ElseIf Me.fldUSED = No Then
    Me.fldDATE.BackColor = vbWhite
End If
    intCounter = intCounter + 1
Loop
End Sub

Open in new window

0
Comment
Question by:AccessHack
[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
  • 3
  • 3
7 Comments
 
LVL 5

Expert Comment

by:noetymology
ID: 24362280
Try the following code in the Format event of the Detail section of the report. I have also attached a sample database (zipped file). However the conditional formatting will work only in Print Preview of the report. If you are on Access 2007 then conditional format doesnt work when you are in report view because the execution of events is different.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Checkbox = -1 Then
Me.F1.BackColor = RGB(255, 255, 0)
Me.F2.BackColor = RGB(255, 255, 0)
Me.F3.BackColor = RGB(255, 255, 0)
Me.F4.BackColor = RGB(255, 255, 0)
Me.F5.BackColor = RGB(255, 255, 0)
Else
Me.F1.BackColor = RGB(255, 255, 255)
Me.F2.BackColor = RGB(255, 255, 255)
Me.F3.BackColor = RGB(255, 255, 255)
Me.F4.BackColor = RGB(255, 255, 255)
Me.F5.BackColor = RGB(255, 255, 255)
End If
End Sub

Open in new window

Conditional-Format-Test.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24365283
AccessHack,

1. Make sure that the BackStyle of the textboxes is set to: Normal, or the Back color will not be visible.

2. If you just need the basic colors, you can use the VB color constants, in place of the "RGB" syntax,
The VB Color Constants may be slightly easier to remember and write:
vbBlack
vbRed
vbGreen
vbYellow
vbBlue
vbMagenta
vbCyan
vbWhite

Something like this, based on noetymology's post:
        ...
        Me.F1.BackColor = vbYellow
        ...
        Me.F5.BackColor = vbRed
        ...

3. Finally, you can disable "Report View" by:
Opening the report in design view.
Bringing up the Property box for the entire Report.
Setting the "Allow Report View" property to: No

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24365334
noetymology,

I think your sample attachment got lost. (it is 0 Bytes)
...Possibly due to you having the file open when you uploaded it.

;-)

Jeff
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Accepted Solution

by:
noetymology earned 500 total points
ID: 24366533
I am attaching the file one more time. It simply has Table1 with ID (Autonumber), Checkbox(Yes/No), F1,F2..F5 as Text. A form based on it. You can keep it as a data entry form too, if you wish to. And a Report1 on the Table1. Use the below code in the Format event of the Details section of your report so that it checks the value of Checkbox record by record and format each record with a backcolor.Hope this helps. If you still are unable to get the MDB file, then you can create one your own by the steps mentioned. It should work!
Option Compare Database
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Checkbox = -1 Then
Me.F1.BackColor = RGB(255, 255, 0)
Me.F2.BackColor = RGB(255, 255, 0)
Me.F3.BackColor = RGB(255, 255, 0)
Me.F4.BackColor = RGB(255, 255, 0)
Me.F5.BackColor = RGB(255, 255, 0)
Else
Me.F1.BackColor = RGB(255, 255, 255)
Me.F2.BackColor = RGB(255, 255, 255)
Me.F3.BackColor = RGB(255, 255, 255)
Me.F4.BackColor = RGB(255, 255, 255)
Me.F5.BackColor = RGB(255, 255, 255)
End If
End Sub

Open in new window

Conditional-Formatting-New-Sampl.mdb
0
 
LVL 5

Expert Comment

by:noetymology
ID: 24372994
Any luck with my sample database/VBA?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24373836
Yes,

Please keep us posted
0
 

Author Comment

by:AccessHack
ID: 25516728
Thank you.  What you suggested works well; I have been ill and apologize for not responding sooner.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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 …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

636 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