Solved

How can I change a color using Conditional formatting or VBA, when the value of a record changes?

Posted on 2010-09-01
5
278 Views
Last Modified: 2012-05-10
I have a question about a continuous form I'm working on.  The record source will have 2 or 3 records with the same "transID,"  then changes to another.  I'd like to visually group these on the form, so the user easily identifies them as a group.  The form has to stay a form.  Since forms don't allow grouping, I thought conditional formatting might work.  Any ideas?  Thanks in advance!
0
Comment
Question by:sarahellis
5 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Unless you are using A2010 which has 50 CF rules, rather than the 3 in earlier versions, then I don't believe this is possible.  But wait for other opinions.

(I'm not saying it would be easy in A2010, but since it is probably unlikely that you have more than 50 different transID values on the screen at the same time  it is at least theoretically viable.)
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
You have a limit of 4 "conditions" for Conditional Formatting (i.e. the Default condition + 3 that you can detail). You can modify this to some degree with VBA, but it's tricky at best.

You might use a Temporary table to "format" your records using blank spaces to delineate your differing groups. In other words, if GroupA has 8 record, and GroupB has 4 and GroupC has 6, then move those records to a Temporary table and add the first 8 records, then a blank line, then the next 4 records and a blank line, and finally the last 6 records. The user would see a blank line and would be visually notified that a new "group" had started. You could also insert text in the first "cell" of that blank line to indicate the TransID, and then insert a value in the second cell to use with Conditional Formating (i.e insert a value of "True" in the second "cell", and then use CF to Bold/Make Red the value of the First "cell" based on the value of True in the second).

If you need advanced functionality like this, you often must move to a 3rd party grid control (I've used GridEX from www.janusys.com, but I'm not sure how well it will work in 07). CodeJock has a ReportControl which may work in 2007 (www.codejock.com), and Bennet-Tec has a control which might suit your needs (www.bennettec.com).
0
 

Author Closing Comment

by:sarahellis
Comment Utility
Oooh, good idea!  I think I'll try a spin on that idea.  Since this is a stagnant table (which I didn't mention), I'll add a field and populate it, with a code for every other group.  Then I can hide that field but use it for conditional formatting.  Thanks for the inspiration!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
In 2007, you also have the Detail sections "Paint" event.  Similar to a Reports Format event, the Paint event allows you to do formatting when the record is displayed.  You could use code similar to the following to cycle through a series of background colors, using changes in [transID] to initiate the change in background color.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Oops, forgot the code:

Private Sub Detail_Paint()

    Static lngTransID as long
    Static intCounter as int
    Dim lngBackColor as long

    if me.txtTransID <> lngTransID then
        lngTransID = me.txtTransID
        intCounter = (intCounter + 1) Mod 3
        lngBackColor = Choose(intCounter + 1, RGB(255, 0, 0), RGB(0, 255, 0), RGB(0, 0, 255))
    end if
    Me.txtTransID.BackColor = lngBackColor
       
End Sub
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

9 Experts available now in Live!

Get 1:1 Help Now