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

x
?
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
Medium Priority
?
303 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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

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 …
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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