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
288 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
[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
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 33576438
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
ID: 33576463
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
ID: 33576529
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)
ID: 33576577
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)
ID: 33576631
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 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